Check for a Change in a SharePoint List Column Value

[ScottShearer]

I've seen frequent posts in the Flow Forums from users asking how they can check for a change is a specific column in a SharePoint list,  If the value has changed, they want to take some type of action.  I have usually answered these questions by saying that this is not an "out of the box" Flow feature.  There actually is a way to check for a change in a SharePoint list column value and it isn't very difficult to implement.  It does involve calling a SharePoint web service, but I'll show you exactly what you need to do.

First, let me set some parameters:

• I am assuming that you have versioning turned on and set to major versions.
• I am assuming that you are checking values in a list and not a library
  • This Flow would need to be slightly altered to use with a library

Step one is to create a Flow that uses the SharePoint "when an item is created or modified" trigger.  Since this Flow checks for changes in a column value, it doesn't make sense to run the Flow on a new entry.  The first part of my Flow checks to see if the created date and time is the same a the modified date and time.  If they are the same, then this is a new item and the Flow exits with a Control Terminate action.

 

Please note that the Terminate action status is set to Succeeded - the default is Failed.  If you don't change the status to Succeeded, your Flow will show as failed when a new item is created even though it ran as designed.

Next, I set a variable that holds the list item ID.  I'll use this variable when I call the SharePoint web service.   

 

Now for the good stuff - next I call the SharePoint Web service.  I use a "Send an HTTP Request to SharePoint" action.  The particular API that I'm calling returns all versions of a list item along with all columns and data in those columns.  Configure your "Send an HTTP Request to SharePoint" action in exactly the same way as I have in the screen shot below except:

• select your own site collection
• replace VersionTest (the name of my list) with the name of your list inside of single quotes

 Note that I am inserting the variable where we stored the item ID in the web service call.

 

At this point, when creating your own Flow, save it and run it by making a change to an item in your list.  Remember that you'll need to have at least 2 versions of an item for your Flow to make it all the way to the HTTP call and we will need information returned by the HTTP call in the next step.

After the Flow runs, take a look at the run history and do the following:

1.  Scroll down to the Send an HTTP request to SharePoint action
2. Click on Body at the bottom of the action.  Select everything in the Body area and copy it to your clipboard.

Now, I add a Parse JSON action.  This action enables us to easily use all the data returned by the HTTP action.  

 

In the Parse JSON action content section, select Body from the Send an HTTP request to SharePoint section in Dynamic properties.  Now, click on "Use sample payload to generate schema".   In the dialogue box the appears, paste the data on your clipboard (copied form the HTTP Body section) and click on Done.  This will automatically configure this action to interpret the data provided by the HTTP action.

When complete, the Parse JSON action should look like this:

  

Almost done - all we need to do now is to retrieve the value we are comparing from the previous version of the list item and compare it to the value in the current version. 

Next, I create a string variable and initialize it to the value of the column in which we are interested from the previous version of the list item.

Here's what I have in the expression:

Referencing the column name requires a little more explanation.  If there are no spaces in the column name you can probably just enter the column name.  If there are spaces in the column name or you just want to be sure you are using the right name,  go to the Parse JSON action and scroll down in the schema section until you see something that looks like your column name.  Here is a section from my schema that refers to a few columns:

I have a column called MyChoice with no spaces which I can refer to as MyChoice.  I also have a column called My Date.  I need to refer to this column as My_x005f_x0020_x005f_Date.

All I need to do now is to compare the current value in the column to value from the previous version (stored in the variable).

That's it - we have now compared the value currently in a SharePoint column to the value in the previous version of that list item

Here is screen shot of my complete Flow:

Let me add a few final points:

• When you Flow runs, you might see error messages about data types in the HTTP or Parse JSON actions. If this happens, edit the schema and remove the data type designation from the area where the column is defined
• I tested this Flow with string, number, choice, person or group, yes/no and text columns
• To see what data is returned and how it is formatted, refer to the output of the Parse JSON action in the run history
• Remember to turn on versioning in your list - it is not on by default

I found the following blog post extremely helpful when creating my solution

Please comment and let me know what you think.

Scott

[AhmedSSB]

Hi Sir,

I need to hide a column in the SharePoint list (when new item is clicked), and we need to use that column only to be updated using Microsoft flow, is there a way to do that ?

[ScottShearer]

@AhmedSSB :

Not all columns can be easily hidden. 

• Got to list settings.
• Click on advanced and set Allow Management of Content Types to yes.
• Go back to list settings and click on the content type
• Click on the column name and select Hidden

If you can't hide the column, consider replace the out of the box forms with PowerApps forms.

[AhmedSSB]

Hi All,

Thank you for helping me and it was an excellent solution

I have a new query which is:
How can I hide the Content Type from the request box See attachment.

[ScottShearer]

@AhmedSSB :

To hide the content type control, set "Allow Management of Content Types" back to no - it will no impact the chnage that you made.

Scott

[AhmedSSB]

I thank you for your cooperation sir 

The operation succeeded

[Anonymous]

Thank you @ScottShearer !!! This is exactly what I needed and it was very well written.

My only dilemma is that I have an email notification sent if a variance is found and the results are sending me all version history and not just the most current changes. But I'll take what I can get! Thanks again.

[ScottShearer]

@Anonymous :

Can you post your question along with screen shots in one of the Forums?  I'll take a look and I'm sure we can get it working the way you want.

[Anonymous]

Hi @ScottShearer,

How can i achive the same if the lists are stored in two site collection? In my case i have Site1 - ListA and Site2-ListB. So far i can add new items from Site1-ListA to Site2-ListB. But my second scenario is when and item eg. status column is updated in Site2-ListB it should then go and change the same status column of Site1-ListA to be updated at the end.  Thanks in advance.

[ScottShearer]

@Anonymous 

If I understand your question correctly, all you need to do is to run the run I outline above on ListB.  If you find that the status column has been updated, simply do an update item for the matching entry in List A and set the value of the Status column.  You might want to store the ID of the item from ListA in ListB to make it easy to do the update.

[Anonymous]

Hi @ScottShearer, thanks for your reponse, let me put the clear scenario so you can assist me better.  my item IDs in Site1-ListA are not the same as Item IDs in Site2-ListB. So what i have done is i have created UniqueID e.g. ABC147 in ListA using  concat('Title', triggerBody()?['ID']) .

I am able to send all new items from Site1-ListA Items to Site2-ListB using Flow where status column is 'Pending' including UniqueID ABC147.

Now requirement is when the Site2-ListB is modified with status 'Allocated' the flow should trigger to change the status column to be 'Allocated' of Site1-ListA.

I have been trying few Flow options but i am getting error as "item does not exist". I just need to match if UniqueID of ListB compared with ListA to change the status column. 

Thanks again and let me know if you need more details.

[JimmyWork]

I have a different issue.

I dont want the flow to run when a new item is created. But i cant use Created by is equal to Modified by because the user will edit the item later on and i do need the flow to run then.

[Anonymous]

@ScottShearer 

I followed these instructions with success in isolating and identifying the modification made (thank you!). Additionally, I added a "Send an email" step at the end to display the particular modification made. I made 11 separate flows to account for modification that could be made in 11 different columns. The problem I'm running into is when I change 1 particular item in 1 column, I receive 11 separate e-mail (all flows are running at the same time to track and notify of any change at any given moment) even when no change was made in those columns. Is there a way to ensure that only the items being changed are sent in an e-mail (and not the items not being modified)? In looking through this blog, the step that should eliminate this is when the item compares to the previous version and is the same, the process should terminate. This isn't occuring properly for me, I wonder what I'm doing wrong?

Thank you for your time with this!

UPDATE:   For one of the compare variables instead of "string" I now set it as "integer" and for me this fixed my issue for one column. But the other columns with compare variables type "String" still send an email when no change has been made.

[rpitts]

At the very beginning of this very helpful article you mention that using this for a Libary requires a slight modification but I don't see what that modification is anywhere in the article.  I would love to use this with a Library, please advise what the modification is.  Thank youl

[kr6955]

Hello Scott

Thanks for providing the information.  I will try them and I appreciate your time.

[ScottShearer]

I have a much better way to check to see if the trigger fired on a new or modified item.  Using the Created and Modified dates/times is not reliable.

See my bog post on this topic please.

[bbastro]

Hi Scott,

This is extremely useful but i do have a questions when i try to retrieve the field Employee name.

I added below as a variable and get trying to get the employee name,

body('Parse_JSON')?['d']?['results'][1]['EmployeeName']

However, the email show a string value like below.

{"__metadata":{"type":"SP.FieldUserValue"},"LookupId":24,"LookupValue":"George, Miller","Email":"GeorgeM@blabla.com"}

Below as the Parse Json Schema for the field employee name.  Actually i just want the email to display the name which is the above "George,Miller" right after the lookupvalue.

Can you please guide me through how i can just call the Name instead of the entire string? 

Thank you for your help.

                            "EmployeeName": {

                                "type": "object",

                                "properties": {

                                    "__metadata": {

                                        "type": "object",

                                        "properties": {

                                            "type": {

                                                "type": "string"

                                            }

                                        }

                                    },

                                    "LookupId": {

                                        "type": "integer"

                                    },

                                    "LookupValue": {

                                        "type": "string"

                                    },

                                    "Email": {

                                        "type": "string"

                                    }

                                }

                            },

[Anonymous]

Hi bbastro,
you can manage that string using compose action.
 
For example-

last(split(variables('Your Variable'),'"LookupValue":'))

After that you can use replace function to get clean name.

Thanks

Hardesh

[KatherineW]

Thank you, this has been incredibly helpful!  

I have the flow working now, in partnership with another flow, summarised as follows:

Flow 1: Trigger: new item added to SharePoint List "Leave Applications"

• Starts approval to nominated Manager
• Once approved, various emails are sent, an event created in a Group Calendar and the list updated for column "Status" = approved
• If rejected, various emails are sent and the list updated for column "Status" = Rejected

Flow 2: Trigger: item is created or modified in SharePoint List "Leave Applications, based on your post above:

• Initialize variable to hold item ID
• Check for new item (terminate if created = modified)
• Parse JSON
• Initialize variable 'varItemCompare' to hold value to be compared - body('Parse_JSON')?['d]?['results'][1]['ManagerFullName']
• Condition comparing 'varItemCompare' equal to Managers Name
• If yes, terminate; if no, re-send updated application for approval

My problem is - 

Flow 1 works perfectly & has done for some time.  

Flow 2 works perfectly for a change in Managers Name. 

BUT 

If you update another field in the Leave Applications List, Flow 2 shows as failed at the 'Parse JSON' step.  

Why?  And how do I fix?  Am I missing anything else obvious?

Thank you!

[Anonymous]

@ScottShearer 

Thanks for providing the solution as above. I have a similar problem that I am looking for a solution for however, I have to compare all the columns (70) of my sharepoint list. Could I achieve so incorporating the above solution, if yes, could you please suggest how that could be done. Thanks

[zwornik80]

Hi @ScottShearer I am having issue when trying to initialize variable to hold item ID. When I add "ID" from "Dynamic cotent" my flow is automatically adding "Apply to each". After adding it I am not able to save flow because of an error:

Can I directly insert "ID" in "Uri" section of "Send an HTTP request to SharePoint"?

In "Body" which I get by running "Send an HTTP request to SharePoint" I should get whole content of my list (all rows) in Json format?

Thanks

[aniebel]

Thanks for this, Scott, just what I was looking for. I'm having a problem, however. The column I am trying to focus on is a Person type that was originally titled "AssignedTo" but I later changed to "Assignee". When I try to compare varItemCompare to the current item in the condition step, I do not see an option for "Assignee Value" but rather "Assignee" and the items shown in the screen shot. I've also sent the two values to an email notification so I could see why they aren't matching which is the second screen shot.

I can supply screen shots of all steps, if needed but I can't figure out why this thinks there are two different values which causes the final action to fire even when I DON'T change the Assignee field but change another column value.

[PhilD]

This is an excellent post. I followed the instructions carefully and it worked!

One thing I needed to work around was getting a schema error in my ParseJSON action triggered by null values.

[ { "message": "Invalid type. Expected String but got Null." } ]

This link was helpful to me, the solution is rather simple but not apparent to me (so perhaps it's not to you either).

https://www.sharepointsiren.com/2018/10/flow-parse-json-null-error-fix/

Thanks for posting this solution!

[liniv]

Hi Scott,

Thank you for this article. I used it in an approval flow and it worked. I have some issues with the versions created by SharePoint though.

Basically my flow is the following: When item is modified, check if column X value is changed if yes check if current version is value 'Ready for Review'. If yes, start approval process.

For the approval an attachment in the item is required.

My issue is: When you edit the list item to add an attachment, the list create a separate version for the attachment. If I go in for example to edit one item, add attachment, set status Ready for review (which must start the approval) and then Save the item, the list will create 2 versions: the 1st will contain only the change of the Status, the 2nd contains only the attachment. Then when the flow is triggered, it compares these 2 versions, where the status is actually the same, therefore the approval process does not start.

In this case after the item was created, i modified it once: status change and attachment added. I can't figure out why a separate version is created for the attachment.

Another problem is that when you open an edit mode and click SAVE instead of Cancel without making any changes on the item properties, an empty version of the item is created.

Would you please recommend a workaround for these issues?

Thanks very much in advance!

[jbrines]

@ScottShearer 

Great article but wondering if you can help me. I am failing at the send an http request to Sharepoint.

I am trying to send an email when new comments are made on a multi-line column.

I had to change the varID to a string as it didn't like the integer setting.

See pictures below with error message at the bottom

[liniv]

Hi @jbrines ,

You have missed some steps I think. Check out the picture of the complete flow from the article. The 1st variable is indeed the item ID with integer, since you need to hold that value for the "Send a HTTP request" action. This action calls the versions of that item.  The variable that you need to compare is initialized after you do Parse JSON.

I hope this helps, i struggled a lot with it also 🙂

[jbrines]

@liniv 

I didn't use the "when an item is created or modified" trigger as I seen a new one called "When an item or file has been modified" and thought that would save me having to have the "when an item is created or modified" trigger

J

[jbrines]

@liniv I have copied what @ScottShearer done above, still failing

[liniv]

Hi @jbrines ,

what do you do to trigger the test? Create or modify? In order for this to work, the versioning must be enabled and there must be at least 2 versions of the item. Based on the error details in your screenshot "items(0)/versions" is the problem, I think that there are no versions to this item to find. Better use trigger "When file or item is modified"

[jbrines]

@liniv 

To trigger the test I add comments to a comments column I have created, there are 3 different comments there already so it is at least 2 versions

Versioning is enabled

So go back to the trigger I had "When file or item is modified"?

J

[liniv]

@jbrines 

in the Uri change the double brackets "ListName" with single 'ListName'. This also may be a problem hence the error is in the expression

[jbrines]

@liniv 

Different error this time

[jbrines]

@liniv 

Just realised that I wasn't replacing Version Test with my list name 🤔

Now I have changed that but now I am getting 

Item does not exist. It may have been deleted by another user.
clientRequestId: 5ce6d186-1de3-408f-adad-62b6305be2d0
serviceRequestId: 8d42979f-60d8-b000-95f3-76c14dc7f41c

[jbrines]

@liniv Figured it out missed out the the value in the In Initialize Variable.

All working to that stage, now time to move on.

Thanks

[sean-knights]

Hi Scott.  You've put me onto a wonderful path with the API, JSON and Flow.  I've implemented this solution very successfully, thanks a million.

Now I have a questions.

Can I make multiple HTTP calls in a single flow?  For example I've looked up all of the versions of a given order then I get the customer number and now I need to get the contact details for the customer from the related table.  Can I embed the second HTTP posted request within the same flow?  - I seem to be running into trouble when doing so.

How would you suggest I make the second call?  Should I change the reference from [d] to possibly [e]?  

I gather data about the order, then about the customer and then about the responsible employee and then assemble an email that is sent to all relevant parties on the placing of an order.  The fields collected form the foundation of the email body.

Looking forward to your response and wisdom:)

Sean Achim

[snehabirajdar17]

Hi,

I have a question that how to check if any items change in a list in last 24 hours using power automate. 

How achieve this task please assist.

[dtagliolini]

hello @ScottShearer and @PhilD

I need your help pleeease!

all good but as expected I get following error, I can't figure out how to remove the data type

Could you please help me? I believe i'm almost there!

[tmaccabe]

Comparing a blank string type column to earlier version that was previously blank fails because null != null. Recommend replacing this condition with a couple steps found in Flow when a SharePoint Column is updated - @WonderLaura. She uses Get changes for an item or a file (properties only) followed by a condition checking for Has Column Changed. You solution here is great because the http request gives you the old value, whereas Laura's approach alone does not. Combing steps from both gives both accurate results and more info.

[RickSavoy]

Hi @ScottShearer (or anyone)

The new screens look a bit different but here's what I have and my problem

And my results

URI path is not a valid Graph endpoint, path is neither absolute nor relative or resource/object is not supported for this connector. Resources: me,users Objects: messages,mailFolders,events,calendar,calendars,outlook,inferenceClassification. Uri: /_api/lists/getbytitle('BOM Status List')items(4486)/versions

Any ideas what might be happening? Should I be using the numeric list code above instead of the text name?

[laz8592]

Hi @ScottShearer 

How can I message you with regards to a similar flow I am trying to create?