Take action based on value in Microsoft Lists column using Power Automate

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

Versioning

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

Automate, Power Automate, See your flows

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

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

When an item is created or modified

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

Initialize variable

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”
Send an HTTP request to SharePoint action

Test the Flow and when complete copy the body.

Send an HTTP request to SharePoint output

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.

Parse JSON

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']

Set variable

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

Condition, status = 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s