Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Power Platform Community / Forums / Building Flows / Approval flow that rou...
Building Flows
Unanswered

Approval flow that routes to a specific person based on a multiple choice MS Forms response + Excel

(1) ShareShare
ReportReport
Posted on by Wendo 8
 
I have a functioning approval flow that adds a row to Excel with MS Form responses. I need to change the flow to route the approval to a specific approver based on a multiple choice MS Form response (org). I have read so many posts, but I am stuck and not able to piece all of the bits of knowledge together to come up with my solution. I am new to Power Automate and appreciate your assistance.
 
Scenario: User fills out an MS Form and selects their Org (Org1 or Org2) and the approval flow needs to route to the approver for the specific org selected, add a new row to an Excel file, then email the user with the final approval. Not complicated, but I’m stuck. See pics
 
How do I route the approval to a specific person based on the form response for Org? I read about adding a ‘Switch Action’ and tried it, but I get errors at the ‘add row to table’ step. Not sure where I’m going wrong. I also read about using an MS List to ‘look up’ the approvers name and email address. Creating the List is easy, but I don’t know how to use that List in the flow to pull the correct approver for the selected Org to then route the approval. Which option is easier? Is there a better option? Would love the steps on how to do this.
 
I like the idea of the Switch Action because the step to ‘add row to table’ needs to add the row to a different worksheet (table) based on which org was selected. If Org1 is selected the approval routes to the approver for Org1 and adds a row to the Excel file on worksheet Org1 with a corresponding table name Org1. Same for Org2.
Thanks so much. Ciao.
Categories:
Business Process Flows
  • Wendo Profile Picture
    Wendo 8 on at
    Approval flow that routes to a specific person based on a multiple choice MS Forms response + Excel
    Thanks @FLMike. I really appreciate your time and  I like your suggestion. I am new to Power Automate and don't have the experience to put your idea into place without a practical example. I had read about using a List as an Approval Matrix, but couldn't get it to work. I went back to the Switch/Case because it's easy and I only have two approvers. I would like to learn how to use a List for the approvers though. The other reason I liked the Switch/Case is because the data submitted on the Form will get added to a specific sheet in Excel that corresponds to the Org selected on the Form. Someone selects Org1, fills out the form, approval gets routed to Org1 Approver AND writes the data to Table Org1 in worksheet Org1. Same for Org 2. I have established the Tables in Excel, but it's not working. It only writes to Org1 Table and Worksheet. It seems like it can't find Org2 Table and Worksheet. I couldn't find anything about whether or not a Flow can write to different worksheets in the same workbook (using Table names of course). Can I have more than one table and more than one worksheet in Excel that a Flow can write to based on the org selected?

    Here's your suggestion and where I need help.
    I have the List set up the way you suggested. 3 Columns: Org; ApproverName; ApproverEmail
     
    You: When the form is submitted you do NOT use a Case, instead you do a Get Items from SharePoint, with a Filter on the Org Column where the value eq that choice from the Form it would be like this
    Org eq 'DynamicPropertyOftheChoice' and yes you need the single quotes around it
    So this is simply doing the Filtering you need to only get the first row if its Org1
     
    Me: Not sure where to do the 'filter on the org column'. Are you saying on the List itself filter the org column and then would I need to save that as a view? I see a Filter Query field on the Get Items action. That doesn't seem right.
     
     
    You: Once you get these back, the Approver Column will be a Dynamic Property you can use :-) so its easy
     
    Me: How do I retrieve the ApproverEmail from the matrix? I use the Outlook action to send an email with details of the request and a link to 'approve/reject'. Instead of putting their hard-coded email address in the TO: field I would put the Dynamic Property for their email address. I love this. I get the concept. I don't know how to do it.
     
    You: And if you need more than one approver, than all you do is make the string semi colon concatentated like this
    michael@t.com; t@t.com; etc@etc.com
     
    Me: In the Assigned To field in the Approval action I have hard-coded the two approvers in there separated by a semi-colon. Is this what you're referring to? I don't think you are based on the next section.
     
    You: Now, this returns back the Email to the Approver (or Approvers) and now you are Dynamically deciding who to send it to :-) without a hardcoded choice.
     
    Me: Not sure where the concatenated approvers should go and I don't know how to put the dynamic email address of the approver in the Outlook To: field.
  • FLMike Profile Picture
    FLMike 26,719 on at
    Approval flow that routes to a specific person based on a multiple choice MS Forms response + Excel
    HI,
     
    Here is what I recommend. and try to follow lol as I can get everywhere sometimes.
     
    Let's say you have a List in SharePoint.
    In there you have a mapping like this
     
    For every choice they have in your form you have a row that is like
     
    Org1, approver email, and anything else you need

    Org           Approver              AnythingElse
    Org1        michael@t.com     stuff
    Org2        t@t.com                stuff 

    When the form is submitted you do NOT use a Case, instead you do a Get Items from SharePoint, with a Filter on the Org Column where the value eq that choice from the Form it would be like this
     
    Org eq 'DynamicPropertyOftheChoice' and yes you need the single quotes around it
     
    So this is simply doing the Filtering you need to only get the first row if its Org1

    Once you get these back, the Approver Column will be a Dynamic Property you can use :-) so its easy
    And if you need more than one approver, than all you do is make the string semi colon concatentated like this

    michael@t.com; t@t.com; etc@etc.com

    Now, this returns back the Email to the Approver (or Approvers) and now you are Dynamically deciding who to send it to :-) without a hardcoded choice.
     
    If this helps please mark as the answer.
     
     
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

September 2024 Newsletter…

September 2024 Community Newsletter…

Community Update Sept 16…

Power Platform Community Update…

Welcome to the new Power Platform Community!…

We are excited to announce our new Copilot Cookbook Gallery in the Community…

more Community News

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 141,191

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,419

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans