Skip to main content

Notifications

Power Automate - General Discussion
Answered

multiple choice form

(0) ShareShare
ReportReport
Posted on by

How would one go about grabbing the approvers from a department list if they selected multiple departments? I have a form with multiple-choice options for affected departments and was wondering if it's a way to query the department list for multiple selections. I am following this video here, https://www.youtube.com/watch?v=GyoD0DD_fW0&list=PLTyFh-qDKAiFUGTtwSwFT79Y7_jq_Aipe 

PDT2017_0-1651261615267.png

 

 

PDT2017_1-1651261615324.png

 

 

Categories:
  • Re: multiple choice form

    If the approval matrix has multiple emails to route, how do you craft the expression?

    outputs('Get_items_-_match_department_to_matrix')?['body/value'][0]?['HODApprovers'][0]?['Email'][1]?['Email']

     

    I have tried doing this and it didn't work.. or putting "[0]?['Email']" doesn't work either.

     

     
      {
        "@odata.etag""\"1\"",
        "ItemInternalId""1",
        "ID"1,
        "Title""CS",
        "DSCApprovers": [
          {
            "@odata.type""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims""i:0#.f|membership|coder@outlook.com",
            "DisplayName""coder",
            "Email""coder@outlook.com",
            "Picture""https://----=coder@outlook.com",
            "Department""IT",
            "JobTitle""Manager"
          },
          {
            "@odata.type""#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            "Claims""i:0#.f|membership|coder2@globalpsa.com",
            "DisplayName""coder2",
            "Email""coder2@globalpsa.com",
            "Picture""https://----=coder2@outlook.com",
            "Department""IT",
            "JobTitle""Analyst"
          }
  • Re: multiple choice form

    Hi Rhia,

     

    Could you explain the code and expression that you did for the Variable departments?

     

    split(replace(replace(replace(outputs('Get_response_details')?['body/r3cbfa311bdbc434190f4570ebd8b8f6c'],'","','|'), '["',''), '"]',''),'|')

     

    Why do you have so many replace and what does each comma and pipe do? Sorry if i take a longer time to understand this.


    Thank you!

  • Rhiassuring Profile Picture
    Rhiassuring 8,688 on at
    Re: multiple choice form

    That gave you the email you needed!? That's weird... but hey, whatever works 🙂 

  • PDT2017 Profile Picture
    PDT2017 on at
    Re: multiple choice form

    I was able to get it using the hardcoded (Single text) emails using this 

    outputs('Pull_PDT_Departments_List_(Get_items)')?['body/value']?[0]?['field_4'] instead of the people column! Not sure why I couldn't get it working the other way but thanks for your help! 

  • PDT2017 Profile Picture
    PDT2017 on at
    Re: multiple choice form

    I originally had it as department/approver, then I noticed you had just approver on yours so I changed it to that. 

    PDT2017_0-1651499726040.png

     

  • Rhiassuring Profile Picture
    Rhiassuring 8,688 on at
    Re: multiple choice form

    So based on what you have above, your Append to String Variable should beeeeee...

     

    ....wait. x002f is encoding for a / .. is your field name Department / Approver? What is the current display name and internal name of your Department Approver column?
     
    (You will definitely need to rename the ['Approver'] part of your string to whatever the name of your column is - it looks like it's Department / Approver but that seems weird to me)
  • PDT2017 Profile Picture
    PDT2017 on at
    Re: multiple choice form

    OMG! I would have never pieced that together! Thank you! I've almost got it working just stuck on the append to variable part. I keep getting this response. 

     

    PDT2017_0-1651460400637.png

    PDT2017_1-1651460565395.png

    I tried using the Department_x002f_Approver and changing my department list to match yours but still no luck. I know you said don't thank you just yet but THANK YOU FOR GETTING ME THIS FAR!! 

  • Verified answer
    Rhiassuring Profile Picture
    Rhiassuring 8,688 on at
    Re: multiple choice form

    Hmm, kind of, but not really. You need to retrieve the email that's for the chosen departments, then put all those emails together into a string, and plop that into your Approval task. Let's take a look...

    First, I have my ApprovalMatrix where I identify which approver is associated with each department. 

    Rhiassuring_0-1651451271218.png

     

    (Ignore that I'm the only person -- I am the only person that exists in my tenant haha.) 

    Next I'm going to create my MS Form and create a trigger on new MS Form response.

    Here's what my form looks like: 

     

    Rhiassuring_1-1651451440735.png

    This solution will work for any number of departments, whether it's 2 or 300. 

    Here's how I start the flow:

     

    Rhiassuring_2-1651451536223.png

     

    Now, to get into our department needs.

    First, I'm initializing two variables. One holds our departments, the other holds our string of approver emails.

     

    Rhiassuring_3-1651452312399.png

     

    In the first one, notice I have an expression. That expression is splitting up the departments into an array by identifying the characters between them, replacing them with either a blank or a pipe, and then splitting where ever the pipe is.

     

    split(replace(replace(replace(outputs('Get_response_details')?['body/r3cbfa311bdbc434190f4570ebd8b8f6c'],'","','|'), '["',''), '"]',''),'|')

    The part that I've made orange above is just the dynamic content from the form. 
     
    Rhiassuring_4-1651452452272.png


    Next, we're going to create an "Apply to Each" and point it at our "arrDepartments" array. We're going to loop through our departments!

    Rhiassuring_5-1651452611845.png


    The first action I drop in is the Get Items action, since we want to say "Hey SharePoint, please return the item where the Title is equal to this Department" (in my approval matrix, the title is the department.) 

     

    Rhiassuring_6-1651452653080.png

     

     

    "Current Item" is a piece of dynamic content available in our Apply to Each, and it automatically picks up the next selected department per loop.

     

    Next, still in our Apply to Each loop, we're going to use the "Append to string variable" action. A little bit of a formula here that, I'll be honest, I'm still learning the format of.. but it works! (This is all tested out btw.) 

    Rhiassuring_7-1651452722862.png

     

    The expression shown here looks like this: 

     

    outputs('Get_items_-_match_department_to_matrix')?['body/value'][0]?['Approver']['Email']
     
    It's saying "look at the first item returned from Get Items, find the approver, and then within the approver properties, please give me the email." If you update the part I've made orange to match whatever you've named your Get Items action, you should be good to copy / paste. (For example, if you left it as Get Items, you'd just have outputs('Get_items') and carry on with the rest of the formula.
     
    PLEASE NOTE: There is a semi-colon right after the expression has been entered -be sure to add it after your expression!
     
    Annnnnd that's it!  Now - on the ‼️OUTSIDE‼️OF YOUR APPLY TO EACH ... you can plop the 'txtApproverEmails" into your task "To" or email "To" - whatever you need. If you want to try it out first to see if you've got it down, try putting a compose outside of your loop and dropping your variable in, like this:

    Rhiassuring_8-1651452890359.pngRhiassuring_9-1651452979919.png

     

     

    Here's the whole thing, top to bottom: 

    Rhiassuring_10-1651453049743.png

     

    This should get you where you need to be - let me know if you have any issues.


    Cheers,

     

    Rhia

     

     

  • PDT2017 Profile Picture
    PDT2017 on at
  • PDT2017 Profile Picture
    PDT2017 on at
    Re: multiple choice form

    Hi Rhia,

     

    The column is just setup as single text right now. Based upon the selection of eyed departments they would be sent an approval to respond regarding a participation request so it would need to be delivered all at once. Your list is accurate as far as department. Thanks for the assistance! 

    Jasmine 

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

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,691

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 65,019

Leaderboard