Adding data to SharePoint List creates infinite update loop

[OJC]

Hello,

I am developing an automated cloud flow for MS Lists that adds a timestamp to a designated column (date/time type) when the "Status" column (choice type) is set to "Approved".

Below is the flow, with the blue arrow showing what's causing the infinite loop. Please note that the compose following the trigger is not doing anything currently (was trying to use it to debug).

The issue is that when the timestamp is successfully added, that triggers the flow again because it's a "modification" to the list. This causes the infinite update loop where the timestamp simply updates every 30 seconds after the item's status was set to "Approved".

The "Generate Timestamp" compose action uses the "utcNow()" function to get the current time when the update is made.

I have been playing with trigger conditions to try and limit when it runs. I only want the flow to run when either a new item is created with a status of "Approved", or when an existing item's status is changed to "Approved". Any time I add a syntactically valid trigger conditions, the logic doesn't work, and the flow never runs.

How can I prevent this infinite update loop from happening? 

[LaurensM]

Hi @OJC,

Using the created or modified Power Automate trigger will indeed cause an infinite loop when updating a record within the same list. One way to avoid this is by adding a trigger condition that validates whether the flow should run. In this case it is important to find a condition that allows both scenarios to happen (e.g. a run on creation / modification) but only when necessary.

A condition that comes to mind is to only run the flow when both the Approval Date is empty (as this indicates it still has to be updated -> flow hasn't run yet) and the Status is equal to approved (flow should run). In this case the flow will run when those conditions are met, regardless if it is a record modification or creation. At the end of the flow run you update the Approval Date field, which will avoid any future runs for that specific record.

@and(equals(triggerOutputs()?['body/Status/Value'], 'Approved'), empty(triggerOutputs()?['body/ApprovalDate']))

The column names Status and ApprovalDate will need to match your SharePoint technical names - adjust if necessary. One approach of finding the technical name is by sorting the list based on that field & fetching the name from the URL. 

A trigger condition can be found within the trigger settings:

At the bottom of the settings page you will be able to add the condition:

---

If this solves your question, would you be so kind as to accept it as a solution. ✔️

If you liked my solution, please give it a thumbs up. 👍

Connect with me: LinkedIn | Blog

[OJC]

Thank you for your solution @LaurensM. It works as intended.

One caveat to this is that I'd like to keep the approval date in the list item even if that user's status is no longer approved. This conflicts with the use of empty() in the trigger condition because Approval Date may not always be empty if the user has been Approved before.

Is there a way to circumvent using "empty()" to account for this possibility? I have tried referencing previous value in the condition ("Status/Value@previousValue") but haven't figured it out.

[LaurensM · Accepted solution]

@OJC,

To better understand the exception - what occurs in this scenario? As I understand it the approved status has been expired. Does a re-approval differ from a normal modified record? (e.g. a difference between pending -> approval and expired -> re-approval).

As the flow will overwrite the Approval Date, you could clear the Approval Date field when changing the status to approved again.

Should my assumption of expiration be correct, an alternative would be adding an expiration date field that is the approval date + x amount of days / months (field is updated within this flow). This expiration date is then used to extend our condition:

@and(equals(triggerOutputs()?['body/Status/Value'], 'Approved'), Or(empty(triggerOutputs()?['body/ApprovalDate']), lessOrEquals(triggerOutputs()?['body/ExpirationDate'], utcNow())))

---

If this solves your question, would you be so kind as to accept it as a solution. ✔️

If you liked my solution, please give it a thumbs up. 👍

Connect with me: LinkedIn | Blog

[OJC]

I made some minor situational changes to this and it's working as intended. Thank you so much for your help!