by There are a lot of valuable triggers for SharePoint list items, like the “When an item is created or modified” trigger. They help us understand when something changes so that we can perform actions to the data. But something else that is important is to understand “what” changed. This is because SharePoint lists can have a lot of columns and, depending on the one that changed, we need to react to it. So today, we’ll explore how to check if something changed by fetching the previous versions of the list item.
Before we start, you must check if your list has the versioning activated. As a good practice, activate it or at least read my article about what happens when you say, “My data disappeared! How to recover it?”.
As always, you have here the template to download and try for yourself now. If you want to know more keep on reading.
How to get previous versions of data in a list
There are times when it’s helpful to fetch the previous versions, so this template will help you do that.
Let’s check the details of the template, but first, here’s a global overview of the template.
Check if it was created or updated
Using the “When an item is created or modified” trigger, we have a trigger that fires when something happens on the list.
We have two columns (“Created” and “Modified”) that SharePoint updates automatically. They contain the date that the item was “Created” and when it was last “Modified.” Please note that SharePoint also considers something as “modified” when an item is created, so both dates are the same when it happens. So we can use a simple “Condition” action to check this:
You can then take adequate actions depending on what happened.
Check what was updated
SharePoint stores changes with version numbers. The numbers are defined by a major and minor version separated by a comma. So if you have “1.2”, then the major version is one, and the minor is two. Of course, we only have major versions in the case of lists, but I want you to understand why the number is built this way.
So let’s get the previous version. To do that, we need to:
- Fetch the current version number
- Fetch all previous versions
- Search for version – 1
Get the last number version
First, we need to create the last number, but since the version is returned as a string, we need to break it into two parts (major and minor version) and get the number.
Here’s the formula:
int(first(split(triggerOutputs()?['body/{VersionNumber}'],'.')))
So what’s happening here? First, we split the version string into two parts using the split function. For example, if we have “5.0”, we’ll get an array with “5” and “0”. Since we only want the first item, we’ll use the first function. After that, we convert to an integer using the int function.
Now that we have a version number let’s calculate the previous value using the “sub” function.
Here’s the formula:
sub(outputs('Get_the_current_version_number'),1)
Finally, let’s create the new version number string using the “concat” function.
Here’s the formula:
concat(outputs('Get_the_previous_version_number'),'.0')
Get the previous versions
To get the previous versions, we’re going to use the “Send an HTTP request to SharePoint” Action since we don’t have an action to get versions “out of the box.”
The URI looks a bit daunting, but this is defined by Microsoft. Power Automate does a great job hiding this from us, but in this case, we need to go a bit “deeper” and built it ourselves. So let’s read it to underhand what’s happening:
_api/web/lists/getbytitle('CreatedAndModified')/items(@{triggerOutputs()?['body/ID']})/versions/
Let’s use SharePoint’s web API (_api/web/) to get the list named “lists/getbytitle(‘CreatedAndModified’)” with the item number (items(@triggerOutputs()?[‘body/ID’])) and get it’s versions (/versions/)
It doesn’t look too bad now.
Find the previous version.
We get an array with all versions in JSON format. Since Power Automate doesn’t know what comes from the “Send an HTTP request to SharePoint” Action, we need to give it the structure to parse it in the following action.
Now that we have an array, we can use the “Filter” action and get the version’s information:
Check what changed
Now that we have done all our homework, it’s easy to check what changed. We have the previous data and the current version, so you only need “Condition” actions to compare the data. For example:
That’s it. All the work is done on the previous steps, so we only need to compare data and act on it.
That “Apply to each” is ugly.
I agree. Since we’re filtering an array, we always get an array in return, even if it only has one row. But since we get an array, we need the “Apply to Each” action. But we can solve it with:
- Condition to check if we have data.
- If we do, get the first element and use it to compare.
Finally, the template won’t contain this change to keep things simple, but you can change it, depending on what makes sense for you. Here’s the code for the additional section. To import it, you can check my article about “copy and paste.”
{
"id": "88d9d877-c5aa-4458-8a1d-b0ca-ba8f394f",
"brandColor": "#484F58",
"connectionReferences": {
"shared_sharepointonline": {
"connection": {
"id": "/new_sharedsharepointonline_5bf5e"
}
}
},
"connectorDisplayName": "Control",
"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iLTQgLTQgNjAgNjAiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+DQogPHBhdGggZD0ibS00LTRoNjB2NjBoLTYweiIgZmlsbD0iIzQ4NEY1OCIvPg0KIDxwYXRoIGQ9Ik00MSAxOC41di03LjVoLTMwdjcuNWg1LjY0djEzLjgzbC0zLjI4NS0zLjI4NS0xLjA2NSAxLjA2NSA0LjAzNSA0LjA1Ljg3Ljg0aC02LjE5NXY2aDEzLjV2LTZoLTYuOWwuODU1LS44NTUgNC4wMzUtNC4wNS0xLjA2NS0xLjA2NS0zLjI4NSAzLjI4NXYtMTMuODE1aDE1djEzLjgzbC0zLjI4NS0zLjI4NS0xLjA2NSAxLjA2NSA0LjAzNSA0LjA1Ljg3Ljg0aC02LjE5NXY2aDEzLjV2LTZoLTYuOWwuODU1LS44NTUgNC4wMzUtNC4wNS0xLjA2NS0xLjA2NS0zLjI4NSAzLjI4NXYtMTMuODE1em0tMjguNS02aDI3djQuNWgtMjd6IiBmaWxsPSIjZmZmIi8+DQo8L3N2Zz4NCg==",
"isTrigger": false,
"operationName": "Check_if_I_have_a_value_after_the_filter",
"operationDefinition": {
"type": "If",
"expression": {
"equals": [
"@length(body('Filter_array'))",
1
]
},
"actions": {
"The_unique_value": {
"type": "Compose",
"inputs": "@first(body('Filter_array'))",
"runAfter": {}
},
"Example_-_Get_the_title": {
"type": "Compose",
"inputs": "@outputs('The_unique_value')?['Title']",
"runAfter": {
"The_unique_value": [
"Succeeded"
]
}
}
},
"runAfter": {
"Filter_array": [
"Succeeded"
]
}
}
}
Final thoughts
The template looks a bit complex, but you can easily understand the power of getting the previous versions when you know the actions. You can do things like a warning if something changed and what, never to the earlier changes if a change is incorrect, and more.
Go ahead and download the template and let me know what you think and how you used it.
Photo by Ross Findon on Unsplash
thank you for the information provided. How do I restore the previous version of a SharePoint item using this information? I’m trying to avoid comparing all the columns on my list. Is there an easier way?
Hi David,
Great question. I haven’t done it yet, but I’ll investigate and probably write something :).
Thanks!
Hi Manuel,
I got a manual trigger and get all items from my SharePoint list via “get items”.
Then I do an “apply to each” with everything you are showing in your text. But when it comes to your “filter array” i get the following error:
Bad Request. The ‘from’ property value in the ‘query’ action inputs is of type ‘Null’. The value must be an array.
It seems that it is not an array, but an object. What am I doing wrong?
Hi Chris,
Strange error indeed. Can you please share your Flow so that I can take a look?
Cheers
Good morning.
The problem is that the ParseJson action doesn’t work like described. I executed the flow and i copied the body of http request and put in a sample space of the ParseJson action. And the text i copied was the same of your example. But when i executed the flow after complete with others parts, this action failed.
Schema
{
“type”: “object”,
“properties”: {
“d”: {
“type”: “object”,
“properties”: {
“results”: {
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“__metadata”: {
“type”: “object”,
“properties”: {
“id”: {
“type”: “string”
},
“uri”: {
“type”: “string”
},
“type”: {
“type”: “string”
}
}
},
……………………………………etc
CONTENT
{
“d”: {
“results”: [
{
“__metadata”: {
“id”: “https://00dpq.sharepoint.com/sites/contosohr/_api/SP.ListItemVersion6f6dab02-0632-4ae5-b368-434a57e0a9d9”,
“uri”: “https://00dpq.sharepoint.com/sites/contosohr/_api/SP.ListItemVersion6f6dab02-0632-4ae5-b368-434a57e0a9d9”,
“type”: “SP.ListItemVersion”
},
“CreatedBy”: {
“__deferred”: {
“uri”: “https://00dpq.sharepoint.com/sites/contosohr/_api/SP.ListItemVersion6f6dab02-0632-4ae5-b368-434a57e0a9d9/CreatedBy”
}
},
“Fields”: {
“__deferred”: {
“uri”: “https://00dpq.sharepoint.com/sites/contosohr/_api/SP.ListItemVersion6f6dab02-0632-4ae5-b368-434a57e0a9d9/Fields”
}
…………………………………..etc
ERROR
[
{
“message”: “Invalid type. Expected Integer but got Null.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[9].DiferenciaParaPromedio”,
“schemaId”: “#/properties/d/properties/results/items/properties/DiferenciaParaPromedio”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got Null.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[11].DiferenciaParaPromedio”,
“schemaId”: “#/properties/d/properties/results/items/properties/DiferenciaParaPromedio”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got String.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[13].Diferencia_x005f_2”,
“value”: “;#0”,
“schemaId”: “#/properties/d/properties/results/items/properties/Diferencia_x005f_2”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got String.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[14].Diferencia_x005f_2”,
“value”: “;#0”,
“schemaId”: “#/properties/d/properties/results/items/properties/Diferencia_x005f_2”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got String.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[15].Diferencia_x005f_2”,
“value”: “;#0”,
“schemaId”: “#/properties/d/properties/results/items/properties/Diferencia_x005f_2”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got Null.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[16].DiferenciaParaPromedio”,
“schemaId”: “#/properties/d/properties/results/items/properties/DiferenciaParaPromedio”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got String.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[16].Diferencia_x005f_2”,
“value”: “;#0”,
“schemaId”: “#/properties/d/properties/results/items/properties/Diferencia_x005f_2”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got Null.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[17].DiferenciaParaPromedio”,
“schemaId”: “#/properties/d/properties/results/items/properties/DiferenciaParaPromedio”,
“errorType”: “type”,
“childErrors”: []
},
{
“message”: “Invalid type. Expected Integer but got String.”,
“lineNumber”: 0,
“linePosition”: 0,
“path”: “d.results[17].Diferencia_x005f_2”,
“value”: “;#0”,
“schemaId”: “#/properties/d/properties/results/items/properties/Diferencia_x005f_2”,
“errorType”: “type”,
“childErrors”: []
}
]
When i enter inthe first execution and look the http request to control what i received in the first execution, the body has changed and it is like the Content in the second execution. There’s no the part
{
“type”: “object”,
“properties”: {
“d”: {
“type”: “object”,
“properties”: {
“results”: {
“type”: “array”,
“items”: {
And without this part i cannot find the “Results” of parseJson when i try to filter de array. I only have the Body of this action.
Hi,
The issue is that when Power Automate creates the schema, it will define all values as mandatory, but SharePoint’s API may not always return the same fields. It sucks, I know, but you need to edit the schema for the fields to be optional. I go on more detail here but the short answer is replace:
{ “type”: “string” }
with:
“type”: [“string”,”null”]
It’s not ideal, but Power Automate tries to make the best approximation of the schema that will be needed.
Can you please try?
I kept getting a similar error on the parse JSON action: “Invalid type. Expected Boolean but got Null”. I went round and round with it, and could not resolve it till I found the following solution from https://dev.to/thebernardlim/power-automate-get-previous-value-of-updated-sharepoint-list-item-1kcb
In the “Send an HTTP request to SharePoint” action, add the following header:
Accept application/json; odata=nometadata
Once I did that, the error went away. Basically, it keeps the results from being returned as XML by default. XML types must meet the data type value requirement, and if it does not, you will get a error every time and modifying the schema will not resolve it because SP allows for null values even for Boolean fields (how a yes/no field could be null makes no sense …)
Hope that helps save others 5 hours of brick wall time!
Hello,
I am still learning SharePoint, etc,; however after following your Blog info, I am receiving the following error when I run the actions prior to oarse JSON. Any suggestions where I have gone wrong?
List ‘CreatedAndModified’ does not exist at site with URL
‘https://xxxxxx#.sharepoint.com/sites/XXXxxxxXxxxxxxxXoooo’.
clientRequestId: 3b22a1d0-11e4-4313-819f-65153807cef4
serviceRequestId: 6aeb5ba0-60db-d000-5140-503f05f5fce0
Thanks
having the same issue 🙁
_api/web/lists/getbytitle(‘Type here your own list name’)/items(@{triggerOutputs()?[‘body/ID’]})/versions/
The ‘CreatedAndModified’ is the name of your list
I had a read in google and thats how I was able to figure it out
Hi, This looks like something I’m trying to create. Whilst I will probably build it from scratch I wanted to try your download.
on import, I get just an update on the import setup. Do you have a download that is for the new environment “Create as new”.
Hi,
On import, you can change it to “Create as new”. It should work. Aren’t you able to change it?
No it just shows update. :-/
In your filter array, where did the dynamic content “results” and “Version Label” come from? I was following up until then and don’t know what to put there.
I have the same question
Also struggling to follow this part
If you PARSE the JSON you would see the version label and results
That was super helpful and worked perfectly thank you. Definitely need to deep dive into each of those formulas and customise it for need of course, but got there in the end. I needed a one-time flow to migrate stuff out of “appended text” fields (that use versioning) and get that data somewhere else. THANK YOU!
In the filter array part
You have version label on the left and output on the right
What is the “output” from? Which output did you select?
Thanks
I, too have the same problem. ‘Results’ is not available. ‘Value’ is from the Parse JSON step.