Dynamic Email based on Form Input

[timfros1]

Hi there....new to Power Automate...

I have a MS form that I want our customers to submit.  I have a trigger that creates an item in a sharepoint list for the form response.

I then want, based on the input of the form, to send an email to one of our team members based on data from an excel table.  

For example:

Step 1 - User Submits Form

Step 2 - Data is created in Sharepoint

Step 3 - User selected ASAP as one of the form options

Step 4 - Email gets sent to ASAP team lead based on email address in the Excel Table

The reason for the excel table is ease of use for adding fields and updating points of contact.

Any help on how to create this flow would be great!  

Thanking in advance!

[Pstork1]

1) do a List Rows after you get the value from the form that you want to use as the key for picking email recipients. Use that value in a filter of the list rows.

2) take the array from the List Rows and use a Data Select to isolate the email column.  

3) use the output of the Data Select in a Concat(<<selected array>>, ';') to create a semicolon delimited list of email addresses.  Use that to address the email.

[timfros1]

Thank you for your response!  I am trying to build this now...but can you explain a little more about what the Data Select is?  

[Pstork1]

The Select Action in the Data connector lets you choose specific columns from an input array to add to an output array.  You can either map them with a field name or just the values.  In the screenshot below I'm using it to create an array of email addresses who last modified a SharePoint list item.  You click the toggle outlined in Red to switch from a name/value entry to just a value entry.

The idea is to use that to get a list of all the emails you might send to and then turn that array into a semicolon delimited list using Concat()

[timfros1]

@Pstork1 - Thank you for your help.  I believe you are helping me to get closer to the desired outputs....

My current flow is below.  POC is the point of contact, or email addresses I am working with.  I'm running into an error when trying to get the output of the select for the "To" field in the email. 

Basically the form allows requesters to select multiple areas.  I need to send an email to the leads of the areas that it affects.  I have gotten this to work with a single select, but I am hitting the wall with the multiple select.  The users email addresses are defined in the excel table from the top step.  

[Pstork1]

Two things.

1) you need to click on the link to the right in the Select. Check my last post and look for the red outline. You should see just one field, not two columns like you see in your screenshot.

2) After getting the values in an array you need to convert the array to a semicolon delimited string using a Join command. 

join(body('Select'),';')

That string goes in the To field.

[timfros1]

Hi there...I cannot get this to work at all.  The flow will run with the advice you have given me, but it doesn't output emails within the Apply to Each brackets.  It doesn't look like my array is filtering.  I want it to look at the excel document column "Module" with the Service Learning Input (from the form via Sharepoint) - and return any values that match.  I then want to grab the email addresses that are in the POC column on the excel form, and use those as the email address for the automated message.  

[Pstork1]

Since you are filtering the rows the Module column should be on the left side and the value you are comparing should be on the right. Use a Compose to look at what you are comparing to make sure they are exactly the same. Once you get the filter to work you should be able to do the rest.

[timfros1]

This is the learning process I suppose....

I am not getting anything out of the array...which is what is leading me to believe the issue is there, or I am not understanding what should be there....I have tried multiple combos.  I have been able to get this to work on a single select from the form input - but it's the multiple options, which of course is what the team really needs.

Any help is great help - and I greatly appreciate your willingness to get into this thread.

This is my entire flow...

[Pstork1]

So in the run you've highlighted the value for Module is "MB". Can you show me the row in the SharePoint list that is supposed to match that value?  Specifically the value of the column for SharePoint that is supposed to match it.

[timfros1]

Good morning.  So I've made some progress, I think.  I've been able to get the array working so it is filtering the selected options based on the form input.  However, I am still having trouble with the concoct of email addresses, as it is stuck in the "Apply to Each" brackets, and I can't get the email step to work outside of that bracket, so therefore I'm getting two emails in the address bar.  The emails are still getting sent separately, so you can't see who else is on the message.  Long and short - it sort of is working, but not as clean or efficient as I would like.

This is what I have so far in the flow....

[Pstork1]

Are the lines in the output of your filter array unique emails for each line?  Or are there duplicates?  I suspect there are duplicates and that is why you are getting multiple emails.

[timfros1]

No...they are all unique values.  This is extremely frustrating - and I appreciate your help...it just boggles my mind that this is as complicated as it is.  Honestly, do you know of a support site that is real-time and phone/meeting based?  

I know that this can be done - I'm either not getting it or it's way beyond my scope of understanding or all of the above.

This is the current state of the flow...

In trying to experiment and get this to work, I've lost this element that was the key that seemed to make it work.  I have no idea how to get it back.

Long story short - I need a flow that reads the sharepoint list values (which were generated from responses on a form), compares them to the excel values (list of depts (column A) and email addresses (column B), returns only the email addresses that match the sharepoint list values into a string that can send a notification email, and be used to create and assign a planner task to only those individuals.  If you or someone are able to really break it down to the simplest of terms, that would be great. 

My brain is fried - everything I have tried, read or been advised on has not worked the way it should...  

[Pstork1]

Could you provide a sample of what the excel file and SharePoint list look like with dummy values?  If you can provide that I can give you a full screenshot of what you want.  Right now I think there is some disconnect in what you envisage the logic to be.

[timfros1]

Thank you!  I have attached a copy of the excel file that is in my documents of the Sharepoint....The form selection that corresponds to the "Module Column" is here:

This is where those selections go into Sharepoint....

This is the flow as it currently exists:

Thank you again, I hope this can help you help me!  🙂