SharePoint lists provide a flexible way to store and organize data, allowing teams to collaborate effectively. Integrate this tool with Power Automate is a great idea, resulting in a lot of time saving for your daily tasks. However, one common challenge from this integration is the dreaded infinite loop. This occurs when a flow is triggered by an update in a SharePoint List record, and inside this same flow there is an action that updates the same list item. As result, there is a subsequential triggering the flow again and creating an infinite loop.
The infinite loop issue can cause a cascade of problems, including excessive resource consumption, slow performance, and potentially crashing the entire automation. Resolving this challenge is crucial for maintaining a stable and reliable automation process. Some posts in Power Automate Community even recommend to use an exclusive service account to manage these flows, which may result in cost increasing (more licenses may be purchased) and in more complexity to the flow.
Fortunately, there is a creative and easy solution that solves this problem without requiring any additional licenses, by using resources already available in SharePoint.
Case Study Overview
As a showcase for this solution, we will use a simple SharePoint List with a few columns, representing a customers dataset:
The List have two text columns (Client and Email) and two numeric columns (Lucky Number and PA_Version Control). For this example, let’s assume that the users will be filling only the text columns, and that the ‘Lucky Number’ (highlighted in yellow) must be generated randomly by the system (in this case, by a flow in Power Automate). When the customer’s name or email is changed, the Lucky Number is refreshed, generating a new random number.
The PA_Version Control column (highlighted in green) will also be populated automatically, and be used to prevent the infinite loop in Power Automate, as we will see later in this article. As the users may not change this column, you can keep it hidden.
SharePoint Lists Infinite loop in Power Automate
The infinite loop bug happens when we have a flow that is triggered when a List record is edited and has an ‘Update item’ action that modifies this same record:
As the ‘Update item’ action modifies the same List item, the trigger will be satisfied, so the flow will run again. The behavior will be repeated successive times, entering in an “infinite loop”. To prevent it, we need to make some small changes in the flow design.
Understanding the Version Control in SharePoint Lists
Version control is an attribute that Power Automate extracts from the List Item when using the "When an item is created or updated" trigger:
This property captures the current version of the record. When included into a List, the record receives a default ‘1.0’ version, and for each further change in this same record, the version number is increased by ‘1’ (becoming, ‘2.0’, ‘3.0’, ‘4.0’, and so on).
In Power Automate, the ‘When an item is created or modified’ trigger will return, among other data about the new or edited record, its “version”. You can find it in the raw outputs from the trigger (if you don’t know how to access the raw outputs, refer to this article).:
As Power Automate receives this information with a decimal point and as a string type (we can identify it as a string due to the quotes surrounding the 3.0, as highlighted in yellow above). Our flow requires arithmetic operations with the ‘version number’, so we will need to convert its value to integer (to learn more about data types in Power Automate and how to work with them, check this article).
So we will initialize a new integer variable called ‘version_controlling’ and assign to its value an int() expression containing the ‘VersionNumber’ property from trigger output as single argument (you can select it as a dynamic content):
The expression will looks like this: Int(triggerOutputs()?['body/{VersionNumber}'])
Comparing the versions with Power Automate
Now that we have the value corresponding to the current edit version, we need to compare it to that one stored in the SharePoint List. All changes results in a new version number, but in our process, we are storing only the version number from the modifications executed by Power Automate.
So, if the current version (stored in the ‘version_controlling’ variable) is equal to that stored in the List plus one, then we are inside the loop and we should terminate the flow, without execute any updates in the List.
In practice, we will need a ‘Condition’ action containing two statements that will be tested with an ‘AND’ operator (to learn more about how to work with conditions in Power Automate, refer to this article). The first statement will check if the version stored in the list is different from ‘null’, which will capture the non empty values only (when a new item is added to the List, the user won’t be filling this field, so it will be ‘null’ at this first moment). The second statement checks if the current edition version number is equivalent to that stored in the ‘PA_Version Control’ column plus one:
In summary:
- Make sure to set the logic operator “AND” to concatenate both statements (highlighted in yellow)
- In the right input from the first statement, add the ‘PA_Version Control’ dynamic content from the trigger (element 1)
- Make sure to set the dropdown from the first statement as ‘is not equal to’ (highlighted in green)
- In the left input from the first statement, add a ‘null’ value as expression (item 2)
- In the right input from the second statement, add the ‘version_controlling’ variable (item 3)
- In the left input from the second statement, add the following expression: add(triggerOutputs()?['body/PA_VersionControl'], 1), replacing the text in red for the dynamic content of your ‘PA_Version Control’.
This condition is testing if the ‘PA_Version Control’ value plus one matches to the modification that called the flow. If it matches, the flow may stop, breaking the infinite loop. We are also setting a condition to test if ‘PA_Version Control’ column has a null value: this is important to prevent any errors, as the ‘PA_Version Control’ column will be always null when we are creating a new List record.
Preventing the SharePoint Lists Infinite Loop in Power Automate
Now we will include an ‘Update item’ in the ‘If no’ block from the ‘Condition’, keeping the ‘If yes’ empty. The flow will update the List Item only if either (1) the ‘PA_Version Control’ value is null or (2) ‘PA_Version Control’ + 1 is not equal to the 'version_controlling’ variable. If the 'version_controlling’ is different from ‘PA_Version Control’ + 1, the flow may terminate without any further action.
In the ‘Update item’ action, we will generate the random value for the ‘Lucky number’ (we can do it by using the expression rand(1, 500)) and set the ‘PA_Version Control’:
As our business case requires that no users will be editing the ‘PA_Version Control’ column, we can be sure that it is keeping the track of the version that was created by Power Automate. Once the flow is triggered again due to this change (starting a loop), the ‘Condition’ that we created above will identify that the last update was promoted by Power Automate, so the outcome of the ‘Condition’ will be true, driving the flow to the ‘If yes’ block, which is empty – and, consequently, will break the loop.
Testing the Flow
To make sure that the flow is working, let’s add a new user “Harry P”, keeping the ‘Lucky number’ and ‘PA_Version Control’ columns empty:
As we created a new item, the flow will be triggered and in the run details we will identify that the ‘Condition’ will return as false (highlighted in blue), which is expected, as the ‘PA_Version Control’ column is empty (has a null value):
When we expand the ‘Update item’ action to check the outputs, the ‘Lucky Number’ is set as ‘83’ (highlighted in yellow) and the ‘PA_Version Control’ is set to ‘1’ (highlighted in green).
Almost at the same time, the flow was triggered again. This is expected, as during the first flow run (highlighted in green)we updated the List record, resulting in another flow call (highlighted in yellow):
At this time, however, the ‘Condition’ returned ‘true’ (highlighted in green), and the ‘Update item’ action was not executed (highlighted in yellow):
When checking our List, ‘Harry P’ properly received the 83 as ‘Lucky Number’ and ‘1’ as ‘PA_Version Control’:
If we change the name of ‘John L’ to ‘John M’, the flow will be triggered again and a new Lucky Number will be assigned to the record. The ‘PA_Version Control’ will also be reassigned to ‘3’ and the loop will be prevented:
Conclusion
In conclusion, by tracking and comparing versions, Power Automate enables intelligent updates while avoiding the “infinite loop”. With these practices in place, it is possible to extract the best use from these amazing tools!
Originally posted in http://digitalmill.net/
trice602
on:
One Minute Fixes - Outlook Trigger Not Firing
on:
One Minute Fixes - Can't Call a Flow from a Power ...
on:
Interpolated strings with Power Fx in Power Automa...