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

[Matren]

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.

[David-]

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.

[Matren]

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.

[David-]

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.

[Matren]

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.

[Matren]

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

[Matren]

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,

[RezaDorrani]

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

[David-]

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)

[Matren]

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']) 

[Matren]

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)?

[Matren]

I've updated the since expression to:

formatnumber(int(last(outputs('Send_an_HTTP_request_to_SharePoint')?['body/value'])?['VersionLabel']),'0.0') 

which adds the decimal (i.e. 4565.0); however, still says the "Version Label does not correspond to any actual version."

It is interesting to note that the Power Automate flow works for records that have <= 500 versions.

[David-]

I assume you tried using the created date in the Since field? Is there a reason that method does not work for you and you need to do it by version number? 

formatDateTime(outputs('Get_item')?['body/Created'],'yyyy-MM-ddTHH:mm:ssZ')

This method has worked for me. I should have thought of this before, but since you were focused on getting the version number I lost sight of your end goal.

[Matren · Accepted solution]

Hi David,

Using your recommendation, and tranforming it for this particular flow, yields:

formatDateTime(last(outputs('Send_an_HTTP_request_to_SharePoint')?['body/value'])?['Created'],'yyyy-MM-ddTHH:mm:ssZ')

 Running the flow, and looking at the output for "Get changes for an item or file (properties only)" function:

You'll note that the Since Version Number should be 4565.0 (the last version in the data); however, the function is changing it to 4566.0! We are storing 500 hundred versions so 5065-500 = 4565.

Also, Version Label 4565 is showing up in the data. This is underscored when I am not able to reference the last version label of 4565 in an expression for the "Since" field, because whenever I do, an error appears stating that Version Label 4565 can not be found (see previous screenshots posted to this thread). And yet, 4565 is outputed to the CSV table.

You'll note the 4565 and 4566 in the csv ouput have the SAME date time stamp. I've noted that some (but not all) of the date time stamps for the Versions repeat and I am unable to tell the difference in what changed for these versions.

By not referencing the earliest saved version, there is a risk that it won't correctly identify all the column changes.

[Matren · Accepted solution]

@David- @RezaDorrani 

In summary, I think there may be a data issue or perhaps a bug. I directly entered 4565.0 into the Since field and the flow would not run. I directly entered 4566.0 and the flow would run. In both cases, 4565.0 is in the data set and showed up in the JSON outputs and/or CSV.

This issue only seems to occur for records that exceed the versioning storage limits. In this case, 500 versions of a record are being stored and records with version labels 1-500 seem to work fine. However, for records with version labels greater than 500, the flow will not run if the user references the earliest version. If the user references the next version after the earliest (i.e. 4566.0 instead of 4565.0 in my case) the flow will run. 

Is there a way to report this as a potential bug to Microsoft?.

[David-]

Sorry, I cannot help then... The last suggestion I offered returns the last version available, as it is getting everything based on the date and time the item was created. There can be no versions before it was created.

[Matren]

Hi David,

As shown in posts above, using the creation date is returning the next to the earliest version. It appears that this occurs if either 1. there are records with version labels which have the same date time stamps (this happened in my dataset) or perhaps 2. there are version labels over the version storage limit (i.e. if storing 500 versions, having a record which has version greater than 500.

It would be preferable to reference the version label which appears to be unique in all cases.

As for how I am getting versions with the same datetimestamp ... perhaps they were created so close togther that it recorded the same value. I see it being recorded to seconds; however, I don't see any smaller time units being recorded.