“Is there a way to store the Power Automate approval history in a separate SharePoint list and link it with the approved document?”
When you build an approval flow in Power Automate, you should always consider how to store the outcome. By default it’s stored in the Approvals app, but that’s not a place that everyone can access. Therefore, you should store it also somewhere else, somewhere easily accessible for everyone.
One solution is to show the approval history in a separate column, another one could be a separate SharePoint list. And that’s the topic for this post: how to store the approval history in a separate list and link it with the item?
Create the approval history list
The list should have a separate column for each piece of information that you want to store. For example, the request ID (renamed Title column), approver name, approver response, comments, and response date/time.
Each approval response should be then a separate item in this list. Add the ‘Create item’ action after every approval action and create new item with the outcome.
Get the link to the request approval history
With the approval history in a separate SharePoint list it’s time to link it with the request. But since the list will contain history for all the items, you can’t just give the users a link to the whole list. They’re not interested in all history, they want only history for the specific item they’re interested in. As such, you’ll have to prepare a link that includes a filter.
Go to the list with the approval history, and filter by the Request ID column. You’ll notice it contains two new parameters, ‘FilterField1’ and ‘FilterValue1’.
FilterField is the column used for the filter, FilterValue is the value you’re looking for. It’s the FilterValue that’ll be different for each request.
Take the whole URL and copy it into a text file, it’ll have a similar format as below:
https://xxx.sharepoint.com/sites/Playground/Lists/ApprovalHistoryList/AllItems.aspx?newTargetListUrl=%2Fsites%2FPlayground%2FLists%2FApprovalHistoryList&viewpath=%2Fsites%2FPlayground%2FLists%2FApprovalHistoryList%2FAllItems%2Easpx&FilterField1=LinkTitle&FilterValue1=2&FilterType1=Computed&viewid=5e0cd73b%2D0c68%2D4264%2Dbc13%2D0cff5f33551dAdd the link to the request
Once you have the link, you can add it to the request using the SharePoint JSON formatting. Go to the list with the requests, and add another column, e.g. Approval History Link. Navigate to the column format and switch to the ‘Advanced mode’.
Take the JSON format from the linked article and combine it with the URL you just copied. But don’t take it as it is, it has a preselected filter for the specific request ID. For the final link the ID must be dynamic – always using the ID of the current item.
To replace the fixed ID with a dynamic ID split the link into 3 pieces. Everything before the request ID, the request ID, and the rest.
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "a",
"txtContent": "Approval history",
"attributes": {
"target": "_blank",
"href": "='<everything before the request ID>' + <request id> + '<the rest>'"
}
}Using my link above as an example:
xxx.sharepoint.com/sites/Playground/Lists/ApprovalHistoryList/AllItems.aspx?newTargetListUrl=%2Fsites%2FPlayground%2FLists%2FApprovalHistoryList&viewpath=%2Fsites%2FPlayground%2FLists%2FApprovalHistoryList%2FAllItems%2Easpx&FilterField1=LinkTitle&FilterValue1=
2
&FilterType1=Computed&viewid=5e0cd73b%2D0c68%2D4264%2Dbc13%2D0cff5f33551dReplacing the request ID with the reference to current item ID and putting it all together…
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "a",
"txtContent": "Approval history",
"attributes": {
"target": "_blank",
"href": "='https://xxx.sharepoint.com/sites/Playground/Lists/ApprovalHistoryList/AllItems.aspx?newTargetListUrl=%2Fsites%2FPlayground%2FLists%2FApprovalHistoryList&viewpath=%2Fsites%2FPlayground%2FLists%2FApprovalHistoryList%2FAllItems%2Easpx&FilterField1=LinkTitle&FilterValue1=' + [$ID] + '&FilterType1=Computed&viewid=5e0cd73b%2D0c68%2D4264%2Dbc13%2D0cff5f33551d'"
}
}
… you get a link to the pre-filtered view on the approval history list.
Summary
Having separate SharePoint list with the Power Automate approval history has a few benefits. No matter how long the history is, it won’t affect the request list/library layout. You don’t have to update the request after each approval step, in fact you don’t have to update it at all. The link is created by JSON formatting and as such it’s built on the user side, it’s not stored in the item/document. And maybe the biggest benefit, you can reduce permissions on the whole list – set it to read only for the users and keep the edit permissions only for the flow account.
On the other side, the approval history won’t be as easily visible as if you had it directly in the item so it’s up to your preference which solution you use.
Thanks a Lot
This is way too useful!
I only have one problem, I think this applies when we have only one approver.
But how would this work for multiple approvers? I mean, what we´d like to do is after the whole process, generate a PDF with the name, result, date and comments of each approver.
So I´m wondering how to store data for each approver
Thanks a lot again
Hello Humberto,
it works for as many approvers as you need – the ‘Apply to each’ loop with ‘Create item’ will create an item for each approver with his/her response.