Either because we created too many rows when we were testing or because requests are no longer applicable, the approvals module in Power Automate can end up with plenty of requests that do not require action:
In this blog we will show you how we can use Power Automate and Dataverse to cancel old requests.
Table of Contents
Some background information
All data related to the approval requests sent from the “Approval” connector in Power Automate are stored in a Dataverse table. You can find this table in the Dataverse portal:
When a new approval request is sent (either if you just create it or wait for it), a record is added in this Approval table and every request has a unique ID assigned when it’s created. The unique ID is returned in the “Approval ID” column.
We usually use the “create an approval” action for example when a new item is added in SharePoint and it requires someone to review it, so we can create a column in our SharePoint list to store this unique ID to use it later to send reminders, to cancel old requests or to simply better track which record belongs to each approval request.
Step 1. Get all pending approvals
For this use case, we will be getting all pending approvals directly from the Dataverse table, but if we are storing the approval ID in a column of a separate database, we can use this value to directly perform an action on this approval.
For now we want to cancel any pending request that is older than 120 days.
The column we can use to identify open requests is the “Status reason”, any record = “Pending” is a request for which approvers have not taken any action on.
The first step in our workflow then should be a “List rows” from the Dataverse connector. We need to select the “Approval” table and then add some filters in the “filter rows” field
statuscode eq 192350001 and createdon lt @{addDays(utcNow(),-120)} v
- The “Status reason” column name is statuscode, and value 192350001 corresponds to any pending record, so this is the first filter we need (this will be a fixed value and column name for all tenants and environments).
- As a second condition, we will filter any request older than 120 days (this filter depends on your specific needs).
The result of this action will be a list of all open approvals that were created over 120 days ago.
Note: you can also store the approval ID in a separate list in SharePoint that is populated every time a request is created and approved, and use the “Get items” action to extract the approval ID and status.
Step 2. Cancel requests
Option 1 - Dataverse connector
If you have a premium account, you can use the Dataverse connector to update the records with pending status and cancel the approval requests.
Just find the “update a row” action, select the “Approvals” table and in the Row ID field, just select the “Approval (unique identifier)” column from the list of rows action.
Stage: Complete
Status reason: Canceled
Status: Inactive
Option 2 - HTTP With Microsoft Entra ID (Pre-authorized) connector
Find the HTTP With Microsoft Entra ID (Pre-authorized) connector
Select the “Invoke an HTTP request” action
You will be prompted to create a new connection, use “https://approvals.teams.microsoft.com” as value
Next, select “DELETE” as method.
Headers:
Accept: application/json
URL:
/api/cancelApproval/@{items('Apply_to_each_2')?['msdyn_flow_approvalid']}?flowEnvironment=@{workflow()?['tags']?['environmentName']}
Final Result:
Step 3. Test automation
And you’re all set! You can use this solution with Power Automate to get old approval requests and cancel them. This will also help approvers not see old pending requests in the “Approvals” module in Power Automate, this way we ensure this screen will only show them records they need to take actual action on.
Got questions? Contact us here or at contact@powergi.net
Conclusion
Managing old approval requests in Power Automate with Dataverse is essential for maintaining your environment clean.
