“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.
Does this track history as it happens or when the flow is actually complete? I’m only asking b/c the GCC High doesn’t have the Approvals App in teams yet.
Hello Sonia,
it updates the history after each approval step, which can have multiple approvers -> you don’t see the real-time result, you get all at the end of the action. But if each approval step has only one approver then it’ll keep it history up to date.
Hi there, I’m a novice flow user.
What would the trigger be in the example you provided?
Hello Esther,
the example is probably from some approval flow so either ‘For selected item/file’ trigger or automated trigger starting on ‘Item created’.
This is awesome. One question, would the ApprovalHistoryList also display when users haven’t approved or rejected yet or does their names only appear in the list once they complete the approval action?
Hello John,
only after the approval action is completed, Power Automate won’t give you intermediate results.
Hello I am looking into a solution, this may be it with some tweaking, to allow the internal users run reports to see requests and their statuses.
I am using a Cognito form for the file submission and the filename title includes the names of outside healthcare providers for the internal users to know who submitted.
If Power Automate doesn’t give intermediate results, do you have a solution for having requested approvals show up on the list for audit tracking?
For example, if a provider submitted reports and nobody took a look at them.
Thanks!
Hello Chris,
I didn’t use Cognito forms so I can’t how it’d work. But if the data is stored in some form of a database then sure, you can build some flow that’ll process them.