Create a subscription tracker with Microsoft Lists and Power Automate

Create a subscription tracker with Microsoft Lists and Power Automate

I’ve been asked if it’s possible to create an application to track subscriptions to Microsoft 365 services. The requested features are:

• Add a new service for each subscription plan purchased.
• Specify the billing term, price, and expiration date.
• Automatically count days until renewal.
• View billing history and total amount paid.
• Send a renewal reminder in mobile notifications

Using Microsoft Lists for the application

I decided to use Microsoft List for my application and created the following columns:

• Service Name (one line of text)
• Logo (image)
• Plan details (several lines of text)
• Billing term (Choice list)
• Price (Currency)
• End Date (Date)
• Number of days until renewal (Calculated column -> [End date]-Today(), displayed as a number)

To highlight some data, I use conditional formatting to add red-amber-green backgrounds based on the number of days left to cancel my subscription, like so if there are:

• less than 3 days to cancel subscription, set background to red,
• 3 days or more to cancel, but less than 7 days, the background turns orange, and
• 7 days or more, use a green background:

To do this, simply change the settings of the relevant columns to apply conditional formatting. Select the column you want to format, open the settings and select Format this column. Then, choose Conditional Formatting and Edit Rule (from the […] menu). Make sure the correct column is selected and enter the comparison to use. In our case, we are using the is less than comparison because we want to check that the value in the column is less than 3 (days). Now click the pencil icon (Figure 1) to choose the color of the formatting you want to apply (red). Click Save to save the rule.

Repeat the process to create the other formatting rules.

Finally, add the third rule.

After entering the rules, our list should look like Figure 4:

Create an alert

Now I want to create an alert if the number of days until renewal is less than 3. To do this, I use Power Automate and create the following flow:

Let’s dive into the details of these actions.

trigger: recurrence: This trigger means that our flow will wake up every day at 10 AM (UTC+1) to execute the actions. No external event triggers the flow.

initialize variable: VarIntegerDays : To make it easier to adjust our callback later (and because I like to avoid hard-coded values), I initialized an integer variable with a value of 3.

initialize variable: VarStringReminder : Since I then want to determine whether the stream should send me a reminder, I initialize a second string variable and use the following expression:

addDays(utcNow(), variables(‘varIntegerDays’), ‘yyyy-MM-dd’)

This means I add the value of my first variable (3) to today’s date and it should return it as a date.

Get items: SharePoint : Now we want to get the items in our list. Instead of first getting all the items and then adding a condition to our feed, I find it more elegant to query our list to get only the items that match my query:

End lt ‘@{variables(‘VarStringReminder’)}’

Lt stands for ‘less than’ – meaning that if our subscription end date is less than 3 days before, we’ll receive a daily reminder to cancel. If you don’t want to bother, you can also replace lt with eq, which means ‘equal’, so you only receive this reminder once.

Loop and notification

Since multiple items could theoretically match our query, we want to receive a notification for each subscription. Inside the loop, we can add a mobile notification, an email, a task, or whatever you like.

Review billing history – what did I pay?

To get an idea of ​​what we have already paid this year, we need to update the list to multiply the price by the billing term and, if the billing term is monthly, multiply that figure by the number of months in that year.

Let’s take it step by step:

1. change the “Billing Term” column to a numeric column.
2. in this column, 12 is monthly (12 times a year), 1 is annual.
3. Create a new calculated column M, which gives you the month of today’s date: =MONTH(Today())
4. Create a new calculated column Costs so far this year, formula: =IF([Billing Term]=1,Pricing,Pricing*M)

Each subscription now shows how much we’ve paid for the current year, but we don’t have an overview of what we’ve paid for all subscriptions in total. Unfortunately, SharePoint doesn’t have totals for calculated columns, so we need to find another solution for this—Power Automate to the rescue!

First, create a new column in the list called Totals.

Using a second stream to calculate totals

Figure 6 shows the flow I built to calculate billing totals:

The following actions are used:

• Trigger: Recurrence:  This feed is scheduled to be pulled daily.
• Get Items:  Retrieves all items from our SharePoint list without any query.
• Initialize variable Current Total:  Set the type to float, the value must be 0 – we will set the value later in the stream.

In an “Apply to Each” loop, we do:

• Set variable tempAmount:  Set the variable tempAmount to the value of our SharePoint column Costs so far this year.
• Increment variable Current Total:  Increment variable Current Total: Increment the Current Total variable with this expression:

float(variables(‘tempAmount’))

• Update item:  Finally, update the list with the Current Total variable in the newly created column.

Figure 7 shows the result:

Conclusion

We can create powerful lists in Microsoft Lists and extend what’s available in SharePoint Online with Power Automate. Next up? How to automatically create a visual report using Power BI and SharePoint Pages.