Validate changed values in SharePoint columns with the Parse JSON action

Before I get into this topic, I have to admit that it has been far too long since my last post. However, I cannot promise this will change anytime soon, due to a very large backlog at my clients. Anyhow, let’s get into it!

Everyone who has been working long enough with Power Automate in SharePoint knows how complex Flows can become once you have to go through multiple conditional steps. In many occasions we tend to use (or nest) one or more Conditional steps in our Flows.

Condition Control: Multiple Keys, Multiple Conditional Values

In some scenario’s using the Switch control would be a better fit. This would be the case if the same Key needs to be compared to different Case values.

Condition Control: Single Key, Multiple Case Values

Thus, sometimes you don’t have any other choice but to use the Condition control. In many of these cases one of the condition branches (“If yes”, “If no”) is left empty, therefore unwillingly wasting an opportunity to build a lean Flow. Hold on to that thought as we switch gears for a minute!

Recently Microsoft released a new SharePoint action “Get changes for an item or file (properties only)

This action enables you to determine which columns have changed since a given timestamp or version. In the past we could only retrieve this information by making use of the SharePoint REST API. So, I think I can speak on behalf of many when I say that this is a very welcome addition!

And this is where things get interesting….

Let’s say for example that we have the following SharePoint List:

Now I build a Flow which triggers every time an item in this list is created or modified:

The Flow was triggered because I changed the Mango quantity from 265 to 280. This means that naturally all steps defined in the flow will run. But this also means that all these steps will run if for example the Supplier’s name would change! And in some cases we don’t want that to happen. And that’s where the new SharePoint action comes in.

To make use of this action the first thing we need to do is enabling versioning in the List or Library settings and set the number of tracked versions to at least 2:

Version History enabled with at least 2 tracked versions

Next we will add the new SharePoint action right beneath the trigger action using the following configuration:

Site AddressThe address of your SharePoint Site
List or Library NameThe name of your List or Library
SinceThe Start Timestamp, Version or Trigger Start Token
Until (optional)The End Timestamp, Version or Trigger End Token
Advanced (optional)This option is useful in cases where you want to perform set of operations on specific data based on the selected view

For the “Since” field I use an expression which calculates the previous version of the list item:

If Versioning is not enabled, the Flow and more specifically the “Get Changes” step will generate the following error:

If no errors are generated, the output of this action results in an Boolean value for each column, meaning TRUE or FALSE. As you can see in the example below, the Flow steps run if any of the column values change. In this case both the Quantity column and the Supplier column:

But now that we have an boolean output of the changed columns, we can conditionally run the Flow steps. I want the Flow steps to be run only if the Quantity changes. Normally we would use the following setup:

Column Validation with Condition Control

But what If I told you that you can actually use the Parse JSON action to simplify your flow….?

Instead of using the Condition control I use this Setup:

Column Validation with JSON Parse Action

Let’s explain what is happening in the Parse JSON action:

  1. The first step is formatting the “Has Column Changed: Quantity” dynamic content to an Object to be used as input/content for the JSON Parse Action. We do that by encapsulating the dynamic Has Column Changed: Quantity value with {“Value”: …. }
  2. Then we generate the schema by simply using {“Value”: true} as an example. You could also use false. In this case it doesn’t matter. As long as you use a boolean value!
  3. The next step is modifying the schema by adding the accepted values for this column. In this case the Flow steps should only be run if the output is true. Thus, “enum”: [true]. If FALSE would be accepted you would use “enum”: [false]. If both were accepted you would use “enum”: [false,true].

When we test the Flow you will see that the validation fails if the value in the Quantity Column has not changed, and succeeds if the value has changed:

If validation succeeds

If validation fails

If the validation succeeds the log will confirm that:

On the other hand, if the validation fails the Flow in its entirety will fail…:

So how do you distinguish between a Flow that truly fails and a Flow that only fails the validation? Well, the answer is simple: by using Power Automate’s Terminate control. We add a parallel step, “Validation Failed“, which is configured to only run if the Parse JSON validation fails:

We have used the Succeeded status instead of the Failed status to limit the number of failed logs due to Validation errors.

Now both Succeeded and Failed validations show up as “Succeeded” in the Flow Logs:

In a Real-World scenario you would want to add a notification/mail/sms/… step after the Validation Failed step, notifying the stakeholders if this was a critical business process.

If you’d like to know more about how you can use the Parse JSON action to validate data, I refer you to Pieter Veenstra’s post. He’s does a remarkable job explaining this concept.

And with this post I think I’ve made up for 1 year of non-blogging… 😉