Introduction
Power Automate, since it’s recent inception, has been a tool to provide non-developers with the mechanism to define business processes.
Out of the box, Microsoft provide some useful templates to facilitate automation tasks in SharePoint such as Approval; the ability to approve / reject edited content, such as policies and procedures.
About this Article
For all business software solutions, it is very much the case of ‘the right tool for the job’. Power Automate and SharePoint are fantastic tools, but they have limitations. We have have been asked many times why we don’t just use Power Automate instead of developing an App like Read and Understood.
In this article describes an approach for how you might try to produce a Power Automate version of Read and Understood so you can understand the challenges and limitations.
SharePoint Structure
Before we embark on creating our Flows for Power Automate we’ll need to get some structure in SharePoint. To begin with, you may have more than one document library. It’s important to note that for every library you will need one Flow to monitor the changes, be it document Create or Modified.
User Acknowledgement List
You’ll need to keep a record somewhere of every user that is required to ‘acknowledge reading’ of a document. Remember, we will want to capture not only the fact that a user has acknowledged but to also know who was asked to but didn’t - because there will be some that don’t.
The structure of the list will require a couple of custom columns. We can use the out of the box Title field for storing Document Name we’re asking them to acknowledge. You will need a People column to store the employee you’re asking to do the acknowledgement. Also, a Choice field, set required with a minimum of one option ‘Acknowledged’. You might consider an ‘acknowleded with comments’ option so that users can feed back into the editorial process if they spot ommisions or errors. Why not a boolean (yes/no) column? The problem is making the field required, you can’t because the value will always be either Y/N and we need to use to select a value.
Definition List
This is optional, but you might want to think about how you might apply different rules against different content. For example, if you have Policies, Procedures and Guides, you might only want to capture acknowledgements for the Policies and Procedures only.
By using a custom column to store groups you can define the groups of users that need to participate in the acknowledgement process. A special mention for the SharePoint group ‘Everyone except external users’. Be warned this group does not phyically exist anywhere in SharePoint or Azure Active Directory so you can’t parse it for group membership.
If you are to create rules based on content and you’re using content types, then storing the Content Id’s would be a good place to start.
Document Library
One problem with SharePoint Online is that when users edit content there is an auto-save feature in office to keep regular saves of your work. The downside is that this creates multiple versions. If our Power Automate job is going to fire on the change event then this will be a problem.
Consider enabling Major and Minor versioning in your library. You can also include publishing approval. With Major / Minor versioning we can now control the PA Flow to only run if a Major version is created.
Automation
Before you start creating the Power Automate flows consider the following:
Child Flows are a great feature, create small reusable flows rather than trying to create one large flow.
If you have Dynamics 365 with multiple environments, then you have a great advantange if you have a Dev / UAT / Prod as you can manage the development / test / live use of your flows
If you don’t have Dynamics 365 then consider how you can have a Dev site in parellel to your production flows. The same goes for SharePoint site, duplicate your production site to test and build your automation.
User Acknowledgement Request
The first part of the automation is to store those users that are being asked to acknowledge reading the document in the ‘Acknowledgement list’. This PA Flow would be an ideal Child flow taking the following values:
a comma separated string of all the users that are going to participate in the acknowledgement. This can be converted to an Array so we can loop over each user in turn.
document sharing link,
the document name, and
document version
Now we have the data, it’s a matter of looping over each user and:
create a list item in the ‘Acknowledgement list’ setting the value of the columns using the Account, Document Name and Version
Next, we need to create a link to the record created in step 1
Send the user an email providing instructions and a link to the list item + document.
Document Definition Flow
Assuming you have different rules for different documents in your library, you may want to create a child flow to determine the documents to process. This would take a parameter of the Content Type Id of the document being processed. With this we perform a query looking for record in our ‘Definition list’ that has the same id.
The record that is returned has a People/Group picker that defines the groups we wish to use. By using groups we have a simplified approach to storing users. In our flow, to get the members of a group we can use Microsoft Graph.
For each group defined in our column, repeat the process of querying Graph for the users and store them in an array that we can pass back to the parent flow.
Document Major Version Flow
Bring it all together we can now have a single flow that will manage the process of calling the child flows. Firstly, we need the flow to run when a document is modified, however, as pointed out earlier we only want to process Major versions so if it is a minor version update - simply exit.
When we know we have a major version to process. we can:
Create a sharing link - be careful as you could be giving access to users that don’t have access, intentionally.
Call the child flow for the Definition list - this will return our users that we need process
Call our User Acknowledgement Request flow with:
The list of users,
Sharing link
Document Name, and
Document version
Challenges
Permissions
While we create list items for users to capure their acknowledgement of reading a document, it’s possible that everyone to see each others items. In addition, another user could acknowledge a document on someones behalf. One solution would be to secure the item to that user, which could be done having created the record. See securable item limit below.
List view threshold
SharePoint invokes a list view threshold and while the modern interface is great at dealing with this, if you want to produce reports across your User Acknowledgement List you may find that your list breaks and you can’t produces views of data e.g. Document ABC version 1.0 even though it might be for 10 or so records, if the list view is exceeded you can’t apply the filter.
Another Version Published
It happens. A major version gets published and the emails go out only for someone needing to make a change or add something else. This will trigger a new set of emails and list items. What happens to the previous items? You could delete them.
New Users added to Groups After Document Version Published
When you have new employees join or people move between departments they’re likely to move between AAD Groups. The issue here is that we want our automation process to pick these users up as being new members of the group and get them to read and acknowledge the documents.
Error Handling
Often, this gets forgotten but it has to be considered. Microsoft document transient errors and cloud computing. When something goes wrong, and it will, you need to be able to recover a situations. This may mean rerunning an automation job but have it pick-up from where it left off - this is difficult in Power Automate Flows without parsing what was done before..
Read and Understood Solution
All of what is described above, and more, is achieved by Read and Understood. We choose not to use SharePoint lists to store data, but instead use a dedicated SQL database to avoid Microsoft limitations.
Start by reading an introduction to Read and Understood…