Phil Cave – Technical Director Adepteq
Working with dates has never been simple, just look at the issues surrounding the ‘Millennium Bug‘. There is always the time and date format to consider and then there are time zones. ISO 8601 does help by standardising the date to YYY-MM-DD, so that 2nd of March 2020 becomes 2020-03-02 no matter where you are located. Then there is the time portion, but I think i’ll leave that alone for today.
Last week Microsoft announced that they will be turning off SharePoint 2010 workflows in SharePoint Online. This step will cause many organisations a huge amount of effort to identify and rectify where 2010 workflows are being used and then recreating them.
There is little point in recreating these workflows using SharePoint 2013 workflow as these are nowhere near as powerful or future proof as the Flows that can be created using Power Automate.
Power Automate also has the advantage that it is not locked into SharePoint but can work with data from over 300 other systems using the available connectors. New connected are being added all the time.
One of the things that we do at Adepteq is offer annual support contracts for SharePoint Online and also for the entire Office 365 suite. These contracts are stored in our helpdesk system which we created in SharePoint and have been using for many years now. I think we first created it on SharePoint 2010.
Our contract renewal reminders are managed using a long running SharePoint 2010 workflow. Unfortunately, this workflow will stop working in November, so I will have to rewrite it soon.
It is a simple workflow that uses the out of the box action “Find Interval Between Dates” to check how many days are remaining until the end of the contract. If there are 30 days remaining an email is sent to our sales team with a copy to the support team. We can then send a proposal for a new contract to the customer.
I shall recreate this functionality using Power Automate. This new Flow will run every night at 1am to check the number of days remaining. If there are 30 days remaining, then a notification will be sent to our sales team so that they can create the new proposal.
Power Automate could be used to create the proposal automatically and send it directly to the customer, but that is for a future blog post.
First create a new Flow using “Scheduled – From Blank”
We need the Flow to run once every day. Here I chose 3am as the start time but you can start at any time that is convenient, it just depends on when you want the email reminder to be delivered.
Next we need to add a step to get the list items from the contracts lists. You will then need to specify the site address and the list name.
You need to remember that by default ‘get items’ will only return the first 100 items, which is not good if you have more than 100 contracts. To return more than 100 items you will need to go into settings and enable pagination and then set the threshold to a suitable value. In the example below I have used 5000.
let’s initialise a variable to store the number of days remaining for the contract.
Now add an ‘Apply to Each’ step that we will use to enumerate each list item.
We now need to calculate how many days are remaining for each contract so that we can send a reminder email when only 30 days remain.
This is more complicated than it is in the 2010 workflow because there is no ‘Find Interval Between Dates’ action in Power Automate. So instead we have to do the calculation using the Ticks function. This returns the number of nanoseconds since 1st January 1601. We need to get the difference in nanoseconds between now and the contract start date, then convert that into the number of days. Finally, take that number away from 365 to give the days remaining until the end of the current contract.
To convert nanoseconds to days we just have to divide by 864000000000.
This is all done using the expression 365-(div(sub(ticks(formatDateTime(utcNow(),’yyy-MM-dd’)),ticks(item()?[‘ContractStartDate’])),864000000000)) Just remember to replace ‘ContractStartDate’ with the name of the column that contains your date.
Now the variable ‘daysLeft’ contains the number of days remaining for the current contract, we can use a simple condition to check the value and take action. In my example below, if there are 30 days remaining an email reminder is sent. If not, then no action is taken.
This Flow can now easily be developed further to create a task to the sales team and to follow up if the task is not completed. If the customer decides to renew the contract then the account team could be notified to raise an invoice…
Read our recent post on the retirement of SharePoint 2010 Workflows here.
If you would like to learn more about Power Automate, or any products related to Microsoft 365 please visit our website. We are also available to speak to on 0800 6444 365 or via info@adepteq.com.
