Avoid Infinite Loops Using Trigger Conditions with SharePoint
If you use SharePoint Lists and Libraries a lot, you probably need to automate tasks and processes related to that SharePoint content. If you don't already, you should know about some simple techniques that can help you avoid race conditions or infinite loops in your flows.
Why is This Important?
Any time we are automating things for SharePoint content, we have options to start that thing whenever an item or document is created, modified, or created or modified. Think of a scenario where you want to start a process, maybe to send a notification or approval to someone whenever a document is updated. If you just leave it at that, then every time the document is touched or metadata is updated, your process will run. This means you could end up with an e-mail for every document update that occurs. This scenario could have additional implications if your process is long-running or results in activity outside your organization. Let’s say you want to run it through multiple stages of approval or send the content of the document to Twitter. Both of these scenarios could end up a mess if our process ran multiple times when we don’t want it to. Finally, we have to consider the built-in limitations of Power Automate. Every time your process is triggered unnecessarily, you’re wasting execution cycles in your Power Automate plan/license.
Let’s Talk About Remedies for This
How is your automation triggered? First think about when you want your process to kick off. Will it be something that runs whenever an item or document is created? Should it only start when the document is updated? What if you want to have a user initiate the process when they are ready? The only trigger of these three that cannot natively fire multiple times for the same item is on the creation of an item or document. So in the other cases, we need to consider what might happen if it does trigger more than once.
For this piece, it’s important to understand the desired user experience. How do the users interact with the items or documents in SharePoint? What is their current workflow? Can we make anything better?
One Option is Concurrency Control
The first setting I will touch on which is often overlooked is the ability to control the concurrency of your flow. In the trigger action, you can limit the number of simultaneously running instances of your flow.
By default, this setting is off. This means that in a list of 1000 items, if 1000 items are modified at the same time, 1000 instances of your flow can run at the same time. This also means that if you update 1 item 100 times in a very short timeframe, you could have 100 instances of your flow running for that item at the same time. For the first scenario, this concurrency is ok most of the time. For the second scenario, this could be a problem and could be a wasteful use of execution cycles.
So, let’s talk about what limiting concurrency would mean in both scenarios.
For the extreme case where we limit concurrency to 1 (one), in the first scenario, it simply means that the flow for each of the one thousand items would be queued and processed in the order. In this case, if the flow does something simple like send an e-mail, and then terminates, this works ok.
However, if your process does something like start an approval, and must wait, then you’re potentially delaying all the other item approvals, because we can only do one instance of the flow or one approval of one item, at a time.
In the second scenario, we have the potential for one hundred instances of the flow to run, one after the other, on the same item. This would be desirable in a scenario where each update resulted in an update to another field on that item... maybe you’re building a text block based on updates to metadata fields, maybe you’re generating an audit trail of sorts and you want to record the individual updates in order. Doing these updates in sequence would ensure that your text block or audit trail is constructed correctly and in order.
However, if your process is again used for a more complex scenario like collecting approval, the last thing you want to do is queue up one hundred approvals for the same item. In this scenario, it would be beneficial to consider what action should really trigger approval and only fire when this happens.
In all the scenarios above, we must be wary of the scenario where we’re triggering an item or document modification, and our flow makes a subsequent update to the same item or document. There is a big potential risk here for creating the dreaded infinite loop.
The last thing I will note about the concurrency setting is turning it on cannot be undone. It’s stated very clearly in the settings of the trigger action but note that if you turn it on and decide it’s not what you want, then at the very least you’ll have to rebuild your trigger action and may be required to update subsequent actions based on that trigger action change.
Understanding Trigger Conditions
How can we avoid some of the pitfalls I’m describing? I encourage you to learn to know and love trigger conditions. Trigger conditions are an extra set of settings that are available on SharePoint List and Document Library-related trigger actions in Power Automate. These would be things like `When a file is created`, `When a file is created or modified`, etc.
Setting trigger conditions will give you the control needed to stop your flow from running unnecessarily and keep you within the execution limitations set for Power Automate.
Let’s jump into a simple example. Let’s assume a scenario where a user will manually request approval for a document. The first challenge to recognize is the best way to do this is in two parts.
First, we’ll create a flow that is manually triggered on a document or item by the user.
Second, we’ll create a flow that is automatically triggered when the document is updated. This flow should create an approval, wait for the approval response, and then send an e-mail notification with the response and comments. In this scenario, we only have one approval process, and we only want that process to run once for any document.
Now, you might be asking... why must we have two flows? Why can’t we perform the approval and notification in the first flow? Well, you can do that. And in fact, Document Libraries even have a built-in “Request sign-off" flow that can be enabled. But... and it’s a big one... trigger conditions don’t work on the SharePoint manually triggered actions... bummer! So just using this scenario means that users will be able to initiate the flow from the Power Automate menu as many times as they want, sending multiple approvals for the same document.
For our first flow, we’ll create one that is manually triggered, called `Request Approval`.
This flow is going to do something simple. We’re going to use this flow to set metadata on the target document indicating that approval has been requested. First, we’ll do a check to ensure no other approval is in process. Next, we’ll set our Approval Status to `Requested`.
So, let's first add an `ApprovalStatus` column to our library.
Now we’ll add our condition and update to the document metadata. In our condition, we’re checking to make sure that the status is not already `Requested` and not already `Pending`. If this condition is true, then we will update the `ApprovalStatus` column to `Requested`.
Now, we’re ready to create the `Approval Process` flow that is triggered whenever our document is modified.
Next, we’ll configure our trigger action in our new flow to point to our document library.
Now, let’s add a trigger condition that will stop the flow from running multiple times on the same document at the same time. In our flow, we’re going to click on the trigger action settings and we’ll set the following trigger condition:
We only want to run this approval process if it has been requested. If `ApprovalStatus` is anything else we don’t want to run. Let’s add a trigger condition to match this criteria.
The next thing we want to do is update the `ApprovalStatus` value to `Pending`, indicating that we are now in the process of approving the document. This will stop any subsequent runs of this flow from triggering while an active instance is running for this document. This should also stop the manually triggered flow from setting the `ApprovalStatus` to `Requested`. To do this we’re going to send an HTTP request to SharePoint to update this value for our document.
Now we create our approval, wait and register the response, send a notification.
Finally, we can set our `ApprovalStatus` to `Complete`, using another Sent HTTP Request to SharePoint action.
Now let’s test this and see if the flow has been limited by this trigger condition...
We can see that after requesting approval our status has changed to `Requested` and then to `Pending` once the approval process has started.
Now, if we attempt to request approval again, and we inspect the flow run, we can see that it did not initiate another request. And we can see that our Approval Process flow still only has one running instance.
Without going into too much detail, this same principle can be used to stop the process from running ever again for the document, simply by defining a final ApprovalStatus value and then including this as condition criteria and/or trigger condition... i.e. don’t run if Status = Finalized.
This example is intended to cover scenarios where one or more users might start a process without knowing it’s already underway. This will also stop the Approval Process flow from running any time a document is updated. Our trigger condition is very narrow and this is the key.
In my experience helping clients, nine of ten SharePoint-related automation fit this scenario. You may have other scenarios that do not give you control over the process like this example. In the other scenarios where the process is a quick update and done, there are some other options such as:
- Including a trigger condition to check that the last modified user is not the same user that runs the flow. Having an “automation service account” can be useful in this scenario.
- Creating an additional copied version column on the library or list, copy the item version to the copied version every time your flow updates the item. Then add a trigger condition to check that the item version does not match the copied version.