Use a Sharepoint list to lookup an email address based on form field entry

[co-anne]

Goal: Route the contents of a form to a team member based on the input of a form field (category). 

I had initial success using conditions and hard coding the email address in Send an email (V2). However, we have ~40 categories and I see that there's an eight-condition limitation. 

Should I use a variable? If so, is it possible to populate the variable with an email address from a SharePoint list? My list has two columns: category and email address. 

Thanks in advance for any guidance!

[creativeopinion]

@co-anne You don't need a variable or a Condition to achieve what I think you are looking for. 

Can you confirm if you are using a MS Form?

[co-anne]

Hi, @creativeopinion! Yep! We're using a form where we ask for the engineering category. We could include the email here, but users won't always know what email to select. 

[creativeopinion]

@co-anne I'm a bit unclear on what you are trying to accomplish. You say: is it possible to populate the variable with an email address from a SharePoint list

After a user fills out the form and selects an engineering category—would you like the flow to find an email address in your SP list based on the engineering category selected?

If so, is it a one-to-one match? Or a one-to-many match?

In the meantime, you might be interested in this YT Tutorial where I cover how to get MS Form Responses into SP: How to Get a Microsoft Form RSVP Response into a SharePoint List

I cover the following in this video tutorial:

✅ How to get a Microsoft Form Response into SharePoint

✅ How to get a Microsoft Form ID

✅ How to get a Microsoft Form response

✅ How to Build a Microsoft Form with Conditional Fields

✅ How to used Branching in Microsoft Forms

✅ How to format Microsoft Form multiple choice responses for a SharePoint multi-choice column

✅ How to parse a Microsoft Form multiple choice response as string of text

✅ How to convert a text response to a number

✅ How to use the Switch action

✅ How to create a custom email confirmation for a Microsoft Form submission

✅ How to Create a Custom View in a SharePoint list

✅ How to use the Compose Action

✅ How to write Power Automate Expressions

[co-anne]

Hi @creativeopinion! I got overtaken by a few urgent issues and wanted to review your tutorial before I responded - thank you for your patience with me. That tutorial is spectacular! There's more there than I know what to do with, and I'm bookmarking for future reference. 

Yep, you've got it - we want the flow to find an email address in our SP list based on the selected engineering category. We want to preserve a 1:1 relationship of email to category for scaling. 

Maybe a little more background would help...our goal is to collect team member suggestions and feedback. We want to categorize the suggestions in the form and then route a subsequent email to the proper subject matter expert (SME) for review. We have ~40 categories and almost as many SMEs. The form has four questions: 1) Category, 2) Date, 3) Feedback/suggestion (long-form text), and 4) Attachments (in case the team member wants to upload a sketch or photo, etc.)   

My thought was to create a SharePoint list with 3 columns: 1) the category, 2) SME name, and 3) SME email. And the flow would go lookup the correct SME name and email based on the category and route the email properly.

I gather a couple things from your tutorial...that it might make good sense to move the form submissions into SharePoint and use Compose rather than variable. Is this correct? And can you please advise on how to do the lookup? 

Many thanks!
Anne   






 

[creativeopinion]

@co-anne Will you be using the SP list to track the status of the Feedback/Suggestion?

[co-anne]

@creativeopinion That would be lovely! 

[creativeopinion]

@co-anne Have you set up your flow yet?

Manual Trigger

If not, please follow the tutorial starting from this section. Set up a Manual trigger and return a single response that you'll use for testing purposes as well as your SharePoint list. 

Compose Responses

The Compose actions are optional. However, for more efficient building and testing of the flow I'd recommend Composing your responses first before trying to create a SP Item in your list. 

We'll just handle the data first (we'll get to attachments later). Add one compose action for each response (Category, Date and FeedbackSuggestion). Tip: Remember to rename your actions to keep things organized!

Group Your Actions

Add a Scope action to your flow. I like using Scope actions in my flow to group actions together to keep my flow organized.

Add as many as you need in your flow. Note: You cannot nest initialize variable actions within other actions—they must be initialized in the root of the flow.

Run a test. Verify the outputs of the Compose actions. 

Get SP List

Can you tell me how your SP list with the category and email address is set up? Are you using a Choice column for the Category? Also, what type of column are you storing the email address in? Are you using a person column for that? If not, would you consider using a person column for that?

Now that your SP List is set up, Add a Get Items action to your flow to get the items from your list. Add a Filter Query.

You will need to use the internal column name of your Category column. If you aren’t sure how to get this, please refer to this section of one of my YT tutorials. Please note, the internal column name may not always be a match to the name of the Column—which is why it's important to check.

Enter eq for the equals to operator. In between single quotes. Insert the output from the Compose action with the Category. This is why it's important to name your actions—otherwise it can be very confusing when you are trying to access dynamic content.

Return Item Count (optional)

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. Confirming that you are expecting a single individual to be returned per category—or are there multiple individuals per category?

If you are expecting only one individual, the Compose action should return a 1 otherwise it'll return the number of individuals that have the Category (returned from the form response) assigned to them.

Condition Check

Add a Condition action. You'll use this condition to check if there are any items returned from the Get Items action. In the first value field, insert the outputs from the Compose action above. Change the operator to is not equal to and insert a zero into the second value field. If not, do nothing—if so, the rest of your actions can go into the Yes branch.

Send an Email

To send an email, you will need to add a Send an Email (V2) action to your flow. Whenever I'm building out a flow that includes a Send an Email or Post a Teams message action—I always insert my address into the recipient field.

Once your flow is ready to go you can replace it with the actual recipient. 

I recommend inserting the dynamic content that you'll be using for the recipient into the body of the email or teams message so you can confirm who the email/msg will be sent to. This way you can easily troubleshoot if it's incorrect.

However, because the Get Items action returns an array of items (even if it's a single item—it'll still be in an array). When you select any dynamic content from the Get Items action. Your Send an Email action will nest itself inside an Apply to Each action.

You will need to use an expression. I cover how to avoid the Apply to Each action when trying to return dynamic content from a single item array in this YT Tutorial: 3 Mistakes YOU 🫵 are Making with the Apply to Each Action in your Microsoft Power Automate Flow

Take a look at the tutorial and lmk once you're able to get the email address from your SP list.

.....more to come!

If you'd like to level up your Power Automate skills check out this video: Power Automate Beginner Tips and Tricks | 5 Things You Need to Know – Part 1

I cover the following in this video tutorial:

✅ Power Automate Beginner Tips and Tricks

✅ Why use the Manual Trigger instead of an Automated Trigger

✅ 3 Ways a Compose Action can help you build better flows

✅ How to Manually Trigger a flow with a specific Date and Time

✅ How to return a count of items

✅ How to use the top count

✅ How to send test emails

[co-anne]

Hi @creativeopinion, I'm tracking well with everything you've suggested, and my flow is testing out as expected. 🙂

Regarding the SP list, yep, for the categories, I created a column with choice = type and entered all 40-some values. 

For the email addresses of the SMEs, I currently have them in a separate list, but I can move them into this list if you think that's advisable. I also originally entered the email as a single line of text. Having now watched your tutorial, it looks to be better to use a Person column to store the SME profile if I understand correctly. I'm completely open to shifting to this way! 

Thanks so much for your continued help!

[creativeopinion]

@co-anne Great! If you edit your list in Grid View, you can copy your email addresses from the single line of text column into the Person column.

Don't forget to click on Exit grid view to save your changes. Once you have the person column set up... LMK!

[co-anne]

@creativeopinion, Done! I'm just using my email for the time being, and all the cells in the Person column (SME Profile) are filled in.

[creativeopinion]

@co-anne I've just added a few steps to the original post with the instructions!