Microsoft Flow has always struck me as having the potential to be the most powerful and useful component of the Office 365 suite, but has always come up just short with the functionality I’ve required. The post below outlines a custom flow I’ve recently created. Hopefully this is the first step of moving away from SharePoint Designer workflows…
Background
I recently had a request to setup a document library in SharePoint Online to store photos. These photos would be of customers and other members of the public, who give their written consent when the photos are taken. The policy in our company is that after 5 years we need to renew this consent, so our marketing team wanted to be notified once 5 years had passed from the date the photos are uploaded.
Initially we looked at two options to achieve what we wanted:
- Office 365 Retention Policy – these are built-in policies that allow you to create labels and apply them to items in selected locations within Office 365. Items with these specific labels can then have retention policies applied to them along with options to delete the item(s) or notify specific users when this period elapses. Currently you can only apply labels to SharePoint sites and there is no option to apply labels on individual libraries. More details on Office 365 retention policies
- SharePoint Retention Policy – these have been built into SharePoint since 2010 and are still available in SharePoint Online. They allow you to set the retention period for items in a library, or a specific content type, and then run an action on these items once the retention period has elapsed. I’d used these before but there are some limitations. Firstly, even though you can set a retention policy to trigger a workflow this can only be a SharePoint 2010 workflow and not a 2013 version, which limits functionality. In SharePoint Online you have no control over when the retention jobs run. You also cannot access them in the event of any problems occurring. They run once a week but if anything goes wrong your only option is a call to Microsoft. More details on SharePoint retention policies
Step forward Microsoft Flow…
In order to ensure future compatibility and provide the flexibility to perform custom actions I decided to try Microsoft Flow. I’d used Flow in the past and it had never had all the functionality I’d required. I knew Microsoft were constantly adding new features so thought maybe now was the time for it to shine…
Flow Design
Recurrence
This is the trigger action that starts the flow. One of the advantages that Flow has over SPD workflows is that you can easily schedule flows without having to wait for items to be created or edited. For my flow I set this to run every day at midnight.
Initialize Variable
This creates a string variable to hold the date that the documents will be tested against. In my case this is five years ago so the value for this variable is set to addDays(utcNow(),-1825)
Get Files & Filter Array
The next two steps in the flow are performing tests to find the documents that need to be flagged up. In plain English the query I want to perform is:
Show all documents that were created over 5 years ago and have not had this flow run against them before
The first part of this query can be done using the Filter Query field in the Get Files (properties only):
This performs an Odata query on the library looking for any documents with a created date of less than (lt) the date held in our 5 year variable. In order to check if the flow has run before there is a choice field in my library called RetentionFlowRun. The options in this choice field are blank, Yes and No. I wanted to also query this field in the Filter Query but at the moment this isn’t possible in Flow.
In order to get around this limitation the Filter Array step filters the output from the step before. This time it looks for any documents that have No as the value for the RetentionFlowRun choice field:
Please note that for this to work you need to select the Value entry for your choice field (i.e the second item shown below)
Apply to each – Update file properties
The last step in the flow updates some fields on the documents that it has filtered. It sets a Consent Renewal field to Yes, which is used in a custom view on the library in SharePoint. Our marketing team can use this view to see all photos that have been marked as past the 5 years. It also sets the RetentionFlowRun field to Yes and sets the RetentionFlowRunDate to utcNow().
Conclusions
Hopefully this has been a useful example of a custom retention flow. As discussed there are other options if you want a more basic OOB solution. However, Flow will enable you to perform more granular actions with the documents that have passed your retention period.