Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

How to log approval history in a SharePoint list with Power Automate

Posted on April 3, 2022April 3, 2022 by Tom

“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.

Power Automate SharePoint approval history list

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’.

Power Automate SharePoint approval history list

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%2D0cff5f33551d

Add 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%2D0cff5f33551d

Replacing 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'"
  }
}
Power Automate SharePoint approval history list

… 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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Now available:

The Ultimate Power Automate expressions cheat sheet
Equip yourself with the tool to translate your thoughts into Power Automate expressions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • Format results of date calculation in SharePoint columnJune 29, 2022
  • Why is your Power Automate flow creating duplicatesJune 26, 2022
  • How to create a unique identifier in your Power Automate flowJune 22, 2022
  • How to assign custom SharePoint permission level with Power AutomateJune 19, 2022
  • Remove permissions from a specific SharePoint user with Power AutomateJune 15, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme