Power Automate (CDS) daily approval reminders

Approvals in Power Automate are very useful to automate approval processes. Unfortunately, some functionalities are still missing, including approval reminders.

Searching on the internet returned some results to provide approval reminders but they all had shortcomings which you can find below.

• The solution uses a lot of API calls (do-until loops that are constantly running to send reminders). Discover why you need to take Power Automate API requests limits in mind.
• The reminder functionality is built in into the flow that triggers the approvals. For every approval flow, you need to rebuild the reminder functionality.
• Reminders are still sent to people that have already approved/rejected. When they already answered, they shouldn’t receive reminders anymore.

Because of these shortcomings, I started searching for an alternative solution. The goal was to remove the reminder functionality from the approval flow and build it for all approvals in the whole environment. This way it can be used by all flows that are built on top of Power Automate Approvals.

Microsoft stores the approvals in CDS in three tables. This is very important to understand the logic of the reminder flow:

• Approval: contains all info about the approval: Title, Description, …
• Approval Request: has a relation with the “Approval” table and contains all sent approval requests
• Approval Response: has a relation with the “Approval” table and contains a record for every record in the “Approval Request” table

Requirements

(Service) Account

It is recommended to execute this flow with a service account because e-mails will be sent by the Outlook mailbox of the owner of the flow. An alternative is to send the e-mails from a shared mailbox using the appropriate action in Power Automate.

Licenses

To access CDS with Power Automate, you need a premium license.

• Per user premium license
• Per flow premium license

See Power Automate pricing for more details.

Permissions

Only user accounts that have at least “Environment admin” permissions have access to all approval records in CDS.

You can check the roles in the Power Platform Admin Center. Click on your environment, click on the user (service account), check the assigned roles.

Building the approval reminder flow

I create a scheduled flow that triggers every morning and executes following logic:

1. In the first action, we define in an array variable on which interval (days) we want reminders. This can be for example for approvals that are 2, 4 or 6 days old. The next steps will go further on this example of reminders on 2, 4 and 6 days.
2. Define two date variables depending on the min. and max. values of the array variable.
3. Get all approvals requests that are created. Only approvals that are not yet 6 days old will be returned. Older ones are not necessary because we don’t want to send reminders for approval requests older than 6 days.
4. Get all approval responses. For every approval request in CDS, an approval responses is created once the approval request is answered with approved/rejected.
5. Get all ID’s of the approval responses.
6. Filter out all the approval requests that are already answered based on the ID’s of step 4.
7. Check it there are open approval requests. If not, stop here.
8. Loop through all open approval requests.
9. Calculate how many days ago the approval request was created.
10. If the approval request is 2, 4 or 6 days old, continue. If not, stop here because we don’t want to send reminders.
11. Get the details (title and description) of the approval itself. The title and description are not saved in the “Approval Request” or “Approval Response” table but in the “Approval” table.
12. Send the reminder e-mail to the person that still needs to approve with all the details and a link to the approval form.

A complete overview of the flow can be found below.

If you’re interested in the reminder flow, try to recreate it yourself based on the screenshot or leave a message in the comments section.