Initiate flow when status Changes

[Vstar19]

Hello. 

I have a sharepoint list with lots of columns e.g

First Name | Last Name | Division | Status 

John          | Smith          | Finance | Active

Joe            | Bloggs         | HR        | Inactive

John          | Doe             | IT          | Draft

Users on lots of occasions change their Divisions, e.g. John Smith might move to Marketing etc

Their Status is what i am interested in, it tells me that this person works in this division, and their status within a network is either active, inactive or draft

I need to create a flow that notifies me ONLY when the status changes from Draft to Active.  

At the moment, i have a flow to say, when a new item is added to the sharepoint list, send me an email. However, i need to modify this to include items that are edited. 

The issue is, if a users changes division and is active, i dont want the flow to initiate. I only want the flow to work when someone changes the field from Draft to Active and to ignore any other modifications..  I tried to use when item is modified, but i dont know how to only make it look at when status changes from Draft to Active. 

Hope that make sense. 

[RMCoder]

There are two parts to solving for this: Trigger Conditions, and an additional SharePoint column.

• Trigger Conditions will prevent your flow from running unless criteria are met. You define this in the settings of the trigger and format it using a normal expression, prefixed with '@'.
• Power Automate can't capture what the status was before someone changes it, so you need to create another column to retain that on every status change.
  • We'll hide this column on your form so user's don't know it exists.

In my example, I created a duplicate of my Status choice column called StatusHistory with the same choices and default value.

My trigger condition checks that the Status was changed to "Review", and that the StatusHistory is "New". In your case, this would be "Active" and "Draft", respectively:

@and(equals(triggerBody()?['Status/Value'], 'Review'),equals(triggerBody()?['StatusHistory/Value'],'New'))

I could also check for situations where somehow a new item was immediately set to "Review" and so it doesn't have a StatusHistory with an or clause:

@and(equals(triggerBody()?['Status/Value'], 'Review'),or(equals(triggerBody()?['StatusHistory/Value'],'New'),equals(triggerBody()?['StatusHistory/Value'],'')))

This probably isn't necessary since I have a default value of 'New' for my StatusHistory column --just like my Status column-- but I'm safe in case that is somehow circumvented.

One of the most crucial pieces, is to update your item's StatusHistory to match the updated Status value somewhere in your flow so that the historic change from Draft to Active is accurate.

I like to do this right away to prevent any other updates I might make to the item from re-triggering the flow.

Lastly, we need to hide the StatusHistory column from views and forms.

• In the List Settings, under Advanced settings, enable "Allow management of content types?" (set to Yes).
• Go back to List Settings and click the name of the content type (probably Item)
  
• From the List Content Type settings, click the name of the column you wish to hide, and then on the next page select the radio bubble for Hidden (Will not appear in forms)