Dynamic Approval workflow based from a Share Point list

[AllanMartins]

Hey there,

I do have two lists in Sharepoint:

List A

[Requisition];[Status]; [Date]

Req-01;Pending;2017-05-04

Req-02;Pending;2017-05-05

Req-03;Pending;2017-04-04

and another

List B

[Requisition]; [Approver];[Order]

Req-01;myname@email.net;1
Req-01;anapprover@email.net;2
Req-01;superapprover@email.net;3
Req-02;myname@email.net;1
Req-02;anapprover@email.net;2

Basically, I would like to send an email for each Approver (from List B), where each recipient would get an O365 Approval Email (in the order: so, the 1st approver would get, and routes to the 2nd after his approval, and so on, if anyone rejects, the process is suspended and will not proceed to the next person), at the of the process, it just updates the List A with either "Approved" or "Rejected".

can anyone think in a smart way to get it to work?

I have been using Flow/PowerApp for a while, so I could manage to get it working by creating a "cascading/nested", but in this case I had to create every single action for all the scenarios (I did for the case of 3 approvers, not more than that), so I did a first Select Case, where if it is 1 approver, where updates the SP List with his response

In case of two approvers, if the first Accepts, sends to the 2nd for response, and then updates the SP accordingly finishing the process.

For the 3 approvers, the same thing, but adding another step of case boxes above.

However... I would like to give it some flexibility as this could have up to 10 approvers! surely, I could just go, design the flow, add a first set of 10 cases boxes and design each nest case by case, but on the 3 approvers solution, the flow just became too slow to Edit, and it crashes the browser constantly - check the size it gets!! I could even expand it more... (I believe this could be due to the ammount of variables) so I assume this idea is not very appropriate, and plus, would give a lot of headache when trying to make any change on it.

Any help on this case is much appreciated!!

[v-micsh-msft]

Hi @AllanMartins,

I think the point here is the flow need to send Approval Email based on the previous Approval Email Action result.

There are some limitations within the current flow:

For example,  the Approval Email can't be added within an Apply to Each,  an Apply to each can't be nested with another Apply to each.

So the proper way here is what you have done, select one Approval after another.

Regards,

Michael

[Anonymous]

Good day ,

Can you perhaps send an example of how to do it please? I need to create a Flow with 3 approvers (if 1 approves it goes to the 2nd and 3rd etc)

many thanks

[AllanMartins]

That is how I managed to do it:

First, created a Sharepoint List where all Items are store, one of the columns I have a unique ID to make sure I keep track of it. Let's call it "MainList"

Then, I created a second Sharepoint List, where it contain all the approvals handling, we'll call it "Approvers"

It would be something like that:

MainList
- PKUniqueID
- Type
- Total

Approvers
- FKUniqueID
- TriggerFlow
- ApproverOrder
- ApproverName
- ApproverEmail
- Status

The MainList contains all relevant information, while the Approvers has the referenced approvers information. The benefit of this solution, is that I managed to have a "dynamic" Approvers list that will suit any quantity of Approvers, having just one or even 20 (I tested!) it simply works.

Here is a sample of the data for these two Sharepoint Lists

 

Now, the Flow portion of the process:

I created a flow where the trigger is "When a Sharepoint Item is changed", and indicated the Approvers list. Now, everytime a item is created or changed, it runs the process, and now it's the most complex part of it and I will try to explain it:

1. Trigger Checks if the flow is to be run, or not (this is useful as a lot of data is added or changed on that list)
 1.1 If is set to false, then it does nothing and stop the flow
 1.2 If is true, then move to the next step (2)...

2. List all the Items from that same list, which has the same FKUniqueID: this will generate a list of all the Approvers associated to that process. Ordering then by the ApproverOrder, and counting only those where the Status is blank is important to make it work properly

3. Then, it takes the first item from the previous list, that item will indicate the recipient of the approval: if it is the first run of this process, then it should email the ApproverEmail value from the ApproverOrder = 1. It emails the indicated address prompting for the input (either Reject or Approver, or any other action you define)
 3.1 If the Approver Rejects, it updates the item's TriggerFlow value from TRUE to FALSE (the same that Triggered the Flow), Status to "Rejected", and then it wont run anything else, Recommend to send the user a notification so he knows the status
 3.2 If it is Approved, it updates the item's TriggerFlow value from TRUE to FALSE (the same that Triggered the Flow), and Status to "Approved", then move to the next step (4)...

4. Check if this just changed item Order equals the Quantity (based on Count) of Items generated on the item 2, it will indicate if this is the last Approver or not
 4.1 If the ApproverOrder = Count, then it was the last Approver, then it finishes the process (indicating it was approved!)
 4.2 If the ApproverOrder <> Count, then there are still Approvals pending for this Requistion, so it move to the step (5)...
 
5. Based on the query generated on the item 2, it takes the next Item (Where ApproverOrder = ApproverOrder+1) and change it's FlowTrigger value from FALSE to TRUE: it will run the same steps for the next Approver.
 (So, if it was the first Approval (ApproverOrder = 1), out of 3 approvers, it will update the item of the next Approver (where ApproverOrder = 2))

Here is a simply diagram I did for that, please, note it is simple and follows no standards:

 
 

Some important notes for that:

- make sure to always check the flows, if you use a "O3365: Send an Approval Email" service, responses can be only given within 7 days, if not, the flow will fail (you can manage fails, but you should handle how it can adjust for your requirement)
- I recommend to generate any portion of the body (of the Approval Email) by using a Compose, it is handy! Then, you just add its output to the reffered action
- The trigger you design for this process will be executed for EVERY change on the Approvals Sharepoint List, make sure you change any item's TriggerFlow value to FALSE before making any "manual" change on it, otherwise, the recipient will get messages for any change done on this item...

I hope this can help you and bring a light to whatever you need to do. This was the only way I figured out to manage these approvals, otherwise, I would have to design a huge flow, with nested actions and handling all of these multiple approvers (I managed to do it with up to 5, but it just become too hard to make any change on it, the page crashes, takes a lot to do anything, and if you keep insisting, you end up corrupting your flow)