Creating Dynamic Email Recipient List based on Form Responses from a SharePoint List

[tbrock_troc]

Hello.

I need Power Automate to generate a dynamic email recipient list based on MS Form responses using a SharePoint list as the email key. Here is an excel representation of my SharePoint List:

• Member - person-type field
• Account/Category - choice field with multiple selections enabled

With a couple of exceptions, all of the the choice options listed in the SharePoint list match the Form answer options - the answer options are compiled from both single-select and multiple-select choice questions in the Form.

What actions do I need to use to have Power Automate create a string of emails (from the person field) based on Form responses using the choice field to determine who should be included?

The Exceptions

• I have a choice option in my SharePoint list called "CC" and I would like Power Automate to create a string of emails for anyone with "CC" so that I can add the output to the CC field in an email action - this is standalone and is not triggered by any Form responses.

The second is more of a stretch goal:

• Because single-select choice is required for branching in Forms, we have a situation with Choice1, Choice2, and Choice1 & Choice2 as available answer options - if Choice1 & Choice2 is selected, is it possible to create the recipient list from people assigned Choice1 or Choice2?
  • This is a stretch goal because I think I can just add "Choice1 & Choice2" as an association in the SharePoint list to match the answer option exactly.

Please let me know if any additional details are needed.

[creativeopinion]

@tbrock_troc You need a way to cross-reference the choices in the SP list against the choices made in your MS Form. You shouldn't focus on the order output as the order of the choices in SP can also be different and you don't want to rely on always requiring the categories to be in a specific order.

Manual Trigger + Test Array

To better understand the logic you'll use, create a new flow and use a Manual trigger. We're just going to focus on returning the contacts based on the category selection. 

Add a Compose action to store an array of categories. They can be in any order. The syntax of the array is square brackets with each category between single quotes. 

Initialize a String Variable

Add an Initialize variable action to your flow. You'll use this variable to collect the email addresses.

Get SP Items

Add a Get Items action. It's always best practice to filter out your items form our SP list. In my case, I'm only going to return items where the Category isn't empty. 

Return Item Count

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test. Verify the output of the Compose action. If the Compose action isn't outputting the number of items expected, you'll need to adjust your Filter Query. If your SP List has more than 100 items, you'll need to toggle on pagination and set a threshold.

Loop through Each Category

Add an Apply to Each action to loop through each Category. You can add a Compose action inside the Apply to Each action to output the Current Item (aka current category being looped through. This action is optional, however it can help you to better understand the logic of your flow.

Filter Array

Since you've already returned the list of SP Items using the Get Items action (outside of the loop) you want to utilize the Filter Array action to filter out those items. To learn more about the Filter Array action, check out the YT Tutorial I've linked at the bottom of this post.

In the From field, insert the value dynamic content from the Get Items action. In the first value field, insert the dynamic content of your category column. Hover over the dynamic content label and take note of the text between the single quotes.

The output of the multi-choice column is an array, it needs to be converted to a string so that we can use the contains operator to check if the string for the current category that is being looped through. For that, you'll need to use an expression. 

Remove the dynamic content label from the first value field and insert an expression. Use the join() function. The join() function takes two parameters. An array and a separator.

join([array],[separator])

The array will be the category column. Use the item() function , add a question mark then the text between two quotes from earlier. In my case it's:

item()?['Category']

It'll be different in your case.

Add a Comma after the closing square bracket and insert a separator between single quotes. It can be a semi-colon, pipe, comma etc. Whichever you choose. I'll use a comma.

Change the operator to contains.

In the second value field, insert the Current Item dynamic content or in my case, (since I've used the Compose action to store the Current Item, I'll just use the output from the Compose action above.

Return Filter Array Item Count

Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow. Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the body dynamic content from the Filter Array action into the length() function.

Run a test. Verify the outputs of the Compose action. In this case the current category being looped through is Category 1. The Filter Array action has returned 7 items. When I cross-reference this number against my SP list, it shows that there are 7 SP Items assigned to Category 1. 

.... more to come.

To learn more about how to use the Filter Array action, please refer to this YT Tutorial: Are you using the Microsoft Power Automate Filter Array Action wrong?

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[tbrock_troc]

Hi @creativeopinion ... first off, thank you for the detailed response.

First note - I did not see the YT link you referenced in the post, perhaps you are including it in the follow-up message.

I successfully completed all of the steps laid out - for the most part it was successful. The final Return Filter Array Item Count step ran and completed successfully, however 3 of the 22 categories have counts off by +1 in the flow:

Internal Account

One of the categories is basically an internal account and is named after our client - we have 10 contacts that have this category however the flow is counting 11 items.

Not sure if it matters, but my SP List email key has 3 columns - Person, Category and Company. The person and category columns are noted in my original post and the company is whether the contact works for my organization or the for the client.

The "internal category" and "client name" are exactly the same - it would look something like Person1 below:

Of the 10 contacts with the internal account category, 1 also has the client name as the company - is it double counting this person?

My guess at what is happening...

When the "Client Name" option was added to the first compose step to create the array, I am assuming it picks up the "Client Name" reference everywhere it exists in the SP list. Even though the filter array step is run against the category column, the person is included because their category is also "Client Name".

EDIT - I edited my SP list and it disproved my hypothesis. See my follow-up comment.

Best Buy

I suspect the issues with the 2 other categories are related to one another and the fact we have multiple options for Best Buy:

• Best Buy Core
• Best Buy Premium
• Best Buy Core & Best Buy Premium

The flow counted one additional item for "Best Buy Core" (4 instead of 3) and "Best Buy Premium" (3 instead of 2) however calculated the correct number of items for "Best Buy Core & Best Buy Premium" (3).

This is how my SP is set up for these categories:

Best Buy Core - should be Person1, Person3 and Person4 however the flow counts 4 items

Best Buy Premium - should be Person1 and Person2 however the flow counts 3 items

Best Buy Core & Best Buy Premium - should be Person1, Person2, Person3 and the flow counts 3 items (correct)

I suspect it is including "Best Buy Core & Best Buy Premium" in both the "Best Buy Core" and "Best Buy Premium" counts due to the partial matches.

Is there a way to force the flow to recognize exact text so that "Best Buy Core & Best Buy Premium" is counted individually apart from "Best Buy Core" or "Best Buy Premium"?

Background note - the "Best Buy Core & Best Buy Premium" exists as an answer option in the Form because branching from questions with multi-select answer options is not possible in basic Forms. We had to set it up as a single-select to enable branching. I added it to my SP list as a category option because it is a unique answer option in our Form.

Thanks again!

[tbrock_troc]

Regarding the internal account - I think my initial suspicion is incorrect.

I thought that the company column was generating the extra item however I updated the client company name in the company column to an acronym that doesn't match the category at all and the flow is still counting 11 items despite there only being 10.

With that said, I realized we have the following categories with the client name referenced - one being the internal account and the other being a product category:

• Client Name
• Client Name Accessory

This now seems very similar to the Best Buy situation noted above where the flow is counting items that contain the text at all as opposed to matching exact text.

How much of this has to do with using "contain" vs "is equal to" in the filter array step?

EDIT - I changed it from "contains" to "is equal to" and test it however the flow counted 0 items.

[creativeopinion]

@tbrock_troc Apologies, I forgot to include a link to the YT Tutorial—the prev post has been updated with that info. 

Can you show a screenshot of your flow as is. When you use the is equal to operator. The values must be an exact match. Because you are taking the multi-choice selections and converting it from an array to a string, you need to use the contains operator. The equals to operator will always return no results.

Keep in mind, there is still more that has to be done. In this part of the flow the Apply to Each action is looping through each selection made to see which SP items contain that item being looped through. So although you may see a count returned, you have to remember that you will need a away to combine all SP items. 

We haven't done that yet. So if you press the NEXT button in the Apply to Each action, the numbers outputted from the Compose action should add up to the correct number of SP items. Hope I'm making sense.

[tbrock_troc]

---

@creativeopinion wrote:

@tbrock_troc Apologies, I forgot to include a link to the YT Tutorial—the prev post has been updated with that info. 

---

Thank you - I will check it out.

---

@creativeopinion wrote:

Can you show a screenshot of your flow as is. When you use the is equal to operator. The values must be an exact match. Because you are taking the multi-choice selections and converting it from an array to a string, you need to use the contains operator. The equals to operator will always return no results.

---

Yeah - I tested it out and found out quickly it did not work. After thinking about it, I suspected that it had to do with the fact we're looking for a phrase within an array and an "exact match" would ostensibly be the whole array.

Here is my flow - I am building the exact flow you have outline from scratch, just using my SP List that I had created:

---

@creativeopinion wrote:

Keep in mind, there is still more that has to be done. In this part of the flow the Apply to Each action is looping through each selection made to see which SP items contain that item being looped through. So although you may see a count returned, you have to remember that you will need a away to combine all SP items. 

---

Understood.

---

@creativeopinion wrote:

We haven't done that yet. So if you press the NEXT button in the Apply to Each action, the numbers outputted from the Compose action should add up to the correct number of SP items. Hope I'm making sense.

---

I think I understand. The numbers being output in the compose step match for 19/22 category cases so, logically, the flow is working.

I believe the issue with the 3 items has to do with the name of one category name being included in another category names resulting in an additional item being counted - the issues are happening with:

• "Best Buy Core" because this text is found in two category names:
  • Best Buy Core
  • Best Buy Core & Best Buy Premium

• "Best Buy Premium" because this text is found in two category names:
  • Best Buy Premium
  • Best Buy Core & Best Buy Premium

• The internal account (aka Client Name category) because the client name text is found in two category names:
  • Client Name
  • Client Name Accessory

The "internal account" issue can probably be solved in a basic way by editing the category and MS Form answer options to remove client name - "Accessory" instead of "Client Name Accessory".

The Best Buy situation will need to be solved for...

I included the combined "Best Buy Core & Best Buy Premium" as a category option in SP List because it is a specific (unique) answer option in my MS Form assuming I would need to have each answer option in the SP list in order to map the Form responses.

With that said, is it better for my SP List to only have the individual "Best Buy Core" and "Best Buy Premium" as category options and use the power automate to split "Best Buy Core & Best By Premium" answer option into the separate parts ("Best Buy Core" and "Best Buy Premium") when selected in the Form?

Thanks again @creativeopinion!

[creativeopinion]

@tbrock_troc Each category should be a single option. In your case, I would have:

• Best Buy Core
• Best Buy Premium

As separate choices. As long as your MS Form outputs Best Buy Core and Best Buy Premium as separate options. 

Are these assigned as categories as well? How do these selections relate to your MS Form?

• Client Name
• Client Name Accessory

[tbrock_troc]

Hi @creativeopinion - thanks for the quick response!

---

@creativeopinion wrote:

@tbrock_troc Each category should be a single option. In your case, I would have:

• Best Buy Core
• Best Buy Premium

As separate choices.

---

---

Got it.

---

@creativeopinion wrote:

As long as your MS Form outputs Best Buy Core and Best Buy Premium as separate options. 

---

My form has these 3 answer options:

• Best Buy Core
• Best Buy Premium
• Best Buy Core & Best Buy Premium

The combined option is due to limitations in basic Forms - we need to be able to trigger child questions (branching) which is not possible for question set for multi-select answer options and we need to be able to be able choose the Best Buy options either separately or together.

---

@creativeopinion wrote:

Are these assigned as categories as well? How do these selections relate to your MS Form?

• Client Name
• Client Name Accessory

---

Yes - generically, these are both assigned categories and here is how they relate to our Form:

• Client Name - this an account management answer option to the account question in our form

• Client Name Accessory - this is a product management answer option to the product category question in our form

(The form answer options and list category options use my client's company name - I am just using a generic "client name" reference for this post.)

The resulting email recipient list should include the corresponding account management recipients and the corresponding product management recipients.

I think the easiest solution here is to just edit the "Client Name Accessory" to just "Accessory" to avoid the client name from being used in more than one category.

I'll put together a flow chart to give you can get a better idea of my specific situation.

[tbrock_troc]

Here is a flow chart of our form and the questions that determine the resulting recipient list (account & product management)

• Audits can be at 1 account or multiple/all accounts

• Events are at 1 account though Best Buy has sub-divisions that impact the recipient list:
  • Best Buy Core
  • Best Buy Premium

[tbrock_troc]

Addendum - it is probably worth noting the following:

Since we do not have any child questions triggered (branched) from the account question in audits, we are able to use multi-select with just two Best Buy options:

• Audits - multi-select:
  • Best Buy Core
  • Best Buy Premium

However Events do have child questions triggered from the account question and Best Buy is the anomaly in that it has different account teams depending on if Core or Premium - so, our event account question is set up as as a single select:

• Events - single select:
  • Best Buy Core
  • Best Buy Premium
  • Best Buy Core & Best Buy Premium

Since the Events account question is a single select, does this make the output a string?

If it is a string, is it possible to 'split' the "Best Buy Core & Best Buy Premium" answer option into the two separate terms ("Best Buy Core" and "Best Buy Premium") to be added to the array search (filter?) when that option is selected?

[tbrock_troc]

I updated my SP list:

• Removed "Best Buy Core & Best Buy Premium" combined option
• Updated "Client Name Accessory" to just "Accessory"

This has resolved the count issues I was experiencing - the flow accurately counts the associations for each of the 21 categories.

Regarding the Best Buy situation and given the structure/limitations of our Form, I was thinking perhaps the following updates would help get rid of the combined "Best Buy Core & Best Buy Premium" option:

• Update Event Account question by replacing the 3 current options ("Best Buy Core", "Best Buy Premium" and "Best Buy Core & Best Buy Premium") with a single "Best Buy US" option

• Add a multi-select child question triggered by "Best Buy US" that has the following options so either or both can be selected:
  • Best Buy Core
  • Best Buy Premium

My flow in the end would just need to have some sort of conditions or parallel action trees using filters to create the recipient list from the correct question for each of the following scenarios:

• If audit, it pulls the audit account question (multi-select)

• If an event but not Best Buy US, it pulls the event account question (single-select)

• If a Best Buy US event, it pulls the child question asking if the event if Core or Premium (multi-select)

[tbrock_troc]

Hi @creativeopinion ...just wanted to ping you. I know you're super active on the forums so probably get a ton of notifications.

[tbrock_troc]

I am still struggling to connect the dots in terms of where to go.

I can't tell if I should be using a "Select" action somewhere (or where to use it) to map the SP Email Key or if I should be going from the Filter Array into an HTML table.

The closest I got was creating an HTML table and setting the string variable, but I am getting the whole person-type field contents in the HTML table and not just the emails.

Any additional assistance is greatly appreciated!

[creativeopinion]

@tbrock_troc Sorry I needed time to think about the logic. I don't believe you need any parallel branches. In your case I don't think it's necessary. You just need to collect the results of these questions into an array.

• If audit, it pulls the audit account question (multi-select)
• If an event but not Best Buy US, it pulls the event account question (single-select)
• If a Best Buy US event, it pulls the child question asking if the event if Core or Premium (multi-select)

Then you can use a Filter Array action to filter out the recipients from your SP list. Let me know if I'm making sense or if you need further clarification.

[tbrock_troc]

Hi @creativeopinion - no worries, appreciate the response. Thank you.

I would not be surprised to find out that there is a way to create the array from whichever of the pertinent questions contains information without requiring conditions. Based on my inexperience, I probably approach everything from the path of least resistance - I figured I would be able to use conditions within my flow as long as I learned how to:

• Use both single choice & multi-select choice MS form responses to create the array to filter my email key
• Pull the emails from the filtered list into a string variable separate by ';' for use in email action

So I had visualized something along the lines of this since they are more or less mutually exclusive scenarios (noting there are a lot of missing specific steps required in an actual flow) 

With all of that said, I'm actually pretty interested in learning the rest of the flow you were starting to show me - I think a lot of it aligns with my actual need (filtering SP list, taking resulting email addresses, adding to string variable) and I think the rest of it will prove to be a good learning experience.

While we contemplate the logic of final need, can you show me how to finish out the steps from the filter query onward?

My most recent half-step forward was being able to find and partially isolate the email address information, however it's still not the correct final format for an email recipient string.

I added a Select steps ahead of and behind the Filter Array step - the output from the first Select is the Filter Array input and the Filter Array output is the input to the second Select. Here is the flow:

Here is the result at the final compose step pictured - it created the following for each of the groups in my array:

{"Email":"person1@email.com"};{"Email":"person2@email.com"};{"Email":"person3@email.com"};{"Email":"person4@email.com"};{"Email":"person5@email.com"};{"Email":"person6@email.com"};{"Email":"person7@email.com"};{"Email":"person8@email.com"};{"Email":"person9@email.com"};{"Email":"person10@email.com"};{"Email":"person11@email.com"}

So, basically, I have 20 or so arrays that look like this - I need to still combine them all into a string variable without all of the extra characters.

Thanks again for your time.

[tbrock_troc]

Progress update...

I have now been able to isolate the email address data and combine all of the filtered groupings back together into a single string. However, there are two things I would like to correct:

• Removing duplicate email addresses

• I think a delimiter (';') is needed to split the last email of the prior group with the first email of the following group
  • The existing join function adds the delimiter (';') to split emails within the groupings but I don't know how to add the delimiter between the groups as they are appended to variable

The string variable output is basically:

person1@email.com; person2@email.com; person3@email.com; person4@email.com; person5@email.com; person6@email.com; person7@email.com; person8@email.com.com; person9@email.com; person10@email.com; person11@email.comperson1@email.com; person2@email.com; person3@email.com; person4@email.com; person5@email.com; person6@email.com; person7@email.com; person9@email.com; person10@email.com; person11@email.comperson12@email.com; person13@email.comperson14@email.com; person15@email.com; person16@email.comperson15@email.com; person22@email.comperson12@email.com; person21@email.com; person22@email.comperson12@email.com; person17@email.comperson16@email.comperson18@email.com; person19@email.com; person20@email.comperson18@email.com; person20@email.comperson18@email.comperson26@email.com; person24@email.com; person25@email.comperson26@email.com; person24@email.com; person25@email.comperson26@email.com; person24@email.com; person25@email.comperson26@email.com; person24@email.com; person25@email.comperson27@email.com; person30@email.com; person23@email.comperson27@email.com; person30@email.com; person23@email.comperson28@email.com; person31@email.com; person29@email.comperson28@email.com; person31@email.com; person29@email.comperson28@email.com; person31@email.com; person29@email.comperson32@email.com

The duplicates are obvious and I have bolded the instances where I believe the filtered groups were merged together without a delimiter between them.

[creativeopinion]

@tbrock_troc Instead of joining the email addresses and using an append to string variable you should use the union() function. The union() function will merge two arrays and remove any duplicates in the process. Outside of the Apply to Each action you can then use the join() function in an expression or use the Join action to convert the array of email addresses to a string. 

[tbrock_troc]

Hi @creativeopinion ... thanks for the response and sorry for the delay in mine. The past month has been crazy between travel, meetings and I lost my dog so it's been a bit difficult to get my head back in the game.

I replaced the append to string variable with the union() function and a join() outside of the 'Apply to Each' - here was the output:

["person1@email.com","person2@email.com","person3@email.com","person4@email.com","person5@email.com","person6@email.com","person7@email.com","person8@email.com","person9@email.com","person10@email.com","person11@email.com"]; ["person1@email.com","person2@email.com","person3@email.com","person4@email.com","person5@email.com","person6@email.com","person7@email.com","person9@email.com","person10@email.com","person11@email.com"]; ["person12@email.com","person13@email.com"]; ["person14@email.com","person15@email.com","person16@email.com"]; ["person15@email.com","person17@email.com"]; ["person12@email.com","person19@email.com","person17@email.com"]; ["person12@email.com","person18@email.com"]; ["person16@email.com"]; ["person20@email.com","person31@email.com","person32@email.com"]; ["person20@email.com","person32@email.com"]; ["person20@email.com"]; ["person21@email.com","person22@email.com","person23@email.com"]; ["person24@email.com","person25@email.com","person26@email.com"]; ["person27@email.com","person28@email.com","person29@email.com"]; ["person30@email.com"]

Compared to the output I had with the 'Append to String Variable', the result seems a mix of both steps forward and backward:

• Forward steps
  • Delimiter added between the groupings of emails - my prior version had concatenated the last email from one grouping with the first email of the following grouping without a delimiter

• Backward steps
  • Brackets and quotation marks in have returned to the string output / not in email-ready format

• No progress
  • Duplicate emails remain

As I understood your direction, the union() function should remove duplicates within the array however, from what it looks like, my steps just lined up the filtered groupings one after the other.

Do I need to append an array variable with the filtered groupings before using the union function?

[creativeopinion]

@tbrock_troc Can you share a screenshot of your flow in the Classic Designer? I believe you need to adjust the logic a bit. It would be helpful to see what your flow currently looks like.

[tbrock_troc]

@creativeopinion ...absolutely.

Detailed

• Manual Trigger
• Compose - Email Key Group Associations (array of associations)
• Initialize Array Variable
• Initialize String Variable
• Get SP Email Key List Items - ODATA Filter 'GroupAssociation ne null'
• Compose Key Item Count - count if items in in each association array

• Apply to each - outputs('Compose_-_Email_Key_Group_Associations')
  • Compose Current Group Associations
    • Input: 'Current Item'
  • Select Pre-Filter Map
    • Inputs: outputs('Get_Items_-_Email_Key_List')?['body/value']
    • Email: Member Email - item()?['Member/Email']
    • GroupAssociation: Group Association - item()?['GroupAssociation']
  • Filter Group Association Array
    • Inputs: body('Select_-_Pre-Filter_Map')
    • Value: join(item()?['GroupAssociation'], ';')
    • Condition: contains
    • Value: output('Compose_-_Current_Group_Association')

• Apply to Each (cont.)
  • Select Post Filter Map
    • From: body('Filter_array_-_Group_Association_Filter')
    • Email: item()?['Email']
  • Compose Recipient Count
    • Input: length(body('Filter_array_-_Group_Association_Filter')
  • Parse JSON
    • Content: body('Select_-_Post-Filter_Email_Map')

JSON Schema:

{

    "type": "array",

    "items": {

        "type": "object",

        "properties": {

            "Email": {

                "type": "string"

            }

        },

        "required": [

            "Email"

        ]

    }

}

• Apply to each (cont.)
  • Select Post Parse
    • From: body('Parse_JSON')
    • Map: item()?['Email']
  • Compose (Union)
    • Inputs: union(body('Select_-_Post_Parse'),body('Select_-_Post_Parse'))

*Note - I was unable to call up the 'Apply to Each' output in the Join action outside of the 'Apply to Each' step. I think I also had issues with the compose function so I created a Union function inside of the 'Apply to Each' and dragged it outside of the 'Apply to Each'

• Compose 2 (Union)
  • Inputs: union(outputs('Compose'),outputs('Compose'))
• Join
  • Inputs: outputs('Compise_2')
  • Join with: ;
• Send Email
  • Body of email: body('Join')

Please let me know if you'd like to see the test outputs for any of the steps.