Complex Approval Question

[PowerappsMax]

Hello, I am building a complex flow for approvals. It involves approvers in a credit card receipt application. 

CURRENT SETUP:

Currently, when a user submits a receipt a power automate flow determines who their approver is and creates an "Approval Record" in a table consisting of a matched record ID of the submission and who the approver is. There is a secondary Power App that an approver would log in to and have a gallery filtered to items where they are listed as an approver. Once they click to approve it would consider the record approved and that would be the end of it. (I am not using power automate approvals natively do to the dynamic nature of this process)

PROBLEM:

• Every submitter of a receipt has a default approver
• Every approver has a $ threshold that they can approve
• All information is in a SharePoint list configured as below

Sharepoint List Name: approver_table

name	emp_id	approval_threshold
John Doe	1234	$5000

SITUATION:

• A cardholder submits a receipt for $10,000
• Their approver (Approver1) can only approve $3000
• The approver's approver(Approver2) can only approve $8000
•  The approver's approver approver (Approver3) can approve $15,000

PROBLEM:

The issue here is the cardholder submits a receipt above the threshold their supervisor/approver can approve. The way the system needs to work is that while the 3rd approver has the threshold to approve, the other two still need to be approvers on the record. This is not a static example as there could be situations where the approver's approver has the correct approval limit or there could be situations where there could be up to 5 approvers before someone has the right threshold. I am trying to figure out a way to make it dynamic and below is my thought process.

CURRENT SETUP OF APP/FLOW:

What I think needs to happen is when the flow runs use a "Get Items" action and with some type of expression, get records of all the approvers until it finds someone who meets the dollar threshold criteria and then stops getting records. This way it would not get every approver who has a higher approver threshold than the amount that was submitted and then would stop at the first person who has it. From there I think I can manage it. I am just unsure about how to write such expression

Please let me know If anyone needs more information on this problem or the process in general!

Thank you so much in advance!

[Rhiassuring · Accepted solution]

Hi there, 

I appreciate how well your post is written - thank you for that!

I just wrote a solution that was about finding an string of approvers up until a certain term was reached - and, I will link that for you, as it's the same situation -- though instead of "Do Until HOD", you're "Do Until next approver value is greater than current receipt".  (Which I think is also how you explained what you're currently doing, in a way.) 

Please take a look at it and see if you also see the connection in the approach:

https://powerusers.microsoft.com/t5/General-Power-Automate/Get-Hierarchy-of-Users-Email-IDs/m-p/1594...

(And of course you'd probably be sending an Approval task instead of emailing them all at the same time? Though I guess you'd likely achieve the same by doing an "Everyone must agree" for approval, if you need every approver's "OK" on it...)

What I don't see in your post is how you identify who the approver's approver is. I see you have John, his employee ID, and his Approval Limit - but who is the approver above him, and how do we link it to them? Are there several lines of approvers, or is it just a hierarchy based on approval maximum?

[PowerappsMax]

Hello! Thank you for the response as well as a great solution!

I do not know how I overlooked the columns but below is the actual structure of the table!!!

name	emp_id	approval_threshold	approver_id	approver_name
John Doe	1234		5467	Jane Doe

I took a look at your other post and that process is very similar to what I am trying to achieve. I am going to take a try at constructing something for it and will certainly make a new post with any issues I run into but until then this is certainly the solution I was looking for! 

Thank you so much for your insight!