cancel
Showing results for 
Search instead for 
Did you mean: 

Check for a Change in a SharePoint List Column Value

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.

 

VersionNewChange.jpg 

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.   

 

VersionVariable1.jpg 

 

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.

VersionHTTP.jpg 

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.

 

VersionHTTPCopy.jpg

 

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

 

ParseJSON1.jpg 

 

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:

 

ParseJSON3.jpg  

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.

 

VersionCompare1.jpg

 

Here's what I have in the expression:

GetData.jpg

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:

 

VersionColumns.jpg

 

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

 

VersionCompare2.jpg

 

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:
Complete.jpg
 
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

Comments

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 ?

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

 

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.

 

 

Hidden content type.PNG

 

 

@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

 

 

I thank you for your cooperation sir Smiley Tongue


The operation succeeded

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.

@crystalhowat :

 

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.

 

 

Hi 

 

@Viral77 

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.

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.

 

 

 

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.

Meet Our Blog Authors
  • Working daily with Microsoft Cloud to deliver the needs of my company, my customers and various Microsoft communities and forums. | Office 365 | Flow | PowerShell | PowerApps | SharePoint |
  • Co-founder of https://plumsail.com, Office 365 and SharePoint expert. Passionate about design and development of easy to use, convenient and flexible products.
  • Microsoft Business Apps MVP. Owner of ThriveFast, an Office 365 consulting company.
  • 7x Microsoft Business Solutions MVP (CRM)
  • Solution Architect with Slalom, and organizer of the Boston Office 365 User Group, and long term SharePoint/Office 365 veteren. Find more at http://www.davidlozzi.com. Follow @DavidLozzi
  • I'm keen in MS technologies, SharePoint, Office 365 and development for them
  • Daniel is a Business Productivity Consultant & Microsoft Business Solutions MVP who is very enthusiastic about all things Office 365, Microsoft Flow, PowerApps, Azure & SharePoint (Online). Since the preview, Daniel has been working with Microsoft Flow and later on with Microsoft PowerApps. That led to him being awarded an MVP Award for Business Solutions. He loves to blog, present and evangelize about improving productivity in the modern workspace with these amazing tools!
  • Michelle is an Office 365 solution architect in Twin Cities, MN. She has been delivering business collaboration solutions for years with her focus on SharePoint and Office 365. Michelle is a recent board member of the Minnesota Office 365 User Group and has been a member of the SharePoint community since 2009. She is a frequent speaker at MNSPUG and SharePoint Saturday and co-chaired the Legal SharePoint User Group for 4 years. Her most frequent projects have involved rolling out a large deployment of Office 365, SharePoint Online intranet, build of a "CHAMPS" Office 365 user adoption program and most recently, SharePoint On-Premise to Online Migration. Michelle is very excited about cloud technology as it is shifting her IT Pro focus to collaboration strategy and technical adoption.
  • I'm a Microsoft Office Servers and Services MVP with a special interest in SharePoint, Office 365, Microsoft Flow, Microsoft Teams and PowerApps. I work at Triad Group Plc ( https://triad.co.uk)