Multiple approvers with individualized roll up reminder emails flow.

[mpistil]

Thank you in advance to all those who read through this -- it's complex, but could be really cool if it can happen.

I built an app for processing incoming invoices. Via a form, the app user can enter in the invoice details as well as select, via Active Directory, the two individuals who will be the approvers – named “Approver1” and “Approver2”.

In SharePoint, “Approver1” and “Approver2" are Person/Group column types. (Long story short, I also have columns “Approver1NameText” and “Approver2NameText” that have the identical data, just as SLoT columns – a separate flow makes sure that the column pairs are always in sync).

So within the app, say Jesse James was chosen as Approver1 and Case Fox was chosen as Approver2. Here’s how my SharePoint list would look:

With two approvers to each invoice, I created an Approvals sequential flow. Once it gets kicked off and is with Approver1 for review, the “Status” Choice column (which you could see as the status of the overall invoice – options: new, under review, approved, and rejected) gets changed from “New” to “Under Review”. I have an Update Item action that also changes the “Approver1ApprovalStatus” SLoT column from blank to “Under Review”, marks the current date in the “TimeSenttoApprover1” Date column, as well as changes the “Approver2ApprovalStatus” SLoT column from blank to “Not Started”.

Once Approver1 approves, another Update Item action changes the “Approver1ApprovalStatus” from “Under Review” to “Approved”, changes the “Approver2ApprovalStatus” from “Not Started” to “Under Review” as the approval now sits with Approver2, and marks the current date in the “TimeSenttoApprover2” Date column.

Once Approver2 approves, I have an Update Item action that changes “Approver2ApprovalStatus” from “Under Review” to “Approved”. (Once Approver2 approves, the “Status” column will also be updated from “Under Review” to “Approved.”)

Here’s an updated look at how the SharePoint list would look when the approval is in Approver1’s hands:

Here’s an updated look at how the SharePoint list would look when the approval is in Approver2’s hands:

Now here is where my question comes in 😊

I need to send reminders when an approval request is 3 days old. But, how do I do it within one roll up email, per approver, for both roles as Approver1 and Approver2?

I know that I must first Get Items from the SharePoint site with a filter that has the “Status” column = “Under Review.” But this is where it gets tricky…. I want the flow to look through each individual approver I have (which I do have in a second SharePoint list) and identify all the records that has them listed as either Approver1 or Approver2. If they are listed as Approver1 with a Approver1ApprovalStatus of “Under Review” AND TimeSenttoApprover1 is 3 days past or as Approver2 with a Approver2ApprovalStatus of “Under Review” AND TimeSenttoApprover2 is 3 days past, I want the flow to grab those records and put them into an HTML table and send that roll up to each of the approvers.

So here’s an example:

Jesse James would not receive an email as the “Under Review” records he is tied to as either Approver1 or Approver2 indicates he has satisfied the request. Jane Chatwin would not receive an email as she has not yet been sent the Approval request from the second record. But, Case Fox would receive a roll up HTML table email showing basically what is shown above since he has not yet approved the first record (which was sent 3 days ago) as Approver1 as well as not yet approved the second record (which was sent three days ago) as Approver2.

Right now, I can get this to work via two separate flows (which means two separate emails). But it would be ideal to roll it up into one email. Here's what I have:

Again, REALLY appreciate any help with this matter! 🙂