cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IanL
Frequent Visitor

Selecting persons from a list

I am using Power Automate in an Education setting.  I have created an MS Form for users to submit requests for pre-planned teacher absences, so that we can approve, organise cover for any lessons, notify appropriate staff for duties and activities cover, etc.  I have a flow that triggers when a new form is submitted.  The flow retrieves the data from the form and starts the approval process.  Currently, the approval process is sent just to me, so that I can test it works.  Thankfully, it has, so far!

 

The next stage of this development is to use the response to one of the questions from the form to lookup the line manager(s) from a list, so that the line manager(s) may approve the request.  I have set up a basic list (using MS Lists) with 2 columns, FacultyName (string) and LineManager (person).  It is possible for a teacher to be a member of one or more faculties, so the MS Forms has a list of these faculty names from which the user may select multiple items:

 

IanL_0-1638465699691.png

 

I believe the MS Form question results in an array of at least one or more elements, assuming at least one item has been checked on the MS Form.  I need to iterate through each of these elements, find them in the FacultyName column of the MS List and obtain the LineManager(s) associated with the matching FacultyName(s) in the list, so that I can appoint them as approvers.  With the output (at least one or more persons) I think I need to add their email address(es) to the approval step, which currently just has my email address hard-coded there for testing.

 

I am at a loss of how I might be able to do this and would welcome any advice or guidance.

Thank you for taking the time to help me.

1 ACCEPTED SOLUTION

Accepted Solutions
miguelcas
Microsoft
Microsoft

Hi @IanL 

 

Thank you for your waiting, Here are some steps to resolve your issue. I'm going to explain you the process. 

 

1.  First we need the trigger to get the new response to your form and then an action to get de details. 

 

miguelcas_1-1638547878187.png

 

2. As you said, the outcome of a multiple choice question will return an array. For example { 'text1', text2' ,... }. So we need to handle this array using the Parse Json action that will create a json from that array. This will help us to manage that data in the flow.

miguelcas_2-1638548152176.png

 As you can see, we are just getting the multiple choice question as a Content in the Parse Json action. In my case that's the question y my test form. Then just adding a variable that will contains the emails as a string to be use in the upcoming approval. 

 

3.  Add an apply to each to go through the array that has the faculties. This array is the output from the Parse Json action. 

 

miguelcas_4-1638548752989.png

 

 

Basically, what we are doing here is per each faculty in the array, we will search in your list for the row that matches. Adding in the advance settings the follow filter query will help us with that. Use Title as the column from the dropdown or Head Of Faculty if it's in the dropdown. Sometimes the Title column will not change the name internally, even if you change the name of the column.  In the filter query select from the dynamic content Item, this is from the body of the Parse Json action. 

 

4.  Once we got the row of the faculty, we need to search for the email of the person in the Person Column. Add a new apply to each within the first one with the value from the get item action as the input. Add the Append to string variable, select the variable we created and from the dynamic content search for the email from the person in the Person Column and next to that add ';' because emails need to be separate with that. 

 

miguelcas_6-1638549135324.png

 

This action will concatenate all the emails from the list in the variable. 

 

5. at the end, add the Approval and use the variable as the Assign to input. Sometime adding the variable from the dynamic content, the action will add a semicolon next to it. Delete that semicolon at the end, the variable already has it.

 

miguelcas_7-1638549415044.png

 

I hope this works for you! 

 

Thank you for using Microsoft Power Platform Communities.
Community Support Team - Miguel Castro.
If this Post helps, then please consider Accept as solution to help the other members find it more quickly.

 

View solution in original post

7 REPLIES 7
miguelcas
Microsoft
Microsoft

Hi @IanL 

 

Can you tell me if the Line Manager column has more than one email?

 

If you can share a screenshot of where it suppose to be the second step of your development it would be amazing and we need an image of your MS List 

 

I'm working on your case and I will be happy to help you. 

 

Kind Regards

IanL
Frequent Visitor

Thank you for contacting me @miguelcas .

 

Here is a screenshot of my list:

IanL_0-1638481335628.png

Faculty is a column of 'strings'.  Head of Faculty is a column of 'persons'.

 

Here is the extract from the flow:

IanL_1-1638481508462.png

 I need the email address of the person (or multiple addresses, if the teacher is a member of two or more faculties) to be available in the 'Assigned to' field. 

 

If there is more information that would be useful, please ask.  I am very grateful to you for your help.

miguelcas
Microsoft
Microsoft

Hi @IanL 

 

Thank you for the information. Just two more thing we need to know it's that you need to sent the same approval request to each head of faculty email, that means one of each can approve or both, or you need to send two or more approvals depending of the number of faculties someone submit in the form, that means each approval would be independent from the other ones. 

 

the other thing we need to know it's if the flow need to wait the approval, if yes, what we need to do then? 

IanL
Frequent Visitor

Hi @miguelcas 

 

I would like to send the same approval request to each head of faculty email (where that faculty has been chosen in the completed Form) but only one of them needs to approve.  Of course, if the teacher belongs to only one faculty, then only one email to that head of faculty and that person must complete the approval.

 

The flow should wait until approval is given before proceeding (I've used Start and Wait for an Approval - I think that's the right one?)

 

The rest of the flow seems to be working just how I want it.  I tested it by having my own email address typed directly into the 'Assigned to' field!

miguelcas
Microsoft
Microsoft

Hi @IanL 

 

Thank you for your waiting, Here are some steps to resolve your issue. I'm going to explain you the process. 

 

1.  First we need the trigger to get the new response to your form and then an action to get de details. 

 

miguelcas_1-1638547878187.png

 

2. As you said, the outcome of a multiple choice question will return an array. For example { 'text1', text2' ,... }. So we need to handle this array using the Parse Json action that will create a json from that array. This will help us to manage that data in the flow.

miguelcas_2-1638548152176.png

 As you can see, we are just getting the multiple choice question as a Content in the Parse Json action. In my case that's the question y my test form. Then just adding a variable that will contains the emails as a string to be use in the upcoming approval. 

 

3.  Add an apply to each to go through the array that has the faculties. This array is the output from the Parse Json action. 

 

miguelcas_4-1638548752989.png

 

 

Basically, what we are doing here is per each faculty in the array, we will search in your list for the row that matches. Adding in the advance settings the follow filter query will help us with that. Use Title as the column from the dropdown or Head Of Faculty if it's in the dropdown. Sometimes the Title column will not change the name internally, even if you change the name of the column.  In the filter query select from the dynamic content Item, this is from the body of the Parse Json action. 

 

4.  Once we got the row of the faculty, we need to search for the email of the person in the Person Column. Add a new apply to each within the first one with the value from the get item action as the input. Add the Append to string variable, select the variable we created and from the dynamic content search for the email from the person in the Person Column and next to that add ';' because emails need to be separate with that. 

 

miguelcas_6-1638549135324.png

 

This action will concatenate all the emails from the list in the variable. 

 

5. at the end, add the Approval and use the variable as the Assign to input. Sometime adding the variable from the dynamic content, the action will add a semicolon next to it. Delete that semicolon at the end, the variable already has it.

 

miguelcas_7-1638549415044.png

 

I hope this works for you! 

 

Thank you for using Microsoft Power Platform Communities.
Community Support Team - Miguel Castro.
If this Post helps, then please consider Accept as solution to help the other members find it more quickly.

 

View solution in original post

IanL
Frequent Visitor

Thank you once again @miguelcas,

 

I am stuck at Step 3 of your instructions above.  I have tried multiple ways of trying to resolve this, but cannot seem to find a solution.  In your screenshot, in the Filter Query for the Get Items action, you have a drop-down list to select 'Title equals Item'.  However, this is not presented to me in my version (difference between Edu and Normal version?)  What I can see is:

IanL_0-1638618328674.png

How do I either change the view (so that I can see them same as you) or create a query that has the same results as 'Title equals Item'?

 

Thank you for your continued patience and delivery of clear instructions to a novice!

IanL
Frequent Visitor

Aha!  By using the filter query in Get Items of:

IanL_1-1638661961750.png

I have been able to get it to work.  The bit I was missing was the ' ' before and after the Item!

 

I am so very grateful for your kind assistance @miguelcas

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,791)