Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

How to run a Power Automate flow only once after SP column has a value

Posted on March 24, 2021April 14, 2021 by Tom

“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.

Power Automate run after column has value

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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

6 thoughts on “How to run a Power Automate flow only once after SP column has a value”

  1. Ben says:
    March 26, 2021 at 2:03 pm

    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?

    Reply
    1. Tom says:
      March 26, 2021 at 7:26 pm

      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/

      Reply
  2. Ben says:
    March 30, 2021 at 3:11 pm

    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.

    Reply
    1. Tom says:
      March 31, 2021 at 6:35 pm

      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.

      Reply
  3. Emma says:
    April 12, 2022 at 1:00 am

    I have been struggling with using trigger conditions with Content Approval on a list.

    The Flow should run only once, on modified, if the item has been approved. RequestStatus is set to Complete during the run and then the Approval Status is reset to Approve as the last step

    I have tried variations of the following three conditions and any time the ModerationStatus is included as a trigger, the Flow does not trigger.

    @not(equals(triggerOutputs()?[‘body/Created’], triggerOutputs()?[‘body/Modified’]))
    -AND-
    @equals(triggerOutputs()?[‘body/ModerationStatus’], ‘Approved’)
    -AND-
    @not(equals(triggerOutputs()?[‘body/RequestStatus’], ‘COMPLETE’))

    I don’t know if the internal name has changed but I’m guessing you created your own ApprovalStatus column instead of using Content Approval?

    If so, does that mean what I am trying to do is not possible?

    Or am I missing something?

    Reply
    1. Tom says:
      April 13, 2022 at 2:43 pm

      Hello Emma,
      I used my own custom column, not the content approval one. If you’re not sure about the condition then create them using the Filter Array action: https://tomriha.com/a-simple-way-to-create-a-trigger-condition-in-power-automate/.
      And if it doesn’t help then I’d try the trigger conditions one by one, to check which one is blocking the run which can then help you find the issue.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Now available:

The Ultimate Power Automate expressions cheat sheet
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme