@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:
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
