How to retrieve data from versioned column in SharePoint list?

[janklaska]

Hello there,

I have my canvas app connected to a SharePoint list named mySPOList. This SPO list contains a versioned column named UserComment (a multiple lines column where the function Append changes to existing text is set to Yes). The LookUp function will always return the most recent text information saved in this column. I would like to know if there is a way to retrieve all previous text information saved in this column before? Or maybe only the last 3 most recent pieces of information (e.g. "test", "TEST 4", and "TEST 3") would be completely enough for me.

Many thanks for your support! 🙂

[VishalJhaveri]

No, with 2 years of experience in SharePoint and Power Apps, I don't think so it is possible to retrieve a specific versioned entry data.

[SharePointJB]

@janklaska you could possibly use the SharePoint REST API called via a flow to retrieve the version information of a list item. You would need to do a SharePoint REST call to something like the following:

{SITEURL}/_api/web/lists/GetByTitle('{LIST NAME}')/items({ITEM ID})/versions

This will return you a JSON array of the list item versions you can then parse to return the information you need.

[iAm_ManCat · Accepted solution]

Hey @janklaska,

@SharePointJB is correct, this is 100% possible and you can look this up using a flow from within your app, but it's not an easy or straightforward process.

There's a blog here written by @rikdekoning Rik de Koning that covers how to implement this for retrieving version history, you'll need to adapt that to your needs:

https://www.about365.nl/2019/08/08/view-sharepoint-item-version-history-in-powerapps/ 

Cheers,

Sancho

[janklaska]

Hello @SharePointJB and @iAm_ManCat,

many thanks for your replies. I've checked the link above, but it seems outdated. The Power Apps connector, where you can dynamically Ask in Power App for the input parameter, is not available for me anymore. I only have Power App (V2) connector which asks for the input parameter by itself. This is not a problem, because I can provide the input parameter (ItemID of SPO List), but I have been stuck with step Process each version history item from the article above...

Can anybody explain me, what does the ['d']['results'] in the body means? If I copy the HTTP request to the browser, I can see that the single comments are enclosed in some d-tags, but I don't understand it:

What should I type in my case? Should I have it the same, or should I type something like ['d']['UserComments']?

Thank you for your support. 🙂

[iAm_ManCat]

Yup, can show you - when you set it up you can use compose to show what you are referencing at each step, so I added a few compose in here - first one shows the body itself, the second the d subproperty (like navigating into its child objects), third shows the results subproperty inside the d property - and we can see inside that there's a bunch of other properties we can reference (these are the properties of the individual results/version we are looking up)

Here we can see from one of the flow runs that the json structure was initially three layered d>results>OtherProperties

The reason I could use compose was that there were no versions of this file, it was only the original version, once I edit it and versions are available (I am going to assume you have enabled version history for this list or that it is on by default for your tenant), then I need to use the 'apply to each', as I will receive multiple results from 'Send an http request to SharePoint' which are compiled into the d>results> subproperty,

Make sure to type out your code instead of CopyPaste from the blog for these expressions, as the website will encode those as a different thing than the singlequote ' that you have on your keyboard:

I am going to try get the SingleLineOfText1 field for each version (this is where your would use your 'UserComments' field):

Now I can see from the flow run that this was successful and on the second iteration it had both the current and previous values added:

The first value was Purring, but the second version had no change for this property, so it also added Purring because for that version, that is what it was.

If I use another field that did change between versions, like SingleLineOfText2:

This means you can now set this to a variable once you've returned the string it to Power Apps, so you should then be able to use that to display the version history item wherever you need 🙂

I took out the compose actions as these were not needed, only used them for debugging to show you the structure of the SharePoint API call 🙂

Cheers,

Sancho

[janklaska]

Holly smokes! @iAm_ManCat many, many thanks for your detailed explanation man! That was exactly what I needed to know. You're a star! 🙂