Expression help to filter SharePoint Get changes for an item or file (properties only) since VersionLabel

(0) Share

Report

Posted on by Matren

454 Super User 2024 Season 1

Hello Power Community,

I would like to filter the SharePoint Get changes for an item or file (properties only) since the earliest version of a record. Usually I use "Since 1.0". However, I am currently storing 500 versions of a record in my SharePoint list, and there are some records that have over 500 changes. For example, one record has over 5000 versions and the Version Labels range from 4500 to 5000. Whenever I use "Since 1.0" with this record, the Power Automate flow will not function since there is no Version Label 1.0 that has been saved.

Is there an expression that I could use for the Since field that would generate the earliest version that has been saved? Perhaps I could leverage the "Min" or "First/Last" function?

I tried

min(outputs('Send_an_HTTP_request_to_SharePoint')?['body/value']?['versionlabel'])

but that did not work.

Thank you for your assistance.

Version Label Expression.png

Categories:

Building flows

I have the same question (0)

All responses (16)

Answers (2)

David- 1,254 on at

Like (0)

Report

I think you would need to add a Get an item or file (properties only) before your Get changes for an item or file (properties only) action so you can get the current version number. Since you are storing only the last 500 versions, you can then use an expression to subtract 500 from the current version to use in the Since field for the Get changes action.

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (0)

Report

Hi David, Thanks for your response. I do not believe subtracting 500 will work for all our records. Many records in the list have less than 500 versions which would result in a negative number. It would be preferable if there was an expression that I could use that would always retrieve the lowest Version Label, regardless of what number it is. In addition, I could apply this to all our lists regardless of how many versions are being stored for that particular list. I would like not to have to enter a specific number to subtract, but rather a formula. I was thinking I would need to use Min, First (or Last), to obtain the lowest Version Label that is currently stored; however, I haven't been able to successfully derive the expression.

Was this reply helpful? Yes No
David- 1,254 on at

Like (0)

Report

Sure it should, just use a condition that checks to see if the current version is over 500. If it is, on one side subtract 500 from the current version (or maybe you'll need to subtract 499), and if it is not, on the other side you can get all versions from version 1. Like:
This is the expression for the since field:

sub(int(outputs('Get_item')?['body/{VersionNumber}']),500)

It's a solution unless someone has figured out a way to extract the lowest version saved.

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (1)

Report

Hi David, appreciate the response 🙂. As you mentioned, I'm looking for a solution that extracts the lowest version saved, making the solution applicable for any of our lists (which have different versioning settings) without having to subtract a different hard coded number for each list. It seems that a Minimum, First, or Last function might be employed in the solution, which references Version Label from the JSON outputed by Send_an_HTTP_request_to_SharePoint.

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (0)

Report
Hello Power Automate Community,

Still seeking assistance on this issue. I've gotten closer and am using the following expression:

last(outputs('Send_an_HTTP_request_to_SharePoint')?['body/value'])?['VersionLabel']

This yields the correct VersionLabel in compose functions; however, it throws an error in the Get Changes for SharePoint function. It is saying that "Version Label does not correspond to any actual version."

Any suggestions?

Get Changes ExpressionCompose Outputs - No ErrorGet Changes Outputs - Error

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (0)

Report

Perhaps the issue is that the output for VersionLabel is not being recognized as a number. You'll note the output is "4565.0" instead of 4565.0. Perhaps, I just need to use an int function; however, I haven't derived it yet.

"VersionId": 2337280,
"VersionLabel": "4565.0",
"ID": 1,

Was this reply helpful? Yes No
RezaDorrani 12,143 on at

Like (0)

Report

Hi @Matren

typecast it as decimal

check https://youtu.be/Ek4oYWPWfT0

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Thanks,
Reza Dorrani, MVP
YouTube
Twitter

Was this reply helpful? Yes No
David- 1,254 on at

Like (0)

Report
Since you are still having trouble with this, I have one improvement on what I proposed, which will do away with the condition. Add a Compose action between the Get item and Get changes for an item action:
The compose action will contain the following expression:

if(greater(int(outputs('Get_item')?['body/{VersionNumber}']),500),'500','1')

You place the output of this in the Since field for the Get changes...

Now to get what you want, I think you just have to replace the second 500 in my expression with an expression that will subtract 500 from the current version. So if your current version is 4500 it will return 4000.

Then whenever you want to use this, you just have to adjust it for how many versions you are keeping.

sub(int(outputs('Get_item')?['body/{VersionNumber}']),500)

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (1)

Report
Hi David, my requirement is to not use a hard coded number (i.e. 500). I think Reza is onto something by changing the output so that the since field recognizes the version label. I'm currently testing Reza's solution to typecast the following expression as a decimal.

decimal(last(outputs('Send_an_HTTP_request_to_SharePoint')?['body/value'])?['VersionLabel'])

Was this reply helpful? Yes No
Matren 454 Super User 2024 Season 1 on at

Like (0)

Report
Hi @RezaDorrani ,

Thanks for your response (and I love your youtube channel)!

Per your recommendation, I typecast the following expression as a decimal:

decimal(last(outputs('Send_an_HTTP_request_to_SharePoint')?['body/value'])?['VersionLabel'])

However, the Get Changes Since Field still sent an error by not recognizing the VersionLabel. You'll note that the correct number is displayed (4565); however, perhaps it should be (4565.0)?

Was this reply helpful? Yes No