In this blog I will show how to take action based on a value in a Microsoft Lists column using Power Automate. A typical use case for this pattern is a change in status value to completed. In my example I will be using the Microsoft Lists Issue Tracker template and when the “Status” column changes to “Completed” a email will be sent to the person who logged the issue.

Important: Versioning must be enabled in your list so you can compare current and previous column values. Versioning can be found in “Settings” > “List settings” > “Versioning settings”.

Create a new Flow by clicking “Automate” > “Power Automate” > “See your flows”.

Click “New” > “Automated-from blank”.

Provide a Flow name, I.e. “Issue Tracker – Status is complete”, search for SharePoint and select the “When a item is created or modified” trigger and click “Create”.

Set the “Site Address” and “List Name” to you target site and list.  

Click “New step”, search for “Variable” and select the “Initialize variable” action.  

Set “Name” to “varPreviousValue” and “Type” to “String”.

Click “New step”, search for “Condition” and select the “Condition” action.  

Set the condition to be “Created”, “is not equal to”, “Modified” from the “When an item is created or modified” trigger . This condition tests to see if the Flow is running against a new or an existing item.

In the “If yes” branch, click “Add an action”, search for “SharePoint” and select the “Send an HTTP request to SharePoint” action.

Set the:

• “Site Address” to your target site
• “Method” to “POST”
• “Uri” to /_api/lists/getbytitle('Issue Tracker')/items(@{triggerOutputs()?['body/ID']})/versions
  
  Where ‘Issue Tracker‘ is the List name
• Headers to “Content-type” and “application/json”

Test the Flow and when complete copy the body.

Click “New step”, search for “Data Operation” and select the “Parse JSON” action. This action will extract the previous version data.

Set “Content” to “body” from the SharePoint “Send an HTTP request to SharePoint” action; paste the “body” outputs into “Schema” and click “Generate from sample”.

Click “New step”, search for “Data Operation” and select the “Parse JSON” action. This action will extract the previous version data.

Click “New step”, search for “Variable” and select the “Set variable” action.  

Set “Name” to “varPreviousValue” and “Value” to the following expression: body('Parse_JSON')?['d']?['results'][1]['Status']

The expression uses the data extracted from the “Parse JSON” step (body(‘Parse_JSON’)?[‘d’]?[‘results’]) and looks at the previous version ([1]) of the “Status” column ([‘Status’]).

Click “New step”, search for “Condition” and select the “Condition” action.  

Set the condition to be “varPreviousValue”, “is not equal to”, “Status Value” and “Status Value”, “is equal to”, “Completed”. This condition makes sure that the “Status” column has changed to “Completed”.

In the “If yes” branch, click “Add an action”, search for “Office 365 Outlook” and select the “Send an email(V2)” action.

Set “To” to “Issue logged by email” and “Subject” and “Body” as you see fit.

My completed Flow looks similar to the image below:

Save your Flow and test by changing the “Status” of an existing item to “Completed”. If all goes well you will receive an email from Power Automate.

This Flow has some complicated bits, through JSON and expressions, that move this pattern from “no-code” to the “low-code” camp but hopefully this example and the many others that exist within the Tech Community can make this type of Flow accessible to all citizen developers.

Thanks for reading.

NY

• Microsoft Lists
• Power Automate