2 step dynamic approval workflow

[andjelamiha97]

Hi everyone. I am trying to build a flow with a 2 step dynamic approval. I was looking at @RezaDorrani videos and he has awesome content, but i just can't seem to figure out how to do this. I will include my flow below and the lists from which the flow is pulling out the information from. 

As i said this would be a 2 step approval, but both approval steps to be dynamic in nature. We have a list of applications as one column, asset owners as a second column, and access provisioning as a third column. The flow would work like this: So, we have a SharePoint Site>end user goes there to submit a request for app access>chooses the app from the dropdown menu >we want the asset owner to approve it first (to dynamicaly pull it from the second column), once it is approved>it goes to the second person to create the account (to dynamically pull the person from the third column). So, basically to pull the approvers 2 times from different columns. My approval matrix has : application, asset owner approver, access provisioner. I have made the flow work where only the first part is dynamic. It goes to the appropriate asset owner (pulling it from the second column)>and then the second part of the flow start another approval, but i couldn't make it dynamic (to pull the access provisioners from the third column.)

This is where the user submits a request - when they choose the application it goes to the appropriate asset owner, and then it should go to the appropriate access provisioner(column named provisioning), (still trying to figure that part out).

This is the list and the columns from which the flow is pulling the info from.

My flow:

This is the approval that i would like to be dynamic. Basically like the first one. I would want the second approval to choose the right people from the "Provisioning" column and send it to the assigned people

This is where i would want the approval to be dynamic in nature as well. I would like for the flow to pull the people from the "Provisioning" column and send the approval to the assigned people based on each application.

Can someone please help me? @MarconettiMarco @Pstork1 @RezaDorrani 

[Pstork1]

Who and how is the Provisioning column assigned? That appears to me to be the sticky spot.  Having the flow pick and send the approval to the right person is relatively easy if the selection is there.

Who fills in the Provisioning column? And at what point is that choice made? 

[andjelamiha97]

So, I will fill the column out. As you can see there is only one name in the "Provisioning" Column. I put it like that for testing purposes. So, if someone goes and wants to send a request to access adobe, the flow would first send it to the assigned asset owner (the second column), once it it approved, it would send it to the person in the "provisioning" column. For the provisioning column, depending on the app, we would have 1 person assigned, maximum 2 or 3. But we can start with one. So, that is all i need, to make my flow send it to the right person by pulling it from the "provisioning" column, dynamically. Thank you for responding so fast! So, once i have the flow ready, i will fill out the column completely, it won't be empty. I just left like this for now, before i get the flow going and test in on myself first.

[andjelamiha97]

Would you please be able to help me add that additional dynamic approval for the "provisioning" column as well? The first one already works - it chooses the person from the "approver" column, i would just want to add another one. Once the "Approver" approves, to move to the second "start and wait for an approval" but to choose the people from the second column "provisioning". I tried this by adding another "initialize a variable" and then setting a variable, my flow runs successfully, but nothing happens, nobody gets anything. If you can please help me 🙂

[Pstork1]

Since you are using the When an item is created trigger then the values in both columns should be available inside the flow. The flow would be pretty simple

1) Trigger when an item is created

2) Send the approval to the person in the Approvers column ad wait for the response

3) If the Outcome doesn't include "Reject" then send the approval to the person in the Provisioning column.

[andjelamiha97]

Hi. Yes. I am having troubles with including both. Would you have an example? Would you please tell me how to do that or how to build it? I know I am asking for a lot, but I have spent the past 4 days working on this and no success, i am desperate. 

[ChadVKealey · Accepted solution]

To keep things organized, I would suggest adding two "Compose" steps at the start of the flow, one to store the Approver email(s) and the other the Provisioner email(s). If those columns are single-select Person columns, it's pretty easy to set the Compose action; just select <column-name> Email from dynamic content. If one or both is a multi-select person field, it's a bit more complicated. The cleanest way to handle that is with a Select action which produces an array of the email addresses and then the Join expression to concatenate them into a semi-colon separated string (which the Approval action will accept in the Assigned to field). See https://youtu.be/fjYNT0EgzAU for a walk-through of the select-join process for a multi-select person field.

[Pstork1]

Here's an example using a single approver and a provisioner. I did add a choice field to record the approval status.

[Pstork1]

See the screenshot above.  You don't need a variable if the provisioning user is there when you create the item.

[andjelamiha97]

Hi. This is where i probably didn't explain. I have 2 different lists. One is called RFA Submissions and the other is called Applications. The RFA Submissions is where a person will submit a request, they will open a new form and they will have a dropdown menu to choose the application (application name). The dropdown menu is connected to the second list "Applications" where all the applications are listed (title column), approver column and a provisioning column. So "when an item is created" is happening in the "RFA Submissions" list, the person submitting a request doesn't even see the "applications" list nor who the approver or provisioner is.

This is the list where the user will come and submit a request. They would click on the "new" button.

The pic above - Once they click on the new button, the have to input some general info, and there will be a dropdown menu of all the applications that are listed in the "applications" list. The applications list is just used for the flow to pull the correct approver and provisioner based on what application they choose from the dropdown menu in the rfa submissions list.

The pic above - this is the dropdown menu i was talking about. Once they select the application, the flow would pull the assigned approver from the applications list for that specific application, and that part of the flow is working. But i do want to add another step, that once it is approved, it will go to the second person, the provisioner, based on the application they selected. So, if they selected Adobe, it would go to Andjela Mihajlovic to approve, once approved, it would go to Andjela to create the account (those names are for testing purposes only). I hope I made it clear.

The above pic - this is the applications list. The user wouldn't come here to submit the request. This list is used by the flor to pull the right people based on the application they selected from the rfa submissions list from that dropdown menu. 

This is a part of my flow where i connect the title from the "applications" list (the title is where all the apps are listed) to the "application name" dropdown menu from the rfa submissions list, where they send the request.

Can you please help me do this?

[andjelamiha97]

Hi, thank you for you reply. Do i just add the compose steps to the flow i already have?

[Pstork1 · Accepted solution]

The only change then would be to use a Get Items, which you are doing,  from the trigger to get the one application from the applications list that matches the value of the dropdown selected.  The rest works the way my sample is setup except you use the first() value from the get items of the application list as the approvers and provisioners values.  So all your really need is a variable set for Object and load the First record from the get items into that.  it should look like

first(items('Get_Approvers')?['body'])

[andjelamiha97]

Thank you! Is there any way to advance the flow for the user to be able to choose multiple applications from the dropdown menu, but for the flow to work in the same way. So, let’s say i want to submit the request  and i want to select 5 applications from the dropdown menu, but for the flow to send the approval to 5 different people (approvers) assigned to those selected applications and then to send them to 5 different provisioners?

[andjelamiha97]

Thank you! Is there any way to advance the flow for the user to be able to choose multiple applications from the dropdown menu, but for the flow to work in the same way. So, let’s say i want to submit the request  and i want to select 5 applications from the dropdown menu, but for the flow to send the approval to 5 different people (approvers) assigned to those selected applications and then to send them to 5 different provisioners?

[ChadVKealey]

Yes, you could modify the "Application" lookup column to allow multiple selections. Just be aware that doing so will change the format of data in that column to be an array. So, in your flow, you would need to put all of the existing actions (except the trigger) into a "Apply to each" loop that iterates over each selected application. 

So, yes, absolutely possible, but it makes the process just a bit more complicated because of the loop.

[andjelamiha97]

Hi! Thank you so much for getting back to me.

This is my flow right now: And how it works, the person submits a request choosing the application>the flow pulls the information of the approver from the applications list>sends it for approval>if approved>pulls the data of the provisioner>sends it for approval

So, I would like my end user to be able to select for example 3 applications (i already put it to be multiselect), and for the flow to pull the different approvers for those 3 different applications>sends the approvals>if approved (i am curious to see how would that work, do we wait for everyone to approve or if i put first to respond and then lets say application one get approved, it moves to the second step, application two get approved, moves to the second step)>once they get approved one by one to keep sending them to the second person(provisioner)>in this case 3 different people as well and wait for an approval..

Would you please be able to show me which part i should change and where exactly should i make those changes?

[Pstork1]

Essentially, you'll put everything except the trigger inside an apply to each loop.  Then set the loop to run in parallel.  Inside each loop you will process one application.  The biggest issue will be what do you do if not all the application requests are approved?  You might pick 5 applications and have 3 approved and 2 denied.  Does that mean all the applications are denied? I suspect you would be better off having the user make the request on a per application basis and just running the flow multiple times.  Otherwise handling all the different permutations will get very complex.

[andjelamiha97]

Hello Chad! I am working on creating another flow that would send the certificate expiration reminders to the assigned cert owners. So, I have a list with all the certs, their expiration dates and their owners (the people i would like to receive an expiration email reminder). I would like for them to receive an expiration email reminders 90, 60, 30,14 days before the cert's expiration date. How would i go about this? Would you please be able to help me?

[ChadVKealey · Accepted solution]

This is certainly possible, and there are several ways to accomplish it. Some would complicate the list (adding calculated columns for each of the reminder dates), others would complicate the flow (calculate the reminder dates and then "Get items" from the list where the reminder date is the current date). I would choose to keep the complication in the flow (because users interacting with the list could have an impact on those columns). 

Here's how I would do it:

1. Use the recurrence trigger to run the flow each morning.

2. Use the Add to time action to add 90 days to the current date and format the output as yyyy-MM-dd

3. Add a Get items action to retrieve all items where Expiration date is less than or equal to that calculated date

4. Add a Filter array action to identify those items (from the Get items output) where Expiration date is equal to the current date plus 90 days

5. Add an Apply to each loop to iterate through the output of the Filter array action and send an email for each item

6. Add more "Add to time" actions for the other milestones (60 days, 30 days, etc.) and Filter array & Apply to each actions for each (basically, repeat steps 4 & 5 for each milestone)

The premise here is by getting everything where the expiration date is less than or equal to the current date plus 90 days, you get all of the items you might need to send an email for with one API call to SharePoint. Then, you can just filter the data that's returned recursively for each milestone. This is the most efficient way to do it from SharePoint's perspective, but if you'll have LOTS (like, 5,000 or more) items being returned in that first action, you might be better off repeating the Get items action for each milestone date.

[andjelamiha97]

@ChadVKealey Thank you! 

I have another scenario if you could help with that. And it goes back to the original question of this post.  I have a list with 4 columns "Application name" and a person column "Approver", a person column "Provisioner 1", and another person column "Provisioner 2". Specific people are assigned for all 3 person columns. When an item is created, i want it to start and wait on approval and send it to the assigned "approver" first, then once approved to start and wait for an approval again and send it to the assigned "provisioner 1", and then once that one is approved, start and wait for a third approval and send it to the "provisioner 2". 

I got the first 2 approvals to work, but i am having issues with the third approval that is pulling the information from the "Provisioner 2" column. This column has people assigned to it, but some are blank, non assigned. I would like for the flow to check if anyone is assigned first. If yes, move it forward and send it to that last person "Provisioner 2" for final approval. If no one is assigned, i would just like for the flow to be done or whatever else is needed to make the flow be done, if that makes sense. I have followed your video for multi-select approvals with compose, select and join. I just can't figure out how to check if the column is empty (does not contain an assigned person), before starting and waiting for an approval for the "provisioner2". Would you be able to help with that?

Please see below the error i receive.

This is my flow before it is ran.

Please note "Provisioning1" in the flow is "Provisioner2" column i was referring to.