How to trigger Power Automate flow on specific SharePoint column update
Posted On March 14, 2021
“The Power Automate flow shouldn’t trigger on each item update, but only if a specific SharePoint column is modified.”
At this moment, Power Automate doesn’t have a direct solution to trigger only on update of a specific SharePoint column. There’s the generic trigger ‘…created or modified’ that will trigger the flow on each update, but it reflects only the current item data. It doesn’t include any information about previous versions of the item / document or which column was changed. It’ll take the item / document as it is and offer you the current values to work with.
If your flow should trigger only if a specific column value was changed, you must use one of the workarounds. You can use HTTP request to get the previous version, or the dedicated action with similar functionality. An example with the action ‘Get changes for an item or a file’ is in the post on avoiding loop in approval processes. But using these workarounds means that the flow must trigger on every update. The only way to avoid triggering the flow is to use a trigger condition.
Trigger condition on SharePoint column update
But as already mentioned, trigger condition can use only the current item / document values. Therefore, you’ll need one extra column to store the previous value, e.g. value_bkp. If it’s just a single column it’s very simple: you compare the current column with the backup column value.
If the columns are not equal, then somebody updated the column value. Trigger the flow and at the end store the new value in the backup column.
Update in multiple SharePoint columns?
It gets a bit more complicated if you want to check multiple columns. The idea you might have is to create a backup column for everything. If it’s for example 5 columns to check, you might think about creating 5 columns and adding a trigger condition for each.
While it’s a valid approach, it’s not very efficient. You would have a lot of extra columns to keep synchronized, and the trigger condition would be quite complex. It would also take some work to add a new column to the list and trigger.
…use only a single backup column
An easier solution is to keep all the information in a single column (I’d recommend Multiple lines of text due to the text length). Just one column used to aggregate data from all the columns. The idea is that you don’t really need to compare all the columns one by one.
For the trigger it’s not important which column was changed, but that at least one of them was changed. You can take values from all the relevant columns and put them in a long string with the concat(…) expression. If you store that string into some backup column, you can compare its value to a string created with concat(…) in the trigger condition. If the trigger condition string is different from the backup column, somebody changed one of the columns.
Let’s take an example, a flow that will trigger only if ‘ApprovalStatus’, ‘ApproverSingle’, ‘Date’ or ‘Title’ column was changed. The first step is to create the backup column and update it with a string built from the column values. Using the expression below, Power Automate will create a string with the values separated by semicolon.
Note: make sure to always store the latest column values. If only users update the column, it should come from the trigger. But if there’s any value updated by the flow, you must use that value. You must also update the backup column with each update in the flow to avoid infinite trigger loop.
The string in the backup column might look as in the example below: ApprovalStatus;ApproverSingle;Date;Title. In this example the ‘ApproverSingle’ is empty.
Trigger condition with single backup column
Once you have the value in the backup column, you can build the trigger condition. The flow should trigger only if the backup column is not equal to the actual item data (somebody changed a column value). Since you built the backup column with the concat(…) expression, you must use the same expression also in the trigger. You can use the ‘Filter array’ action to help you with the trigger condition: backup column is not equal to concat(…).
Note: all values in the concat(…) expression must come from the trigger.
Such trigger condition will trigger the flow only if one of the selected columns was updated.
When building a solution with SharePoint and Power Automate, you shouldn’t consider them as 2 separate solutions. It’s a single solution and as such SharePoint and Power Automate can complement each other.
Power Automate provides some options to check the previous column values by itself, but none of them within the trigger. You would need to trigger the flow on each item / document update, check the previous version… and in most cases just terminate the flow.
Or you can store the previous column values in the SharePoint item / document. If you use the concat(…) expression to build a string from all the relevant columns, you’ll need just a single extra SharePoint column. And if you use the same concat(…) expression in the trigger condition, you can recognize if one of the columns was changed and trigger the flow. You can then check later in the flow which column it was.
The solution doesn’t add a ton of columns to your flow, it’s not even hard to maintain. All the relevant columns are defined in the 2 concat(…) expressions: in the trigger condition and in the backup column update. I’d say it’s also a nice solution to avoid the flow infinite trigger loop.