“My Power Automate flow should run only once, and it should trigger only after the user selects a value in a SharePoint column.”
There’re situations when your Power Automate flow should run only once, and it shouldn’t be on ‘create’. A flow to send an email when user updates item status to a specific value. An approval process that will start only after user selects an approver. Or a flow to send notification to stakeholders to prepare them for an incoming request. All these Power Automate flows should run only once when a SharePoint column has a specific value.
You can’t trigger them on ‘create’, it’s too early. But at the same time you don’t want to start them on each update (and send another notification or create another task). It must run only once, and only if a column was updated to the desired value.
You’ll need to add two trigger conditions to your ‘modified’ trigger.
Trigger condition to start the flow
The first trigger condition will start the flow only if the column has the right value (or is not empty). In all other situations, when the condition returns ‘false’, the flow shouldn’t start.
The easiest way to create such trigger condition is to use the advanced mode in ‘Filter array’ action. To show an example: a trigger condition to start an approval flow only if there’s a user in a people picker column.
@not(equals(triggerOutputs()?['body/Approver'], null))Trigger condition to let the flow run only once
For the second trigger condition, the one to ‘disable’ the flow after one run, you’ll need assistance from SharePoint. Trigger conditions have access only to the current item data, therefore, you must store the run information in the item itself. You can create a new column, e.g. a Yes/No column ‘FlowDidRun’:
@equals(triggerOutputs()?['body/FlowDidRun'], false)Or you can use an existing column. As an example, approval processes have very often a column to track the request status. That column can tell you also if the flow was already started or not. The condition below will trigger a flow only if ApprovalStatus column is equal to ‘Not started’:
@equals(triggerOutputs()?['body/ApprovalStatus/Value'], 'Not started')Note: In both situations, the flow must update the column during the first run and it should never have the original value again. If it’s a Yes/No column, update it to ‘true’ value. If it’s a status column, it should reflect the new process status, e.g. ‘In approval’ or ‘In progress’.
Use both trigger conditions
Now you can take the ‘start’ and ‘stop’ trigger conditions and add them to the trigger. Each condition must have its own row to be evaluated with AND operator (both must be true).
Until user updates the 1st column, the first condition will be ‘false’. After user updates the column, both conditions will be ‘true’ and the flow will start. But the flow will update the second column during the first run. From that moment, the 2nd condition will be ‘false’ and the flow won’t trigger again.
Summary
When you combine the Power Automate ‘modified’ trigger with the requirement to run only once, you must define the borders. You shouldn’t only define when it should run, but also when it should stop running.
Looking on the two conditions above, they’ll be both ‘true’ only in one situation. When the specific column is updated by the user, but the second column is not yet updated by a flow. The single flow run.
Of course, the conditions above are just an example from an approval process. You can use any condition on any of the available columns to trigger the flow. And since you’ve got also a ‘stop’ condition, you won’t have any issues with the infinite trigger loop.
Thanks for this post – somehow I’d never noticed the Trigger conditions before – they open up a lot of possibilities!
One question for you – I had a similar situation where I had a flow that needed to run when a column contained a particular value. After it ran, it was supposed to update the properties to change that value so it wouldn’t accidentally run more than once.
The problem was that sometimes the update would fail because the file was locked for editing. Then the flow would run and re-run every time the file was saved until it was finally unlocked and the properties could be updated.
Have you encountered this issue?
Hello Ben,
I use the ‘Configure run after’ feature in these situations. If the ‘Update file properties’ action ‘has failed’, I’d configure a ‘Delay’ action to wait for 60 minutes (that should be enough) and then run another ‘Update file properties’.
Recently, I saw a solution using HTTP request with a header “Prefer: bypass-shared-lock” to bypass the file lock. I didn’t try it yet, but it looks promising: http://www.cleverworkarounds.com/2021/02/21/how-to-clear-annoying-excel-file-locks-in-power-automate/
Thanks, Tom. In a situation where the file is locked but a user is still actively working on it, wouldn’t this create a situation where you would have multiple flow starting each time the user saved changes? And once the file is unlocked, each of those runs would proceed? So if the flow included a notification email, it would be sent multiple times?
I like the idea of using an HTTP request. That shows a lot of promise for my scenario.
You’re right, if users are actively working on the file then they might do a lot of updates without changing the metadata = multiple flow runs. My approach would be to explain that as a limitation to the users, tell them to set the ‘starting’ metadata column only after they finish working on the file.