Send approval work flow to different email depending on department selected in MS Form

(0) Share

Report

Posted on by CD-11060934-0

We have a lot of MS forms that we use for various approvals. Currently the forms ask who the budget holder is (selected from a list) and this is used in Power Automate to send the approval emails. It does work but there are issues when a budget holder leaves and I have to update 20+ different forms.

I'm hoping there is a way to do a lookup based on the department selected that would return the budget holder email so I can just have one dataset that would feed into all the forms. I have tried searching but I've not found what I'm looking for. There are around 60 departments so using Switch would be too time consuming to write the rest of the flows (also, many forms already use a Switch). I'm reasonably experienced with PA but but probably only it's basic functions.

The general approval flow layout (some go through multiple approvals or use a Switch but this is the basics of it):

When a new response is submitted

Get response details

Start and wait for an approval

Condition

Approval/rejection emails

Is this something that can be done easily? If so can you point me in the right direction to do it? I did have a look at Expressions but I don't know enough about them to be able to figure out if one of these (or combo of them) can help me.

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Suggested answer

David_MA 14,956 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

I would create a SharePoint list with the 60 departments and include a people field so you can populate it with the person that needs to be notified. Then update your forms so that users choose the department rather than the person. You can break inheritance on the SharePoint list so only the site owners can update the people.

In the SharePoint list, ensure that the department field requires unique values so there can only be one item with IT, HR, R&D, etc. When a new form is received, use a Get items action with a filter query where the department field value from the form equals the value in the department field in SharePoint. You can then use the e-mail value from the people field in SharePoint for the approval. This way, all of the workflows for your 20+ forms point the same list to get the approver's e-mail and you just have one list to update. It also makes it easy to update the list with a temporary person if someone goes on leave or vacation.

When you populate the approval or e-mail with the e-mail address from SharePoint, the flow will put it into an apply to each since you're using a get items action. So you may want to use a condition with a length() expression to make sure it returned 1 item. If it does, you can put the apply to each on the yes side and use the no side for error handling, which would mean either no match was found or there was more than one item with same department value.

We use this type of setup for many different scenarios, and it is one of the easiest ways to deal with what you are describing. We call it a DOA list (Delegation Of Authority).

Another benefit of using a SharePoint people field is built-in validation. You can only select users with active accounts in your directory, so you avoid typos or invalid entries. With a choice field in a form, the values are just text, so there’s nothing preventing someone from entering or maintaining incorrect names.

Was this reply helpful? Yes No
CD-11060934-0 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks. This sounds like exactly what I was looking for. I'll test it out.

Was this reply helpful? Yes No
Suggested answer

Sajeda_Sultana 189 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @CD-11060934-0,

@David_MA has already provided a solid and scalable approach.

One small improvement you could consider is adding support for backup or multiple approvers in the SharePoint list (for example, an additional column or allowing multiple people in the People field). This makes it easier to handle cases where someone is on leave - no need to update the flow, just update the list.

Also, for better performance, especially as the list grows, it’s a good idea to index the Department column.

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Sajeda_Sultana

Was this reply helpful? Yes No