Context of problem:
I have a column in sharepoint list (f.e: Status) and when item is modified, I want to compare updated value (f.e "Accepted") with the previous one (f.e "Pending")
It took me few days to figure out the way which doesn't require complicated going-arounds, premium actions or hard stuff like parsing json
Final structure:
(later on Send an HTTP request to SharePoint will be renamed to HTTP and Filter array to FILTER)
As you can see - It took only 7 actions to solve this problem. Let's go!
What you should have at the beginning:
- Sharepoint list with column that you want to track. In my case it is "Status"
- At least one item
Step 1 - When an item or a file is modified
Intention: Triggering when specified column is modified (for example - Status has been changed)
Key | Value |
---|---|
Site Adress | Link to your sharepoint list |
List or Library Name | Name of your sharepoint list |
Folder name | leave blank |
Step 2 - Send an HTTP request to SharePoint
IMPORTANT - Rename this action to "HTTP" (click three dots -> Rename) - Trust me, it will be useful later
Intention: Getting old states of this row
Key | Value |
---|---|
Site Adress | Link to your sharepoint list (the same as before) |
Method | GET |
Uri | /_api/web/lists/getByTitle('LIST NAME')/items(ID-OF-ITEM)/Versions?$filter=IsCurrentVersion eq false&$orderby=versionId desc&$top=1 |
Where:
- LIST-NAME - Is string with the Name of your list
- ID-OF-ITEM - In Power Automate select this fragment, select ID from "When an item or a file is modified" (first screen from this step)
(Optional - You don't have to read this if you are not interested what is beyond Uri stuff)
- _api/web/lists/getByTitle('LIST NAME')- Catches collection of your list
- /items(ID-OF-ITEM) - Catches all the old versions of updated item
- ?$filter=IsCurrentVersion eq false - Filtering: You don't want the current version (it is avaliable from the very first action)
- &$orderby=versionId desc&$top=1 - Sorting: You want the most previous element and only one
Step 3- Initialize variable
Intention: Data that you received have very strange form (nested array of objects) and you have to find the one column that you are interested in
Key | Value |
---|---|
Name | foundObj |
Type | Array |
Value | [] |
Step 4 - Filter Array
IMPORTANT - Rename this action to "FILTER" (click three dots -> Rename) - Trust me, it will be useful later
Intention: I mentioned that received data have very strange form. You have to filter it and access necessary fields.
Click the input, select "Expression" and write the following text:
Key | Value |
---|---|
Array to filter | body('HTTP')['d']['results'] |
Choose a value (left) | contains(item(),'Status') |
Choose a value (right) | true |
Final effect should look like this:
Note that this fragment works only when:
- You renamed "Send an HTTP request to SharePoint" to "HTTP" (as i asked in step 2)
- You want to refer to the "Status" column. If the one you track is different - rename it
So far, you should have 4 actions:
Step 5 - Set variable
Intention: Saving the data from filtering
After this step, you will have an array of one object. Job is almost done
Step 6 - Initialize Variable (to save oldStatus)
(when you add this action, Power Automate should automatically rename it to Initialize Variable 2)
Intention: Saving old status (the one before the update)
Key | Value |
---|---|
Name | oldStatus |
Type | String |
Value | body('FILTER')[0]['Status'] |
In order to apply this value, again, click the input and switch to "Expression"
Note that this fragment works oly when:
- You renamed "Filter array" to "FILTER" (as I asked in step 4)
- You want to refer to the "Status" column. If the one you track is different - rename it
Step 7 - Initialize variable (to save newStatus)
(when you add this action, Power Automate should automaticly rename it to Initialize Variable 3)
Intention - Saving new status (after the update)
In fact, it is only a formal step. You have to select interesting column and save it to the variable (so you eventually have two variables: oldStatus and newStatus)
All you have to do is select Status from "Send an HTTP request to SharePoint" action.
Note, that in my case Status was an option-choice, so I have to select Status Value, but it doesn't effect anything.
The final effect should look like this:
Conclusion
And that's it! You should have 2 variables: newStatus and oldStatus which contain wanted information. You can do with them whatever you want: Creating conditions, sending emails - Sky is the limit
It doesn't matter if the column will change in terms of Power Apps form or hand-change in sharepoint - This flow will work.
Please be aware of the fact, that in step 2 I fetched only last update). If you want to compare more old values - Switch &top=1 in Uri input to the number you actually want.
I also wanted to mention that my solution doesn't involve using premium actions and bases on elementary operations, so you shouldn't worry that future changes in Power Automate actions would affect integrity of this flow.
Top comments (4)
Thank you very much for posting this. I have been trying to find this exact solution so I can complete a flow for a power app I am creating for a client.
Hi, i've followed this to a T and keep getting the error
InvalidTemplate
Unable to process template language expressions in action 'Initialize_variable_1' inputs at line '0' and column '0': 'The template language expression 'body('FILTER')[0]['Engineering Status']' cannot be evaluated because array index '0' cannot be selected from empty array. Please see aka.ms/logicexpressions for usage details.'.
for the 2nd "initialize value" ..... (your 1st one had a '1' in the name, mine didnt, my 2nd one has a '1' in the name)
Hi, I will try to help you with that.
By step T I assume you mean step 6. Could you please provide a screenshot of your steps, and what is the output just of body('FILTER')[0]?
And could you also run the flow and show step by step what are the outputs of each cell? Are you sure you changed filter cell name to FILTER?
If your data is sensitive, you can blur it. I would like to see the overall JSON and keys in each step
Hi!
I’ve a problem running the flow at the step 6:
![Image description](https://media.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2Fd4cbg6qpw4ge9m1za9v0.jpeg)
“Unable to process template languaje expressions in action ‘initialize_variable_2’ inputs at line ‘0’ and column ‘0’: ‘The template languaje expression ‘body(‘FILTER’)[0][‘Status’]’ cannot be evaluated because array index ‘0’ cannot be selected from empty array.
I see the filter and at the output it shows only []