“Can I use Power Automate to send an email with the previous and new value in a SharePoint column? I’m interested only in the changed columns.”
This post is an extension to the previous post ‘Identify which SharePoint item columns were updated‘. The outcome of that flow is an array with the changed columns. Only the column internal names, nothing else. While it’s nice to know that a column was updated, it’s better to know also the actual values. And by values I mean not only the new value, but also the previous value.
The result of this post is an email similar to the alert sent on item update. But this one will include only the updated columns and skip the rest.
Note: the version history on the SharePoint list must be enabled.
Get the previous item version
To get the previous field values, you must get the whole previous version of the item. That’s one of the reasons you need the version history, without it there’d be nothing to get. If there’s an existing previous version, use a HTTP request to SharePoint to get it.
Method: GET
Uri:
/_api/web/lists/getByTitle('<listName>')/items(<itemID>)/Versions(<versionID>)Note: everything inside < and > are placeholders which you must replace.
The only problematic placeholder is the <versionID>. You might be tempted to use the previous version number in the same way you did in the ‘Get changes…’ action. But that’s not the expected <versionID> parameter. That number, e.g. 15, is the VersionLabel. The actual VersionId, which is required for the HTTP request, is a much higher number.
But that’s not a problem as the right number should be already available among your dynamic contents. It’s one of the outputs from the ‘Get changes…’ action.
outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']Knowing this you can build the whole HTTP request, e.g.
/_api/web/lists/getByTitle('PlaygroundList')/items(@{triggerOutputs()?['body/ID']})/Versions(@{outputs('Get_changes_for_an_item_or_a_file_(properties_only)')?['body/SinceVersionId']})
The output of this HTTP request will be a JSON with the previous version of the item, including all previous column values.
Get values for modified columns
Once you have the previous item, you can extract the column values and store them in a variable. But you’re interested only in values of the modified columns, not all of them. That leads us back to the previous post.
The output of the previous post was an array with the modified columns – the columns you’re interested in. If you loop through that array, you can extract from the previous version only their values. As the first step, initialize an array variable, e.g. var_changesArray.
Then add the ‘Apply to each’ to loop through the modified columns, the output from ‘Select’.
In each loop it’ll take one of the modified columns and store it as the ‘Current item’ (=item() expression). Navigating through the version history JSON, you can then take only the value for this specific column. For the value in the previous version history, the output from the HTTP request:
outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?[item()]And for the value in the current item:
triggerOutputs()?['body']?[item()]Place these expressions in their own ‘Compose’ actions for later processing.
Format the values
The last step is to fill the array variable in a readable way. I say ‘readable’ because you’re still dealing with complex SharePoint columns, e.g. lookup, choice, or people picker. These column types contain a whole object, much more information than you need.
And as such they’ll need an extra expression to process them. By using the if(…) expression you should check if the column contains specific “type”, and if it does, take only the ‘Value’, not the whole object.
If the column contains string ‘FieldLookupValue’, it’s a lookup column and you want to take the value in ‘LookupValue’ property.
The same logic applies if it contains ‘FieldUserValue’, which defines a people picker column.
For the previous item version it would mean expression as the one below. Check if the column string contains one of the values, and if it does, select the ‘LookupValue’. Otherwise use the column as is.
if(contains(string(outputs('Compose')),'FieldLookupValue'),
outputs('Compose')?['LookupValue'],
if(contains(string(outputs('Compose')),'FieldUserValue'),
outputs('Compose')?['LookupValue'],
outputs('Compose')
)
)For the current item version it’s a bit different as it uses different properties for those columns. Lookup columns are identified by string ‘SPListExpandedReference’ and the value is stored in ‘Value’. People picker columns contain ‘SPListExpandedUser’ and it allows you to take any user property, e.g. ‘DisplayName’ or ‘Email’.
if(contains(string(outputs('Compose_2')),'SPListExpandedReference'),
outputs('Compose_2')?['Value'],
if(contains(string(outputs('Compose_2')),'SPListExpandedUser'),
outputs('Compose_2')?['DisplayName'],
outputs('Compose_2')
)
)Using both the expressions you can append a new entry in the variable.
Then just convert the array to a string with the join(…) expression using some separator, e.g. a new line or HTML new line (<br>), and send the overview in an email.
join(variables('var_changesArray'),'<br>')
In this post I used a simple, text only email, but since you’re working with HTML, you can format it in any way you’d like by adding html tags in the ‘Append to array variable’ action, e.g. by using <b>[Current item]</b>:
Summary
When you check SharePoint item for modified columns, you might need also the column previous value, and Power Automate can give it to you. The whole process, including filtering the modified columns, has multiple steps.
Firstly, you must extract only the modified columns. Secondly, get the previous version of the item. And thirdly, get the actual value and format it. You must take into account the complex column types, as shown on the people picker, choice and lookup column, and display them in a readable way.
There’s an extension to this post in case your list contains multiple selection columns.
Hi, Thank you so much for the post. But I see an error as following.
Unable to process template language expressions in action ‘Append_to_array_variable’ inputs at line ‘1’ and column ‘20268’: ‘The template language expression ‘if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
outputs(‘Compose_2’)?[‘Value’],
if(contains(string(outputs(‘Compose_2′)),’SPListExpandedUser’),
outputs(‘Compose_2’)?[‘DisplayName’],
outputs(‘Compose_2′)
)
)’ cannot be evaluated because property ‘DisplayName’ cannot be selected. Array elements can only be selected using an integer index.
Hello Sumanth,
take a look on the new blog post: https://tomriha.com/get-previous-values-of-sp-multiple-selection-columns-in-power-automate/
Hi ,
i am facing issue with multichoice column other wise your works fine.
so please help me how to deal with multi choice column.
the below code i am using but not working.
if(contains(string(outputs(‘Compose’)),’FieldLookupValue’),
outputs(‘Compose’)?[‘LookupValue’],
if(contains(string(outputs(‘Compose’)),’FieldUserValue’),
outputs(‘Compose’)?[‘LookupValue’],
if(contains(string(outputs(‘Compose’)),’Collection’),
outputs(‘Compose’)?[‘results’],
outputs(‘Compose’)
)
))
if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
outputs(‘Compose_2’)?[‘Value’],
if(contains(string(outputs(‘Compose_2′)),’SPListExpandedUser’),
outputs(‘Compose_2’)?[‘DisplayName’],
if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
outputs(‘Compose_2’)?[0][‘Id’],
outputs(‘Compose_2’)
)
))
Hello dinesh,
take a look on the new blog post: https://tomriha.com/get-previous-values-of-sp-multiple-selection-columns-in-power-automate/
Thanks for the post. Is there a way to show the Sharepoint DisplayName within the email instead of the StaticName?
Hello James,
it’s doable. The approach would be similar to this post: https://tomriha.com/how-to-display-date-in-your-local-language-with-power-automate/. You’d need a mapping table and instead of using the ‘Current item’ directly in the ‘Append to array variable’ you’d have to access the corresponding value from the mapping table with an expression variables(‘MappingTable’)?[item()].
Note: MappingTable is the name of the variable
Hi, I was able to create the flow successfully, however I have noticed that by default the modified column alway appears in the email. is there a way to exclude that in the final email?
Much appreciated for the help!
Status updated from Close to Closed
FulfilmentPriority updated from Critical to VeryCritical
DemandProbability updated from 80 to 800
Modified updated from 2022-02-01T11:34:43Z to 2022-02-01T11:36:04Z ( *****this one****)
Hello Partha,
you can add a ‘Condition’ before the ‘Append to array variable’. If the ‘Current item’ is equal to ‘Modified’, do nothing. Otherwise append the value.
I am having an error at the “Compose” section:
Unable to process template language expressions in action ‘Compose’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()]’ cannot be evaluated because property ‘{ “Audit_x0020_Status”: “” }’ cannot be selected. Please see https://aka.ms/logicexpressions for usage details.’.
My “Audit Status” column is a single-selection field, so according to your notes on your more recent blog this should continue to work for me.
Hello Kyle,
you probably didn’t switch the ‘Select’ action to the value only mode: https://tomriha.com/how-to-select-specific-values-from-an-array-in-power-automate/
Tom,
You would be correct. I was able to resolve that issue with your help! However, I am now running into another issue. I followed the steps throughout the rest of the article, but the data I am returning via the join(…) expression is not returning the original value. I only receive the new value in the email I setup at the end of the flow.
I thought it would be due to the type of field it is, but it is the same field I am changing and I receive the new value, so I am not sure why I wouldn’t receive the old value as well.
My flow:
Send an HTTP Request – /_api/web/lists/getByTitle(‘Administrator Audit’)/items(@{triggerOutputs()?[‘body/ID’]})/Versions(@{outputs(‘Get_changes_for_an_item_or_a_file_(properties_only)’)?[‘body/SinceVersionId’]})
Compose – outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()]
Compose 2 – triggerOutputs()?[‘body’]?[item()]
Append to array variable –
1) Current item
2) if(contains(string(outputs(‘Compose’)),’FieldLookupValue’),
outputs(‘Compose’)?[‘LookupValue’],
if(contains(string(outputs(‘Compose’)),’FieldUserValue’),
outputs(‘Compose’)?[‘LookupValue’],
outputs(‘Compose’)
)
)
3) if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’),
outputs(‘Compose_2’)?[‘Value’],
if(contains(string(outputs(‘Compose_2′)),’SPListExpandedUser’),
outputs(‘Compose_2’)?[‘DisplayName’],
outputs(‘Compose_2’)
)
)
The main issue I notice is there are no inputs or outputs shown when looking at the flow for the “Compose” action. It just says “click to download inputs/outputs” with no data showing. The “Compose 2” action shows the new value in my column.
Hello Kyle,
if the ‘Compose’ doesn’t return anything, then there might be a problem with the HTTP request. I’d check the output of the ‘Send an HTTP request to SharePoint’ action if it contains a valid JSON with all the previous values, it should contain pairs of “columnInternalName”: “previousValue”.
A late reply to Kyle and anyone else who may have hit this problem, I found it to be an issue with the SP list column names having spaces in when created.
My list was created with a lot of spaces in the column names, so SP was using the ‘field_x’ reference to them in most cases,
The ‘Get’ process would return ‘field_x’ but the ‘HTTP request’ was returning ‘field_x005f_x’ and so I never got the previous value pulled through,
I started replacing my SP columns, recreating them with no spaces in the name, these new columns all pulled through previous values straight away, I have even been able to go back and edit the visual column names to include the spaces for reading.
It seems like it is only returning the new value for some reason. The call I: am making is this:
/_api/web/lists/getByTitle(‘Administrator Audit’)/items(@{triggerOutputs()?[‘body/ID’]})/Versions(@{outputs(‘Get_changes_for_an_item_or_a_file_(properties_only)’)?[‘body/SinceVersionID’]})
I can confirm the select action returns the correct fields modified:
[
“Audit_x0020_Status”,
“Modified”
]
Hello Kyle,
the ‘Select’ action looks fine, now you should check the JSON output of the HTTP request, if it contains data for the fields “Audit_x0020_Status” and “Modified”.
And just to confirm the main prerequisite, you must have the version history enabled on the SharePoint list.
Hello Tom,
Thank you for this amazing article. It is exactly what I have been looking for. However, I seem to have the same issue as Kyle Webb, were the outputs of my compose action only says “Click to download”. The only difference is sometimes it works for my old value and new value, other times only the new value works, and other times the old value works. I can’t seem to pin point why some fields work and others do not. What would you suggest I do to troubleshoot further?
Thanks.
Hello Sam,
isn’t it empty because there’s no value in that field?
Hi all,
I’m having the same problem, where the previous values are not appearing but the new values are. I have versioning enabled and the previous values do appear in the HTTP request body ‘d’.
If anyone has had a similar problem and been able to solve it, could you please let me know, I’d really appreciate it.
Thanks,
Sean
Hi, great post! Do you have a post about using the changed values to update another list with the same columns (both lists have a mapped/matching unique ID assigned when the items are created – not the SharePoint ID). Thanks!
Hello CJ,
if your goal is to keep the two lists synchronised then you can just find the corresponding item in the second list (https://tomriha.com/how-to-lookup-data-in-another-sharepoint-list-in-power-automate/) and then update all the columns, it doesn’t really matter which columns were updated, it’ll update all of them to the current value.
Hi all,
I’m having the same problem, where the previous values are not appearing but the new values are. I have versioning enabled and the previous values do appear in the HTTP request body ‘d’.
If anyone has had a similar problem and been able to solve it, could you please let me know, I’d really appreciate it.
Thanks,
Sean
Sean and Kyle,
Did you get this resolved? I have been through all the linked info and still have the same problem. HTTP request output is there, the select is correct. The apply to each works for the compose(Triggeroutput…) but I see no input for the Compose(Body…) step. I’ve been reading up on how to consume the results of an HTTP request action and it all looks right.
If y’all solved it, please share.
Alan
outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()]
Is correct. I had the “outputs” as “body”.
Just posting my question led to the answer.
Thanks guys.
Alan
Hi – just trying to get this working before moving on to the expanded one that works with selections etc. In the Compose action inside the Apply to Each I get the following error – it seems not to be able to bring back the Year column which is the field I changed:
InvalidTemplate. Unable to process template language expressions in action ‘Compose’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()]’ cannot be evaluated because property ‘{ “Year”: “” }’ cannot be selected. Please see https://aka.ms/logicexpressions for usage details.’.
Hello Craig,
you probably didn’t switch to ‘Text input’ mode in the ‘Select’ action.
Hi Tomas.
It’s 06:26 in Denmark on a Saturday morning, typing this. I’ve been spending the last half hour meticulously following your instructions, first in your excellent article “Identify which SharePoint item columns were updated in Power Automate” and subsequently the follow up article here.
I just want to express my sincere gratitude towards you for having taken your time to solve this riddle (in such a nice way) and telling the rest of us about it.
I must have spent about 40 hours or so the past week trying to solve it and hadn’t it been for your articles, I probably would have or settled with a really ugly and inefficient “solution”.
The irony is that I actually read the first article at the beginning of this week and although I was led into this article here, for some reason I didn’t recognize it delivered what I was searching for.
But many of your comments (which are great by the way as they give a deeper understanding of the logic behind it all) I was able to understand because I have struggled with the outputs so had a knowledge of that data.
Many thanks for your efforts! I will certainly look at the rest of your articles here.
I have a challenge that remains unsolved … How to deal with multiple selection columns?
The datatype is also ‘SPListExpandedReference’ just as single selection columns.
But when appending the value to the array it runs into problems (understandably).
Part of my attempts this past week has been trying to identify a multiple choice object and traverse it for it’s values (which probably isn’t possible to do with objects).
But I’m not skilled enough to write such an expression or understand how to turn the object into another type which can then be iterated…
If you have a solution, I would love to hear it. Then it should work for all (or most) datatypes you can throw at a Sharepoint List.
Here is the ‘Append to array variable’ error:
InvalidTemplate. Unable to process template language expressions in action ‘Append_to_array_variable’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘if(contains(string(outputs(‘Compose_2′)),’SPListExpandedReference’), outputs(‘Compose_2’)?[‘Value’], if(contains(string(outputs(‘Compose_2′)),’SPListExpandedUser’), outputs(‘Compose_2’)?[‘DisplayName’], outputs(‘Compose_2′) ) )’ cannot be evaluated because property ‘Value’ cannot be selected. Array elements can only be selected using an integer index.
I’m wrong on the datatype (mistake: I was looking at triggerOutputs()?[‘body’]?[item()] | solution: the type is actually ‘Collection(Edm.String)’ when looking at outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()])
“MultipleChoicesColumn”: {
“__metadata”: {
“type”: “Collection(Edm.String)”
},
“results”: [
“Choice 1”,
“Choice 2”,
“Choice 3”
]
}
My current solution is to add a condition after the Compose action and examine if string(outputs(‘Compose’)) contains string(‘Collection(Edm.String)’)
If true:
Compose 3: outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[item()]?[‘results’]
Compose 4: (same as in this article but with another option for Collection(Edm.String):
if(contains(string(outputs(‘Compose_3′)),’SPListExpandedReference’),
outputs(‘Compose_3’)?[‘Value’],
if(contains(string(outputs(‘Compose_3′)),’SPListExpandedUser’),
outputs(‘Compose_3’)?[‘DisplayName’],
if(contains(string(outputs(‘Compose_3′)),’Collection(Edm.String)’),
outputs(variables(‘var_multipleValues’)),
outputs(‘Compose_3’)
)
)
)
If false: (follow the procedure as described in this article)
I would love to format the text into code / send screenshots but I can’t here in the commentary field.
Feel free to contact me on my email. I’m curious to see if there’s a better solution.
Kind regards,
Johan
Hello Johan,
did you check also the article on processing the multiple selection columns? https://tomriha.com/get-previous-values-of-sp-multiple-selection-columns-in-power-automate/
🙏🙌 Thank you!! 😊
Hi Tom,
I was achieve the similar result for my query,my next advancement is from the output I got I would like to exclude some columns such as modified,edited by and so.How do I achieve that?
Appreciate your response
HI. Tom. THANK you for these super helpful solutions.
I am having a bit of trouble with one thing.
I have a column that I called “HR/OPS”. As you can imagine, it was renamed behind the scenes to “HR_x002f_OpsNotes”. When I get the values from the TRIGGER results, it properly reflects this column name.
HOWEVER, when I do the call to SharePoint to get the values of the OLD version, the column is changed to “HR_x005f_x002f_x005f_OpsNotes”.
As you can imagine, that’s means I cannot reliably match up the results (without some hardcoding exceptions).
The full detail with Screenshots are here:
https://powerusers.microsoft.com/t5/Building-Flows/PowerAutomate-SharePoint-column-name-special-character-problems/m-p/1945234#M214269 .
Do you have any advice or input for me? Thanks!
Hello Mat,
I’d follow the advice by Dennis in the linked topic, a hard-coded replace(…) is probably the easiest solution. You can put it around the item() part in the first ‘Compose’ action to see if it helps.
outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’]?[‘d’]?[replace(item(),’_’,’_x005f_’)]
Thanks, Tom, for your work in designing these processes.
I’ve had similar trouble to Mat and others – no values are captured in the first ‘Compose’ action. I’ve tried several strategies to replace() the additional characters in the output of the HTTP request to SharePoint action, including the one suggested above by Tom, i.e., putting the replace() expression around the item() part in the first ‘Compose’ action, to no avail.
In my case, since I need to remove some characters added to the SharePoint internal column name, my expression for the first ‘Compose’ action looks like this:
outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘d’]?[replace(item(),’x005f_’,”)]
The flow runs successfully, but still picks up nothing in the first ‘Compose’ action.
I would appreciate any ideas for how to troubleshoot this situation. Thanks!
Correction to the expression, it is actually:
outputs(‘Send_an_HTTP_request_to_SharePoint’)?[‘body’][‘d’]?[replace(item(),’x005f_’,”)]
The results, however, are the same: no values are picked up in the first ‘Compose’ action.
Hello Nathan,
the x005F_ series of characters might have been specific to Mat’s column names, maybe your names are using different special characters. Check the outputs from the ‘Get items’ and the output of the HTTP request to check what’s the difference and what character to replace.