As IT professionals, we have a lot of tasks that we run daily. One of those tasks in my professional career is to keep track of all kinds of licenses. There are a lot of subscriptions that I have to track either yearly, quarterly or monthly, and I have to make sure that I am aware of the expiration dates.
I decided to build a system which will give me a piece of mind and keep all my licenses up to date. A searchable, centralized database to store license information, with an ability to send expiration date reports to an email.
This is not the only use case where you can use this solution. Any data that is associated with expiration dates can use the same approach.
For this automated system, I am going to use three main components:
- Backend database – SharePoint list to store license information
- Workflow engine – Microsoft Flow to send email notification based on a business rule
- Email notifications – Exchange Online as the delivery method
Building the SharePoint List
Let’s start by building a SharePoint List with the required columns. The list can live within one of your existing SharePoint sites, or even a Team site. An Office 365 Group is an easy way to create a Team site for this purpose.
For this example, I have built a very simple list based on the business requirements. The columns and column types are shown in the screenshot below.
You can add additional columns based on your business needs. For more details on SharePoint columns and options, visit Microsoft Office support webpage.
Now we can add software licenses to the list. The screenshot below shows what the new item form looks like for adding items to the list.
For this demonstration I’ve added a few Office 365 licenses to the list.
Creating the Flow
Now let’s build the workflow that will send an email notification when licenses are near their expiration date. We are going to use Microsoft Flow as the workflow engine to run daily and check the expiration date of the licenses. Flow will send an email if there are items that are expiring in the next seven days.
We need to configure five main steps in the workflow:
- Recurrence of the workflow (when to trigger the workflow)
- Get Items (Query SharePoint list)
- Filter Items (based on business needs, which is seven days prior the expiration date )
- Create an HTML output
- Send Email
The screenshot below is the final setup of the workflow.
Let’s review each step one by one.
The Recurrence (first step) is a very simple step where I set the workflow to run daily at 06:00 AM CST. You have to specify Interval, Frequency and Time Zone.
In the next step, Get Items, we need to query data from SharePoint list by specifying the URL of the SharePoint site and the SharePoint list name where we store information about our licenses.
By running the Get Items action, we will pull all the items from the SharePoint list and store them in the Values output object. You will find the use of this object in the next step of the flow.
Next, let’s review the Create HTML table action. This action is very useful to generate table reports from SharePoint List, SQL server, Microsoft Dynamics and more.
Create HTML table is a part of the Data Operations action group. To add Create HTML table action to the flow start by adding a blank action to the flow. Then, search and click on Data Operations action group.
Next, search for Create HTML table within the Data Operations action group.
After adding Create HTML table action, we will need to link the dataset from the previous action step as a source for the table. Click on From, and then Show advanced options. From Dynamic content choose Value (which is the output from the previous step).
After the dataset is configured, we will design our HTML table by specifying fields from the SharePoint list that we would like to report on.
As I mentioned at the beginning of the blog post, we will be adding a Filter Array step between Get Items and Create HTML table actions to filter data based on our business needs.
In the From field of Filter Array action, we need to specify a collection of data to filter. We are going to use data object from the previous step that was queried in the Get Items step and stored in the Value data object.
The Value object is automatically created by Flow and available for you to use in the Dynamic Content library. Now let’s set the filtering parameter where Expiration Date must be equal to [Today() + 7 days] to meet business requirements. The left side of the filtering requirements is a SharePoint column named Expiration Date, which is available in the dynamic content. The right side of the filtering requiremensts is an Expression.
To set the expression click on the right side of the condition, switch to Dynamic Content section, click on Expression tab and type below expression.
addDays(utcNow(),7, ‘yyyy-MM-dd’
Where:
- addDays – function to add a number of Days to a date
- utcNow() – today’s date
- ‘yyyy-MM-dd’ – comparison date format. Make sure to use capitalize “MM” for a month, since lowercase “mm” represents minutes.
You can also review your expression in the advanced mode of this step by clicking on Edit in advanced mode.
@equals(item()?['Expiration_x0020_Date'], addDays(utcNow(), 7, 'yyyy-MM-dd'))
Now, as we have our data filtered, we need to change the source of data in Create HTML table from Value object to the outcome of Filter Array object. The need of this step is to build an HTML table only with items that are expiring in seven (7) days, not the entire SharePoint list.
In the last step, we need to send an email only if the data array contains at least one license that is expiring in 7 days. To achieve this goal, I am going to check the array length.
First, add a condition and set the statement:
Since the condition is complex, we have to switch to Advanced mode and enter the conditional expression below:
@greater(length(outputs('Filter_array').body), 0)
In the step above, we are checking if the array contains any data. You can switch back to Basic mode and review visually what is happening in this step.
In the screenshot below, you can find steps on how to review expression visually.
If the length of the array is greater than zero, we will be sending an email with an HTML table that we built in the previous steps.
As you can see, Flow created an output object from the Create HTML table step for us to use in the email body.
Since the body of the email is an HTML table, we will need to turn on an HTML mode in the email action by navigating to Advanced mode and setting an isHTML property to Yes.
Testing the Flow
Ok, we are all done. Let’s test our solution. For that, I’m going to set up a couple of licenses in SharePoint list to expire in 7 days and run the Flow manually. At the time I am running this flow today date is March 25, 2018. We can see that two licenses are expiring in 7 days (O365 Business Premium and O365E3).
To run the Flow manually, open the Expiration Notification Flow and click on Run Now.
After running the Expiration Notification Flow manually, let’s check the mailbox.
Awesome, we can see that expiring items are reported in our email message.
I hope this solution saves you time, and helps you track your software licenses to ensure that license expirations are not missed.
Photo by Sonja Langford on Unsplash
The execution of template action ‘Filter_array’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@less(item()?[‘Expiration Date’], addDays(utcNow(), -17, ‘yyyy-MM-dd’))’ failed: ‘The template language function ‘less’ expects two parameter of matching types. The function was invoked with values of type ‘Null’ and ‘String’ that do not match.’.
Mine run successfully but I don’t receive any email :
Hi,
i rebuilt the flow same which you have explained but i am able to get the notification only html headers like title and expiration dates there are the headings ..
i want to notify that particular software expiry details,please help me out
Hi,
I’m having trouble with the conditional part as there is no advanced option.
Don’t suppose anyone would be able to export their working flow so that I can import it and amend from there, please?
Hello,
I want to know how to add table borders. how we can add in a border around the table and cells
Hello,
I run my Flow and receive an email I can see only column names but does not contains any values.
also In send email action, i don’t see inHTML option in advanced.
Any ideas or help would be greatly appreciated.
Great Article! Just what I needed.
I do need some help on an issue where I have people picker fields in the SharePoint List and I need it to send an email to the “Creator”, “Supervisor”, and “Manager, but when I add them in the “To: field” it nests them in an Apply to each for the body(‘Get_items’)?[‘value’] and sends 1 email for everything listed in the SP list. For example, if there are 10 items in the SP list, it sends 10 of the same email. How do I stop that and only send 1 email? Any help would be great. Thanks!
“We provide tourist e-visa,, we need a strong follow up to the tourist who enter the destination country with our company sponsored visa”
we need follow up with customers or clients to inform them to left the country or renew their visa before expiry date, otherwise we will blacklist and charge the blacklist fees from them.
Currently we are doing this all manually. for maintaining this we are spending too much time and effort.
We are looking for an automated system to check the visa expiration date from the government website and send several follow up email automatically to the clients.
If you have any idea please share with me..
I will be really grateful to you
Thanks
I am getting an error when I try to add the calculated column.
When I try to add the Days Left column I get this error message – “Sorry, something went wrong. The formula contains a syntax error or is not supported.”
Here’s m formula :
=IF((TODAY()>[Renewal or Expiry Date]),”0″,(DATEDIF(TODAY(),[Renewal or Expiry Date],”d”)))
One of your quotation mark is not right the one. Paste the formula to the notepad and make sure that all symbols are the same and then copy it back to SP
Thank you very much. Everything is ok. You are the best.
Cheers,
Hello,
1 – I wanna know how to Create HTML and formatting the table in an email.
2 – How to make the reneal or Epiry dates column decrement every day
How to automatically update a column with a type of calculated value in a sharepoint list
Very Nice..
I have only one issue which is if the expiration date passed still the email notification includes these items.
How I can remove them from the email since that I’m already using the output from the HTML Table.
I would just use where expiration date is equal to TODAY or you if you want to know a month prior then use where TODAY+30 days is equal Expiration date.
Hello,
FANTASTIC POST!!! This is exactly what I was looking to try to do for a discussion board that I created. I want to send an email that contains all the posts from the previous day.
This is the syntax for the Filter Array
@equals(item()?[‘Created’], addDays(utcNow(), -1, ‘yyyy-MM-ddTHH:mm:ssZ’))
I run my Flow and receive an email but it contains no data. When I check the history for the flow it shows that it gathers all the data from the discussion board but there is nothing in the Outputs/Body.
Any ideas or help would be greatly appreciated.
Try to use variables to check the content of the output. I am thinking something is wrong with HTML generator
Any ideas on how to due this now that you can’t use Edit in Advance Mode (sorry kind of new anything beyond the most basic Flows)?
Would love to know the same thing
Any idea how to convert the ExpirationDate format “2018-11-15T16:00:00Z” that i get in the email to something like below.
dd/MM/yyyy
Thanks
This is fantastic and works great with my test data – however I’m trying to adapt it to return a greater than or equals to value. but keep getting a failed to run. In other words my scenario is..
We have documents that after 7 years old need to be destroyed. I’m working from a date column which contains the date they were created/added to the system. The idea was that Flow would generate an email when a date reaches 7 years old BUT would continue to be included in the email if ignored, hence the greater/less than or equals to.
Message i get is
‘The template language function ‘greaterOrEquals’ expects two parameter of matching types. The function was invoked with values of type ‘Null’ and ‘String’ that do not match.’.
Have tried this with a minus symbol and and less instead of greater. Sorry I’m new to this but would appreciate any help you can give.
Many thanks
Got is working. For testing the the test data has to be exactly 7 days out. Otherwise great tutorial and it’s in use right now.
This is great. Thank you for sharing this. I foresee this solution working nicely for certificate expiration tracking.
Agree, you can enhance this functionality for any other business needs.
More articles are coming…
Very nice article and very easy to follow.
Thank you!
Hi Daler
Very nice article. I was wondering what the formula is which you used to calculate the days remaining. Would you mind sharing?
“Days left” is a calculated SharePoint column.
Formula is this:
***Code starts here
=IF((TODAY()>[Expiration Date]),”0″,(DATEDIF(TODAY(),[Expiration Date],”d”)))
***Code ends here
You could do that without condition (IF statement) but you will get an error when Expiration Date<Today. That is why I set a condition where if Expiration is greater than today, i will calculate the difference otherwise show zero.
When I use the formula I get an error but it doesn’t say what it is. Can the column name have spaces in it like Expiration Date?
Do you set Expiration Date column to Today’s Date or None?
If you are using this formula exactly how it is here, you have to make sure that all columns are named the same like in the formula.
Yes, you can have spaces in the column name.
Let me know if you still have troubles.
I am getting an error when I try to add the calculated column.
I have triple checked the column names are exactly like your example.
When I try to add the Days Left column I get this error message – “Sorry, something went wrong. The formula contains a syntax error or is not supported.”
Here’s what I am using –
Column Type
Title Single Line of text
Quantity Single Line of text
Expiration Date Date and Time
Days Left Calculated =IF((TODAY()>[Expiration Date]),”0″,(DATEDIF(TODAY(),[Expiration Date],”d”)))
Modified Date and Time
Created Date and Time
Created by Person or Group
Modified by Person or Group
Assigned to Person or Group
Hello
I have a message: Model validation failed: “The” Filter_array “action (s) referenced by” entries “in the” Condition “action are not defined in the model. “.
can you help me?
Sure, I will need to know which step are you in?
Please specify more details.
Thank you