Microsoft Flow: Check for existing items in SharePoint and configuring (Approval) timeouts
For a customer, I was asked to set up a Microsoft Flow PoC for an approval process where a SharePoint site owner must be given the option to maintain or to delete a site when that site has been marked as inactive (no activity during the last six months).
Situation
This customer uses Office Dev PnP for its site provisioning on SharePoint Online. All site requests are stored inside a SharePoint list with its status (along with other site information).
We also had a scheduled PowerShell ‘healthscript’ which checked the tenant for various possible ‘health’ issues. One of these checks was if there was no activity on the site for six months or longer. The script wrote its output to an Excel file on which we had to take manual action to contact the site owners, ask whether the site should be maintained or deleted and perform that action on the site.
We wanted to automate the process of deleting/maintaining an inactive site, so I was asked to look into a possible solution with Microsoft Flow.
The process
Before adding various actions, conditions, switch cases etc., I had to define the process of the Flow. Since I did not have the ability of using Visio, I used a great free alternative: draw.io.
This is a simple example of a draw.io diagram of the current Healthscript process:
I wanted to eliminate the Excel output for the inactive sites and replace this with an automated approval process in Microsoft Flow.
Since we had a SharePoint list in which we stored all sites and its information, I had the idea on letting the Healthscript write the inactive status in that list and creating a Flow that would run when an item in that list has been changed. However, because the Flow would run on change, we could not write output from the Flow back into that list, because that would trigger the Flow again.
For that reason, I created a second list called InactiveSites which contained the following information:
- URL: This is the unique identifier of the site
- Notifications sent: This is the number of approvals that have been sent to the site owner. The customer wanted to give each site owner a maximum of 4 retries, each lasting a week. After the 4th approval with no response, the site would be automatically deleted.
- May be deleted: This is the output of the approval process on which a follow up action has to be created (not yet in scope for this PoC).
This lead to the following overall process:
And the following Microsoft Flow process:
I will not go into full detail of the entire Flow, but only the actions that took me a bit longer to figure out, namely checking if the item already existed in the InactiveSites list and the maximum approval tries.
You can always leave a comment if you want to know more and I’ll be happy to assist you.
Check for existing items in SharePoint
One of the actions I had to perform was to check if the site existed in the InactiveSites list. This can of course be done by using the HTTP API action inside Microsoft Flow, but there is a much easier way around.
First, you have to initialize a Boolean variable using the Initialize variable from the Variables connector and set its default value to false using the Expression tab. Simply type in ‘false’, select it and click OK:
After that, you can use the Get items action from the SharePoint connector.
Why not use the Get Item action? Because that action requires the Item ID, which you don’t have at this moment.
You configure this action with the Site Address, the list name and (after clicking on Show advanced options’) a Filter Query. The Filter Query is based on ODATA as used in REST requests. For me, I used the Url field in the Inactive Sites list to compare with the Url field in the Site Registration list. You can add the Url field from the destination list as Dynamic content from your ‘When an item is created or modified’ trigger:
Don’t forget to put single quotes around the Dynamic Content attribute, otherwise your action will fail!
To make sure you always get a single item returned by the query, you can edit the settings of the action (by clicking the three dots on the top right of the action), enable Pagination and set the Limit to 1:
Now that your query is finished, you have to do something with the outcome of the query. Therefore, you have to add the ‘Apply to each’ action, which is basically a foreach loop that will process each item that has been found which is in this case a maximum of 1.
If the Flow enters the Apply to each action, it means that an item was found and no item should be created anymore in the InactiveSites. Within this action I had to change the variable itemExists from false to true using the Set variable action from the Variables connector:
If no item was found, the Apply to each action will be skipped and the itemExists variable will maintain its false value. In this case, a new item had to be added to the InactiveSites list. This can be done by using a Condition which checks if the variable itemExists is equal to false. If this is the case a new item had to be added with the Create item action from the SharePoint connector. This action can be configured with the Dynamic Content attributes from the ‘When an item is created or modified’ trigger:
To empty fields from your Flow, you can use the ‘null’ expression.
Now that the item exists for sure, we need to run our Get items action again in order to get the corresponding Item ID from either the existing item or the item that was just created using the exact same configuration as the previous Get items action. Make sure you name the action in such a way you can easily recognize it in a later Flow stage.
In my case, I called the first Get items action ‘Get item from Inactive sites list’ and the second Get items action ‘Get item from Inactive sites list after possible creation’.
This way, you can extract the ID from the Dynamic Content attributes from your corresponding action without doubting which action to use.
Timeouts and how to handle them
How often does it happen that an approval is sent and the approver doesn’t respond (in time)? You probably know the answer to that question…
To keep track on your approval processes in Flow, it is possible to set a maximum duration on the approval action (and many other actions as well). You can do this by enter the Setting of the action and configure the Timeout section.
As you can see in the screenshot, you have to define the timeout period in ISO8601 format which stands for . But luckily, Microsoft Flow provides you with an example: PT1D for a 1 day timeout.
Strangely, if you configure your action to use a 1 day, 2 day or even 10 day timeout according to the example Microsoft has provided your action will fail:
It seems like the example Microsoft is giving you is not correct. I looked into the ISO8601 format and found out the following:
Durations define the amount of intervening time in a time interval and are represented by the format P[n]Y[n]M[n]DT[n]H[n]M[n]S or P[n]W as shown to the right. In these representations, the [n] is replaced by the value for each of the date and time elements that follow the [n]. Leading zeros are not required, but the maximum number of digits for each element should be agreed to by the communicating parties. The capital letters P, Y, M, W, D, T, H, M, and S are designators for each of the date and time elements and are not replaced.
- P is the duration designator (for period) placed at the start of the duration representation.
- Y is the year designator that follows the value for the number of years.
- M is the month designator that follows the value for the number of months.
- W is the week designator that follows the value for the number of weeks.
- D is the day designator that follows the value for the number of days.
- T is the time designator that precedes the time components of the representation.
- H is the hour designator that follows the value for the number of hours.
- M is the minute designator that follows the value for the number of minutes.
- S is the second designator that follows the value for the number of seconds.
For example, “P3Y6M4DT12H30M5S” represents a duration of “three years, six months, four days, twelve hours, thirty minutes, and five seconds”.
(from: Wikipedia)
It seems that T is a Time component that uses H(our), M(minute) and S(econd).
So, for 6 days your ISO8601 format would be P6D.
Now that the timeout can be configured correctly, we can now configure what happens when an approval is overdue. To do this, we can create a new action after the Send approval email action. This action is the default action that happens after the approver has responded within time. Most of the times, this will be a Switch case that handles the various outcomes of the approval task. After that action has been completely configured a new step has to be inserted after the approval action (not after the action you just configured). You can now select ‘Add a parallel branch’ followed by the familiar steps.
With a parallel branch, you can run multiple steps at the same time. We don’t want to this, we want a specific branch to run after the approval process has been completed (in this case, the Switch step) or a specific branch to run after the approval process is overdue (timed out).
To do this, enter the Configure run after configuration (by clicking the three dots on the top right of the action). Within this configuration you can select when a specific step has to run:
- After the previous step is successful
- After the previous step has failed (error handling)
- After the previous step is skipped
- After the previous step has timed out
For our overdue approval process, we want to select ‘has timed out’:
The step will now only run when the previous step (Send approval email) has timed out. The default Configure run after configuration is always ‘is successful’, which means the Approval Outcome step in my Flow will run after the Send approval email action is completed within time.
You can use this for error handling as well, as the action can also be run if the previous action has failed or skipped. Please note that when you run your Flow and it reaches its ‘error handling’ action, the Flow will show up as failed, but it did actually finish in its ‘has failed’, ‘is skipped’ or ‘has timed out’ Configure Run after action.