In this blog post, I will walk through the process of fetching version history of a Multiple Line of Text field (Append to enabled) from SharePoint list. This is helpful for folks who wants to show history of changes on an item in SharePoint List.
Problem Statement
A company has implemented a customer request management system on Power Apps with a SharePoint List as a data source. A column of type Multiple Lines of Text has been created to record the internal comments while working on the ticket. Now the developer is facing issue while showing all the previous comments on the canvas app for a user who wants to view the ticket status. How to show version history from SharePoint list in a canvas app? How to overcome the limitation of getting last comment only on a multiple line of text column?
Solution
To get data, we will create a Flow in Power Automate to iterate different versions of the item in SharePoint and send the response to Canvas Apps.
We will use "Send an HTTP request to SharePoint" action to perform a REST API operation and then filter out the required results.
If you have already enabled append to on the Multiple lines of Text column, you can start from Part 1 directly.
Part 0: Enable versioning on list
To enable Versioning, open the list in SharePoint Online, select "Settings" from the top right side of the screen and then select List Settings. On the new window, select Versioning Settings. Set Item Version History to YES. Enter the number of versions that you want to keep (between 1 to 50000) and then click OK.
Once that is done, come back to the SP list and create/update the column as per below image.
In this use case, the name of field is "Internal Comments"
Part 1: Power Automate
In this example, we will be getting the data in a Canvas App so I have used Power Apps trigger but this could vary based on the use case.
Action 1: PowerApps Trigger
Action 2: Initialize Variable -> To get the ID of the SharePoint List Item as a string from the canvas app.
Action 3: Initialize Variable -> To store the formatted response.
Action 4: Send an HTTP request to SharePoint -> To get the versions using HTTP request to SP Site. Method: GET Uri: _api/web/Lists/getbytitle('Customer%20service%20requests')/items(int(variables('Item ID')))/versions?$select=InternalComments,Modified,Editor
Customer%20service%20requests -> This is the logical name of the SP List. You can get it from the SP List URL.
int(variables('Item ID'))} -> Convert string passed from Power Apps into an integer.
$select=InternalComments,Modified,Editor -> $select is used to pick columns that will be fetched in the response.
Note: Make sure that you are passing the logical names of the list and its attributes.
Now that we have the response from SharePoint, lets filter it so that the version that does not have internal comments changed is filtered out.
Action 5: Filter Array: This will remove the versions where Internal Comments is null.
Select an output from previous steps: Expression: body('Send_an_HTTP_request_to_SharePoint')?['d']?['results']
Filter: item()?['InternalComments'] is not equal to null
InternalComments should be replaced with the logical name of attribute.
Note: Ensure that you have passed Null from expression.
Action 6: Apply to Each: This will iterate over all the filtered Items: Select an output from previous steps: Output from the Filter Array action
Action 6.a: Append to String variable:
Expression:
items('Apply_to_each')?['Editor']?['LookupValue'] at formatDateTime(items('Apply_to_each')?['Modified'],'dd/mm/yyyy hh:mm:ss tt') : items('Apply_to_each')?['InternalComments']
items('Apply_to_each')?['Editor']?['LookupValue'] -> Fetches the name of person who modified the record.
formatDateTime(items('Apply_to_each')?['Modified'],'dd/mm/yyyy hh:mm:ss tt') -> Fetches the modified date and time in 12 hours format.
items('Apply_to_each')?['InternalComments'] -> Fetches the Internal Comment
Action 7: Respond to Power Apps: Returns the Response variable to Power Apps.
Part 2: Canvas App
Add the Power Automate Flow to a canvas app actionable control. Once that is added, pass the SP List ID and retrieve the results.
Expression: Set(Response, GetVersionHistory.Run(ThisItem.ID).response)
DEMO
This post shows how to get versions of an item from SharePoint list. This demonstration was based on overcoming the limitation to get append to history on a SharePoint multiple lines of text column. I have showed a process that gets the comments history on demand by triggering the flow on the press of a button from Power Apps. This flow can also be triggered when the edit/ view screens are loaded for a selected item. This process can be applied to get version history for different kinds of fields as well.
I hope this was useful for you. In case of any questions or suggestions, feel free to reach me out on twitter at @agarwal_ritika