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.
Extract from current 2010 workflow
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.
Power Automate is an easy and powerful tool that has allowed us to save time and money, boost productivity, streamline every day tasks and share data faster. In a digital world that is always connected and always working, businesses that use Power Automate are not looking back.
It is never too late to get started.
These day's there's a cloud service for everything. Power Automate is a user friendly way to connect all of your favourite apps and automate the tasks that you find too time consuming. With the option to create your own multi-step workflow or start from one of the many templates, it's simple to automate what you want.
Why waste time constantly checking emails when you can get a text message whenever an important contact emails you? Automating means you can use your time to work on other things, like setting up a flow that manages your Twitter. Has somebody mentioned your company in a Tweet? Let Power Automate take care of it with a flow that follows them, sends a nice reply, adds them to a spreadsheet that gets emailed to you for approval and then adds their contact to Dynamics 365.
You can connect to over 100 services and choose from hundreds of pre-made templates, tweak them, or create your own.
Power Automate is part of the Microsoft Power Platform, alongside Power Apps and Power BI. We use Power Automate everyday in our SharePoint development projects. Often in combination with Power Apps to digitise your paper forms and processes.
We live and breath Microsoft 365 and we love it. Whether you want to just ask us a few questions about Power Automate, arrange training with one of our experts, or discuss an automation project you are considering you are welcome to get in contact with us today. It doesn't cost anything to talk to us, and we are always happy to help.
We are available on 0800 6444 365 or you can send us a message via our contact page.