How to run a Power Automate flow only once after SP column has a value
Posted On March 24, 2021
“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.
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’:
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’:
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.
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.