Power Automate Approval Reminder Summary Email

I prefer to send Power Automate approval reminders as an email with a list of past due items. A single reminder email is much better than receiving one reminder email per late approval. Power Automate does not do this automatically so I will show you my method for creating better Power Automate approval reminders.

Table of Contents
• Introduction: The Approval Reminder Email
• Get The User ID From The Users Table
• List The Approval Requests For The User
• Retrieve The Approval Responses For The User
• Determine Which Requests Do Not Have Any Response
• Check If An Approval Reminder Email Should Be Sent
• Send An Approval Reminder Summary Email
• Run The Approval Reminder Power Automate Flow

Introduction: The Approval Reminder Email

Managers at a plumbing services company use Power Automate approvals. Each manager receives an email reminder showing a list of past due approvals older than 14 days.

The email shows the recipient’s past due approvals, how many invoices are past due and the title of the approval.

Get The User ID From The Users Table

To get the list of past due approvals for a manager we will need to find their unique identifier in the Users table. Open Power Automate and create a new instant flow. Add a text input named User Principal Name. When we run the flow we will supply a UPN in the format [email protected].

Then insert a Dataverse – List Rows action, select the Users table and filter on the internalemailaddress column. Store the unique id of the record being returned in a variable named varUsedId.

Dataverse List Rows – Filter Rows field

internalemailaddress eq '@{triggerBody()['text']}'

Initialize Variable – Value field

@{first(outputs('List_rows:_Get_User')?['body/value'])?['systemuserid']}

List The Approval Requests For The User

We want to retrieve a list of active approval requests for the user that are older than 14 days. Insert a Date Time – Get Past Time action to get the date 14 days ago. Then add a Dataverse List – Rows action to get the desired Approval Requests sorted by newest to oldest. Use an expand query to get the title of the approval.

Dataverse List Rows – Filter Rows

_ownerid_value eq '@{variables('varUserId')}' and msdyn_flow_approvalrequest_approval/statecode eq 0 and createdon lt @{body('Get_past_time:_14_Days_Ago')} 

Dataverse List Rows – Sort By

createdon desc

Dataverse List Rows – Expand Query

msdyn_flow_approvalrequest_approval($select=msdyn_flow_approval_title)

Retrieve The Approval Responses For The User

To determine if the user still needs to respond to an approval we must get their approval requests for any pending approval. Then we will check if there was a response for the same approval. This is necessary because a single approval may require multiple people to respond.

Add another Dataverse List – Rows action and get the Approval Responses records for the user sorted from newest to oldest.

Dataverse List Rows – Filter Rows

_ownerid_value eq '@{variables('varUserId')}'

Dataverse List Rows – Sort By

createdon desc

Determine Which Requests Do Not Have Any Response

We now have a list of approval requests and another list of approval responses. We will determine which approval requests still need a response by looking for a matching Approval Id Index in both lists. Insert a Data Operations – Select action to create an array of Approval Id Indexes from the approval responses. Then add a Data Operations – Filter action to find any approval requests which do not contain an Approval Id Index from the previous step.

Data Operations Select – From

@{outputs('List_Rows:_Approval_Responses')?['body/value']}

Data Operations Select – Map

@item()?['msdyn_flow_approvalresponseidx_approvalid']

Data Operations Filter – From

@outputs('List_Rows:_Approval_Requests')?['body/value']

Data Operations Select – Left Comparison Value

@body('Select:_List_Response_Approval_Indexes')

Data Operations Select – Right Comparison Value

@item()?['msdyn_flow_approvalrequestidx_approvalid']

Check If An Approval Reminder Email Should Be Sent

An approval reminder summary email should only be sent if there are past due approvals. Add a Condition action and check the length of the array returned by the previous filter action using an expression.

Condition – Left Comparison Value

length(body('Filter:_Requests_Not_Having_Any_Response'))

Send An Approval Reminder Summary Email

If there are past due approvals, we must get the title of each approval and send a summary email. Place an Apply To Each action inside of the Yes block and select the body of the Filter action. Add a single Compose action inside of the loop and write an expression to get the approval title.

Then use the Office 365 Outlook – Send An Email action to send the approval summary to the chosen manager. Include the number of past due approvals in the email and an list of the past due approval titles in the body using an expression.

Data Operations Compose – Inputs field

@{items('Apply_to_each:_Pending_Approval')?['msdyn_flow_approvalrequest_approval']?['msdyn_flow_approval_title']}

Office 365 Outlook – To field

@{triggerBody()['text']}

Office 365 Outlook – To Subject

Past Due Approvals Reminder - @{length(body('Filter:_Requests_Not_Having_Any_Response'))} Approvals

Office 365 Outlook – Body field

@{join(outputs('Compose:_Approval_Title'),'<br>')}

Run The Approval Reminder Power Automate Flow

We’re done building the flow. Give it a test run and supply the User Principal Name of a manager who has some past due approvals.

The Power Automate flow executes its actions.

A summary of past due approvals is sent to the manager.

And the approval reminder matches the past due items showing in the Microsoft Teams approvals app.

Questions?

If you have any questions or feedback about Power Automate Approval Reminder Summary Email please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.