Flow when a SharePoint Column is updated
In Microsoft Power Automate, you can create a flow that can take action based on specific columns being updated in SharePoint! This can be done on a list or library. There is an action called Get changes for an item or a file, which gives you a boolean for each field, whether it was just changed or not.
Here’s how to use: Get changes for an item or a file
In my example, I have a list of projects, and I only want the project completion email to be sent when someone changes the project status column to completed.
First of all, these steps assume that you have prior SharePoint and Power Automate knowledge. Here are my classes if you’d like to ramp up.
1. Make sure that versioning is turned on in your list or library. This can be found in the versioning settings in the list/library settings.
2. Create a new flow. Use the trigger called When an item or a file is modified. Pick your site and list.
2. Add the action called Get changes for an item or a file (properties only).
3. Pick your site and list. For the ID field, pick the ID from the trigger.
4. There is a box called “Since”. This lets you tell the workflow which changes to compare. If you want to know which fields have been changed since the item was first created, you’d just type 1.0 in this box. But in this example, I want to know what just changed in this item, so I’m going to subtract 1 from whatever the current version is. Use the following expression using the expression builder. The int fuction ensures that you’re looking at the version number as an integer, and sub does the subtraction.
sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1)
5. Since I specifically want to know if the status has just been changed in this latest modification, I can use a condition to check that. My column is called project status, so I select Has Column Changed: Project Status and is equal to, true.
6. Now what would you like to happen if the status has just been changed? That part is up to you. Maybe you need to send an email or create a task, whatever your business process requires. Put these next actions in the If yes section after the condition.
BONUS
If you would like to narrow down when your flow gets triggered, and set it up to not even trigger at all if it doesn’t have a status of completed, that can be done as well, in addition to the steps you added above. Go to the trigger’s settings.
Add a trigger condition, here at the bottom. You can see here that I’ve written an expression that is a filter. Therefore, the flow won’t trigger at all unless the project status is completed. Then, once it triggers, the condition in the workflow looks to see if the status was just changed in the modification.
To learn more about flows with Power Automate, and details about triggers, check out my course on my training site at IW Mentor.
Funny you should post this now. I just watched a video on this same subject in the corporate site of the company I do work for! While the video was helpful, I appreciate your post as something I can refer back to (and probably will) when this subject comes up again. Thanks Laura!
Ran across your video when you created a flow to pull the Employee Directory into a SharePoint list – It grabbed all direct reports – it’s exactly what I am trying to accomplish. The only issue I am having is how to get the employee photo and I can’t seem to find anyone who can explain how to do this action. I thought you said in the video that it was doable – can you point me in the right direction? Thanks much! Robin
Thank you! Works great 🙂
Hi, Not sure what i’m doing wrong but when i enter “sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1)” as an expression in the “Since field” i get an error “The expression is invalid”. I pasted it direct in. Thanks
MickM – I also had an issue with that, however I came across another site (that honestly looks like they copied Laura’s post) and their formula appeared the same, but after pasting the two side-by-side, the apostrophe in Laura’s seems to be the issue:
http://www.benprins.net/2020/09/01/power-automate-start-a-flow-from-a-column-change/
Keep up the amazing work Laura! You’re the best!
I got an error with that formula as well.
Try re-typing the quotes when you paste it.
Same problem for me. I think it’s because my Version Numbers are outputting with a decimal, e.g. 150.0, but I don’t know how to deal with this. I tried to look in the settings to turn of minor versions, but I don’t see a setting for this.
Try re-typing the quotes when you paste it.
Try sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1))
with two “)” at the end of the function. The function comes back valid, but the flow is unable to run for me unfortunately.
Try re-typing the quotes when you paste it. They’re supposed to be single quotes, but come over as apostrophes.
Hi, Not sure what i’m doing wrong but when i enter “sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1)” as an expression in the “Since field” i get an error “The expression is invalid”. I pasted it direct in. Thanks
Sometimes when you paste, it messes up the quotes. Try manually typing it, or at least just manually re-type the quotes.
Thanks all for the help
Excellent tutorial, thank you Laura.
Awesomeeeee! you are the BEST ♥
Hello, your post is very helpful. Is working great on updates. However, when I create a new record, I’m getting an error: Specified Version Label does not correspond to any actual version. Any ideas?
Only run the flow on modified items, not new ones. My post says to trigger on modified. If you were going to run one when it’s new, you’d have to create a different flow that compares it to version 0 instead of trying to get a previous version.
Pingback: SharePoint PnP Dev General SIG Call – Oct 15th, 2020 – Screenshot Summary - Warner Digital
Hi Laura,
I get this error: “the template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type”. Could you please advise?
Regards,
Daniel
I’m not sure why exactly it would be giving you that error.
I’m getting the exact same thing
Is your flow only triggering when an item is modified, and not when a new item is created?
Any particular parameter that needs to be within the list or can’t be so it won’t block the flow? I suppose that target type is the VersionNumber in this case, but I don’t understand why it says that the parameter is not valid. Everything is per your guidance. 🙁
Laura, now I get this error: Specified Version Label does not correspond to any actual version. Any thoughts?
Sorted! The first problem was connected with “apply to each”- I deleted the flow and created a new one.
Second issue was resolved by including minor version in ‘Get changes from…’ step. 🙂
Thank you for this article. Very helpful. Much appreciated.
That will happen if either versioning isn’t turned on, or it’s the very first version (on create).
Hello Laura,
With your above solution, everything works fine. But when the column is modified with an attachment added in the same modification, sharepoint list is making 2 versions, one with column modification and one with the attachment (very annoying), this has as a result that the above modification does not work because of the extra attachment versioning. Do you have any idea about that?
Thanx!!!
That’s annoying but it doesn’t surprise me, and I don’t know of a way around it.
On custom list item, on list item changed, how to know if attachment/s have been changed (added, deleted). In custom list attachments doesn’t have versions :/
There isn’t a way to do that. If you need versions on individual files, I recommend using a library.
However I have another problem with conditioning workflow to trigger only for desired status. Should I put different name for my list item? You put Project_x0020_.
Thank you so much for the great post, is there a way to trigger a flow when a file is created or modified, but based on version?I don`t want the flow to run on minor versions, Also I have this issue that when flow runs for approval – on publishing a major version, each time that flow runs on the file it adds a minor version to the file 🙁
Hi Laura, I tried your flow but unfortunately, when inserting the function “sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1)” I have an error message “The expression is invalid”.
Any advice on this?
Thanks
Found what the issue is… the brackets are not same (probably linked to geographical parameters). Yours are ‘ and I should use ‘.
Différence is small but seems Sharepoint makes the point.
Don’t copy and paste, the quotes end up weird. Type them from scratch.
Hello Laura, thanks so much for this post. It got me one step closer to what I need. There is one final step that I would like to achieve and I have wrecked my brains on how to go about it. There is an additional possibility in my scenario where the column being referenced to check that the item has been modified (which will then trigger an email), is modified again. When the item is first created this column is empty.
My problem is that I only want to send this email once i.e. as long as the column in the previous version is empty. After that if this column is modified again then the mail should not be sent.
For added info the column I’m referring to contains a time value. Is this possible?
Thank you in advance.
Daniel
THANKS !!!!!!!!!
Your article helped me break through the wall on triggering flows based on changing a status. HUGE THANK YOU!
Hi Laura, Regarding this comment that you made on this post: “Only run the flow on modified items, not new ones. My post says to trigger on modified. If you were going to run one when it’s new, you’d have to create a different flow that compares it to version 0 instead of trying to get a previous version.”
I need to run it on any modifications higher than v0.1, so v0.2 or higher any time there is a change I need to check for certain data. So if someone creates a document in list library as v0.1, once they got to v0.2 I need to start checking for certain changes that I need.
I’m understanding this blog article to mean that subtracting v1.0 from your results is only going to return data if your document is higher than v1.0. Am I understanding this correctly? If so, I do not need to compare to version 0, but how would I write my expression statement to look for the last change that was made since the last version for any version v0.2 or higher? I have like 8 flows all working perfectly for a BI Department at a bank, but I have zero coding experience and I’m the only one at my company who knows Power Automate. So if I can’t find an exact example for what I need to do I have a really hard time writing the expressions. TIA
I get the following when testing the formula:
Unable to process template language expressions in action ‘Get_changes_for_an_item_or_a_file_(properties_only)’ inputs at line ‘1’ and column ‘2861’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
I’ve retyped the quotations and also typed in the full formula. I still get this error. Any ideas on how to resolve?
Hi Heath, not sure if you solved your problem yet, but OrcaDeWils had the same error and mentioned it on this page: https://wonderlaura.com/2020/08/25/flow-when-a-sharepoint-column-is-updated/#comment-110735 From what I understand from his comment a couple posts down is that that error had to do with the “Apply for Each”. Not sure if this helps you, just thought I’d point it out, I haven’t experienced that error myself.
Wow, this has really helped me out. I got errors on the expression at first, even after typing it out from scratch. Turns out you have to type it out from scratch in the EXPRESSION BUILDER, not directly into the ‘since’ field. That fixed it for me.
Thank you so much, Laura!
Thank you Laura for this wonderful guide. I set the condition if “status” column has changed to “approved”, send an email. I added all details you specified, my flow runs successfully, however, I am not getting any email as I sent the condition. When I checked the result, it says condition “false”.
Please advise.
It’s case sensitive, so that may be the problem. Take a look at the raw data in the flow’s log, to see what the actual value of your field is.
Thank you Laura for this article, very helpful. However, my run does not run to send the email. no action. any idea what I have been missing.
Hey Laura
Thanks for the great article. Do you have any guidance on how to handling comparing of 2 fields, for example in a condition, I would have field A not equal field b and then build out in the yes/ no branches.
Not sure if MS haved changed something but i got this concept working by using the Trigger window Start Token and the Trigger Window End Token in the Since an Until Fields. The Connector also need to be an a Apply to Each Loop now.
Thanks
Hi, usually an apply to each loop only appears when you’re trying to set a value or do something to a column that allows multiple selections, like a people picker that allows multiple people, or a choice field that allows multiple choices.
This content is very helpful for newbies like me. It worked so well on my workflows. Thanks and stay safe!
Pingback: "When an item or a file is modified" trigger is not working | Anj Cerbolles
Thanks for this Laura. I have an issue that maybe you can point me in the right direction with – the test for HasColumnChanged:Status equal true always fails (is false) when the status is changed so my flow terminates immediately. And I cannot figure out why. Any suggestions what to look for? Thanks.
Hi, the best way to troubleshoot that, is to look at the raw data and see what the value of that column is. So after the flow runs, look at the log and click that action to look at the raw data. See if the true/false value is slightly different, like in all caps, or 0 and 1, or YES NO.
This is great content, Laura. Really appreciate it. Thanks!
But how to build the switch with the case where I need to trigger for value “A” or “B” and another case for Value “C” or “D”.
Can you come up with a suggestion?
Hi Laura, get post and it really helped me. After getting the main flow to trigger correctly I wanted to narrow the trigger event as you suggested in the bonus section. Unfortunately it does not trigger with this expression @equals(triggerOutputs()?[‘body/In_x0020_EDMS/value’], ‘true’) I typed it in from scratch and I checked the raw data to make sure I had the field name correct. It is a Yes/No field but the raw data resolves to true/false. I also have a similar expression as part of my condition (checks for new version and that this field is equal to ‘true’ to send an email and that works when the flow triggers. I was just trying to avoid the flow trigger every time a field is updated and narrow the trigger event. But that expression is not working. Overall, thanks for a great discussion.
Hi Dave, if it’s a boolean, maybe it doesn’t need to be in quotes? So, true instead of ‘true’.
Hi Laura, yes, that was the problem. I sorted it out shortly after sending my post and this is the working expression: @equals(triggerOutputs()?[‘body/In_x0020_EDMS’],true). I also had to remove the /value element. I found this post useful to help troubleshoot the expression ( https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Power-Automate-Trigger-Conditions-made-EASY/ba-p/441348 ). As you know, the expression in the settings does not provide any feedback like a failed flow might. As this powerusers post suggests, add a variable into the flow with the same expression and work out the trigger condition prior to using it in the settings Trigger Conditions expression. Thanks again for the great post and helping us out.
Now that we know a certain field has changed any way to pull the previous value into an email?
No it doesn’t provide the previous value. Regular built-in SharePoint alerts will provide that information automatically. When you receive the alert that an item changed, it displays what the value was and what it was changed to.
Thanks for this post!
One remark to avoid an error when working with new items: Just use max(1,sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1))
Another formula for a Trigger condition so the Flow only runs on modified items is;
(Trigger action, three dots > Settings):
@not(equals(float(triggerBody()?[‘{VersionNumber}’]),1.0))
— so if the item isn’t at version 1.0, it’ll run for anything modified. Brackets around VersionNumber are curly { }
— as mentioned above, check the single quote marks are straight ( ‘ ‘ ‘ ‘ ) as slanted ones are a different character & will cause the flow to error. Manually retype them in the trigger condition field to be sure! 😀
Ah, in my example above ( my straight quotes display as slanted !! ) – proof in point 😀
That is the condition I was looking for, thank you very much Laura. It worked !
Now, I still have a question. I have a People field with multiple entries, and I wanna get the last entry to use it as a recipient of the email I wanna send.
How to get the value from a People field to use in this condition that you taught ?
I don’t think there is a way to do that, unless you created an extra column, and stored a copy of the person in there. Otherwise you won’t have that data anymore if it’s been changed.
Well, I’m getting the same output error of ‘Invalid format for version input value…’; which I see many on here have also experienced. I’ve gone ahead and typed it from scratch, deleted it all and recreated the flow, tried changing from version 1 to version 2, tried removing the quotes, tried changing falst to true…nothing seems to work, any suggestions?
Is your flow only being triggered when it’s an existing item being modified, not a new item being created?
Okay, I had the wrong step on here so it was looking at newly created or modified existing. Now, the only issue I’m having is that it keeps sending out emails even if the condition isn’t met. So, i have it to send a notification when the request status has been changed = YES and the change is equal to ‘X’. Well, that trigger works but… now, if I change any other column (not these) although the status did not change it still executes the email. It seems to always reflect that the status column is updated TRUE but it is not TRUE. Any suggestions?
That’s an interesting one. When you look at the version history in SharePoint, is that one listed as having changed? If your flow is changing the status, then that would re-trigger the flow.
This isn’t working for me. I finally got passed the error with the Invalid Value by typing the statement directly into the FX input and not directly in the ‘Since’ field. Now, I’m getting “Unable to process template language expressions in action ‘Get_changes_for_an_item_or_a_file_(properties_only)_2’ inputs at line ‘1’ and column ‘18309’: ‘The template language function ‘int’ expects one parameter: the value that is to be converted to an integer. The function was invoked with ‘2’ parameters. Please see https://aka.ms/logicexpressions#int for usage details.’ ” What do I do to fix this?
Be sure that your flow isn’t being triggered when an item is created, it’s only when an existing item is modified. New items won’t have a version number yet.
Hi, Laura. It’s awesome.
But, What if I want to recover the information that has been changed? For instance, I have a field (Office365User) and if it is modified I want to send the previous user a notificaton. How could I do that?
That data isn’t available.
How can I reference what the status was before on the previous version?
In the version history of the item. It’s not possible to reference that in a flow.
hi, Thanks Laura it’s excellent and very helpful article. Hovewer I have problem and I really don’t know what’s wrong. I receive “false” when column X is change. I reduce my flow only to three first step (1. when an items.. is modyfied; 2 get changes for an item..; 3 condition. In the step 2 “get changes for an item or a file (properties only)” i receive “false” to every column – I of couser made changes in column on sharepoint. Do you have idea what i were doing wrong??
This is exactly what I needed!! Thank you so much for this tutorial – everything is working perfectly for me!!
Hi Laura,
I’m really hoping this works out for me. I followed your instructions exactly, but I’m getting the same “Specified Version Label does not correspond to any actual version” error some of the others are getting. I typed in the expression vs. copying and pasting and everything else looks to be in order (versioning is on, etc.). Any other advice on troubleshooting?
This seems to happen if your flow is triggering “on create”. You only need to do this when an item is modified, not when it’s created, since when it’s created there is no previous version.
Hi Laura,
Big fan of your work so thanks for all your hard work…
I have a question about the trigger condition. If I want the flow to trigger on one of two options (for example, Yes*, No*, blank) how do i incorporate the two options into the trigger?
HI Zak, you can add more than one trigger condition in a flow.
Another great tip. Thanks Laura!
Hi, I don’t understand step #4 for this code: sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1)
Is the above code going to result in showing all the changes since the file was created OR what “just changed” in the item?
If it shows what “just changed” in the item, I don’t understand exactly what “just” means. Will “just changed” get 1 change, meaning the last change that happened for the last version that was checked-in in SharePoint? Or does “just” mean it will it get all the changes for the entire last version, meaning it will get all the changes that happened for the last version that was checked-in in SharePoint? I hope that makes sense. Confused people can be confusing. LOL Thank you!
The flow is being triggered when the existing item is modified. Modified = changed. So in my example, we’re looking at what changed between the previous version and the current version. In order to obtain the previous version number, we’re subtracting 1 from the current version number. There is no “check-in” being used on the list item in this example.
Hi Laura,
The code that you provided (sub(int(triggerOutputs()?[‘body/{VersionNumber}’]),1)), does this take into account that when something is first entered, it’s version 1 or not?
If it doesn’t, do you have any suggestions on what would need to be done?
Sometimes my flows is giving me this error
“Specified Version Label does not correspond to any actual version”
Cheers
You don’t need to check what’s changed, if it’s a brand new item, so this post was written in the assumption that you’re using the trigger I mentioned, only when items are changed. Potentially if you have your trigger also running when items are new, you could add an additional condition in your flow to check if version number equals 1.
Hi Laura
I have the same issue as some people here. Specified Version Label does not correspond to any actual version
I have had this working on other flows, but the last couple I have been setting up have failed. I have just deleting a flow that not working and setup a new modified only flow, but still get the error. I have played around with drafts/minor versions etc. to no avail
Looking at the version numbers in my list E.G 2.0, I decided to try float instead of int, but still get the same error
Any ideas would be great
Thanks
Mike
thank you so much, that was exactly what I needed/was looking for!!!
I want to add column mentioning date when a specific column is modified
Ok create a new column in SharePoint and then do an update item action in flow to set the date in it.
This was so helpful, simple, to the point. Thanks. But I have one question (newbie, here!). I would like it to send an email if the status changes (one of my columns, TaskStatus), but ALSO only if another column has certain text in it. That column is a location column (store numbers, 1284, 4000, etc.). Currently, I get an email any time the task status changes, but taht’s for all three of our stores. Can I narrow it down, and send an email when, say store 1284, is the one where the status changes?
Yes, you can do that in a condition in the flow, if X contains xyz
i’ve tried a condition in a variety of places, and it does not work. It takes out the condition I have that I got from your code. Sorry for all these newbie issues.
No problem, to learn more fundamentals, check out my online self-paced Power Automate course: https://www.iwmentor.com/pages/plans
Pingback: Power Automate Astuces | KTNN SharePoint
Hi Laura, great post. I want to use this on a date field, so if a value is added to the date column then start the trigger, how would I do that?7
Great article. Thank you for that.
Is there a way to set up a column value based on a condition using conditional formatting in SharePoint Document libraries?
My use case is that I have a doc lib with draft documents and when they are published as PDF (using the flow of course), I set one column value to “Published” and save the published date in another column. I would like the column set back to “Unpublished” when the published date < modified date.
It looks to me that conditional formating cannot set the value of the field, and I have to use a flow for that.
Just wanted to make sure that there is no other, more simple way.
Thanks in advance for your reply.
You could create a schedule recurrence flow that runs once a day or so, and looks through that library for items with a certain date, and then takes an action on those items from the get files action, like update a date field on each file. I teach this in my advanced Power Automate course.
Thank you for this! You really are WonderLaura!
Here’s a little add to not get spammed via mail on failing flows (if you have this, creating a new item):
-Between “Get changes for an item or a file (properties only)” click on the ‘plus’ button and add an parallel branch.
-In this branch, find the action ‘Terminate’ and give it the option Cancelled.
(Failed gives mail and succeeded makes every flow succesfull and you can’t find easy which one is succesfol because of the Terminate
action or whether the flow really did run succesfully)
-Click the three dots on the “Terminate” box.
-Click ‘Configure run after’
-Deselect “Is successfull”
-Select ‘Has Failed’
-Click ‘Done’
Now, new items wil have the status ‘Cancelled’ instead of “Failed” making your inbox not flooding with email from Power Automate
Pingback: Action Button in Lists: Set Column Value | @WonderLaura
Thanks so much for this post! I’m am definitely using it. What is killing me though is that when a person uses Teams to update the list, every field change creates a new version. So if someone changes the status then the priority then the another field, that’s three versions. Since I never know how many fields they will update, I don’t know how many versions to look back for. Do you know of a way to search the versions until you hit on the changed field then execute the rest of the flow?
Each field change creating a new version shouldn’t prevent this solution from working as written.
Hi Michelle, each field change creating a new version is not a result of users updating the list through Teams. It will be the same if they update the list through SharePoint. Rather, this is a result of the “Allow items in this list to be edited using Quick Edit and the Details Pane? ” setting being enabled under advanced list settings.
Disabling this will prevent this behaviour and will only create a new version once they click ‘Save’ bundling all changes into one new version, but it reduces the ease of use and general user friendliness because users will first have to click the ‘Edit all fields’ button before being able to edit any fields. In Teams, this button doesn’t show up if you open an item, you have to select it from the list view via the 3 vertical dots on the item. That’s definitely not a nice user experience.
As for your question, I think a solution would be to have an additional (hidden) field to keep track of the last version that your flow was executed on. In the ‘Since’ property of the ‘Get changes’ action, you would just reference that hidden field, rather than using the expression explained in this post. Then you will also need to add an action to update that “last flowrun version” (ie. the hidden field I’m talking about) in your flow with the current version of the item it runs on. Hope that makes sense! 🙂
I’m in the same boat. I’m checking to see if one of two columns is modified, but the last change people make to the list is a third column which is “send update to user” which is a yes or no ratio button. That tells the flow to run or not, but it will check the last version, and i have the quick edits enabled which makes each change a new version.
The last version just shows that that third column(the yes/no) has been modified, but not either of the columns I was looking for changes for. how would one tackle this problem?
Pingback: Action Button in Lists: Set Column Value - @WonderLaura