Grab staff's manager email and send as "assigned to" using ms flow?

Question

I'm having difficulty on SharePoint list with ms workflow.

I've created New Request sp list and Requested Order sp list, then Employee sp list.

So, Employee List has Manager Column which includes name, email addresses and also Staff Column which includes names, email addresses. Each list item has staffs info in one column and their manager's name & email address in another column within the same Item.

I'm using ms flow approval where "Assigned to" - I need to have business logic that "Assigned to" should go to respective email of the manager.

for example: as shown in the screenshot.

Business logic should be like If logged in user is staff then should pick his manager and send an email for approval

thanks Fera

Answer 1

As per my understanding, following are three list you are working with.

1. Employee List: Keep a track of Unit Manager and Employees within it.
2. New Request List: All the new request are logged in here by end users.
3. Requested Orders: Once the Unit Manger Approve a request, automatically a order must be created here.

Base on above problem, I am using an assumptions here that in Employee list contains the Unit Manager column is single-select People only and not enforcing unique values and Staff Member is multi-select People only column. Also, I am using a lookup column in New Request from Employee list to get the manager. If this not the case, in MS Flow, where I am using SharePoint Get Item you can modify it make a REST API Call to SP to get the right manager. Remaining logic remains same in MS Flow.

For reference use the URL to see how to create approval workflow in MS Flow.

Overall MS Flow:

Step 1: I added the MS Flow to New Request list. The flow will trigger as a new item is created in this list. The Employee list and Request list schema is following, while the order list in my case contains only Title column.

Step 2: Here I am using the trick to get the Manager Email ID using lookup column. In case you are not using lookup, either use SharePoint Get Item action or Send HTTP Request to SharePoint. The flow configuration is following:

Step 3: Once we have the Manager Email ID, we need to use Start an Approval MS Flow action. Except the Assign To all other values are filled using New Request List.

Step 4: Now add parallel branches based on approval condition we can make the decision. If the Approval is 'Yes' we have a set of logic to create a new item in Order list and send confirmation mail, otherwise send the mail to initiator saying request rejected and adding Unit Manager comments.

This completes our approval flow. On Approve we are adding a new item to Order list, if rejected we are just notifying the user. Also to note, this will trigger only when a new item is created. If user modifies the request nothing will happen. They need to submit a new request. We can modify the logic by triggering flow on when item modified.