Based on two questions in the form, pick an email from the SharePoint List

[mithushalk]

Need help!

I have 2 questions in the form

Q1. Choose the departments        Q2. Region
a. Marketing                                a. US
b. HR                                           b. UK
c. Finance                                    c. IND

I have a SharePoint list created like this

Teams	US	UK	IND
Marketing	marketing_us@abc.com	marketing_uk@abc.com	marketing_ind@abc.com
HR	HR_us@abc.com	HR_uk@abc.com	hr_ind@abc.com
Finance	finance_us@abc.com	finance_uk@abc.com	finance_ind@abc.com

Let's assume the responder chooses a for 1st question & b for 2nd question.

Output - Flow must recognise Marketing & UK
Based on this it must send an email to marketing_uk@abc.com

Any suggestions on this?

[ccc333ab]

I guess I'm meaning the filter result is incorrect, if you are only getting one email. From the data, what you selected from your form should have produced 3 results and I would expected to see in your apply_to_action showing 3 results (1 of 3).....but yet you only see one. 

So just want to know what that one is

[mithushalk]

As mentioned in the prev message I'm using my email address to test.

The flow ran successfully but the email wasn't delivered. 
Filter Query picked up the responses correct.
Filter Query - (substringof('US', Region) or substringof('EU', Region)) and (substringof('Client Development ', Title))

[ccc333ab]

I understand you are using your email address. However, with the apply to each, it would generate a separate email (to you) for every filter result that is found from your Get Items filter. 

This time, since you selected US/EU/Client Development, you should have gotten 2 reuslts from your filter query. But you got zero since your apply to each run 0 times. For this run, can you open up the Get Items, and click on "download" in the Outputs. And can you serach through it to see if you can find any EU or US in the results at all? I'm expecting NO, but taking this one step at a time.  

[mithushalk]

No, I didn't find them. 

But previously, I chose two options in forms one had my email address & another had a different email address. Send an email picked another email address & mine wasn't.

I believe that flow is not accepting my email address.

[ccc333ab]

I'm signing off for the night, but will check in with you tomorrow. Some ideas to check:

1. In your last selection of EU/ES/Client Development, the GetItems step should have found 2 items form the data you sent me. Its finding zero UNLESS you don't have the right value in the Apply_to_each_3 step. So maybe check to make sure the "value" you put into the "Select an output from previous step" is from GetItems. 

2. Thinking your email address isn't accepted by flow doesn't really make sense. It still should find the results and just give an error when it tries to send to the wrong email. You can actually just eliminate the email step for this testing purposes, put in a COMPOSE statemnt and use Email address as input. This step just shows what is stored in that value...

3. Maybe show me your get items step...I believe that may be the issue since its not returning the results expected. 

[mithushalk]

Before you sign off, I request you to show a demo of the steps you advise me to do. 

[ccc333ab]

Sorry I didn't catch your last message before I went to sleep. I have built up a test list, form and flow so you can see that it does work and maybe trace through to see if there are any discrepancies in your flow...I'll demo it below and give possible areas to check in your flow. 

List

I am just putting in a different "Person#" in the email address column so I can display that selecting different options will give different results. 

Form

Maybe for you, just double check to make sure the data in the form matches exactly the data in the list. 

Flow

Not going to show the whole flow as its the exact same as I posted previously, but I'll show a few extra steps past the Compose - Combined Filter action showing the results of the Get Items step. 

This apply to each loop: 

1. Loops through each "value" from the Get Items step
2. Then I am just using a compose to show each email address it finds (Email Address from the Get Items step). MAYBE remove your email step for now, and use a compose so you can just run and see the output while you troubleshoot the issue.

Test Run

And here is how the run looks: 

Form: Selecting these items on the form

I then manually stepped through the list based on those choices just to make sure I get the right results. I see that 4 email addresses should be chosen: 

Submit form, flow runs successfully and produced this: 

Output: (substringof('US', Region) or substringof('EU', Region) or substringof('Global', Region)) and (substringof('Client Development', Title) or substringof('Client Services', Title) or substringof('Business Ops', Title))

Filter Query: (substringof('US', Region) or substringof('EU', Region) or substringof('Global', Region)) and (substringof('Client Development', Title) or substringof('Client Services', Title) or substringof('Business Ops', Title))

Of note, the COMPOSE action is in a loop of 4 items. Here are the Outputs of the other 3 items: 

Which is what we had expected. 

[mithushalk]

Yes, you got that right. I will edit it the same.

Just to understand, what can I use in Send an email TO field?

[ccc333ab]

You would use the "Email Address" from your GetItems action. 

In my example, I put the "Email Address" in the Compose statement so you can see it, but you would just use that in your "To" field. 

[mithushalk]

Hi @ccc333ab 

I would like to confirm if I have done it right?

[ccc333ab]

You don't need the apply to each 6 at all. I was just using the Compose statement to show you what the values are. 

Delete that and all should be good. 

[mithushalk]

I ran the test again using my & my colleague email address but the email wasn't sent out & the flow ran successfully.
I confirm that the filter query picked by responses right.
(substringof('Global', Region) or substringof('US', Region) or substringof('EU', Region) or substringof('IND', Region)) and (substringof('Client Development ', Title))

I figured one weird issue that is flow picked email address of the departments but when I changed it to the personal address for testing purpose then I encountering this issue.

[ccc333ab]

In your Apply to each 3, can you : 

1.  Add in a Compose Statement
2. In the compose, can you select from your dynamic content the [Project Name] [Email address]
3. Run flow again, and walk through the Apply to each 3 loops and let me know: 
   • How many loops there are
   • In the compose statement, are you seeing the proper project name associated with the email address for each loop

[mithushalk]

Should I take out the Send an email outside the loop? 

[ccc333ab]

No, just add the Compose statement either above or below the Send an email, but in the same apply to each 3 loop. 

[mithushalk]

Flow ran successfully. Email wasn't sent 

[ccc333ab]

So that means you FILTER is not picking up ANY rows at all. So you NEED to make sure the values in your form EXACTLY match the values in your list. The one thing I can see that may be causing this the item I highlighted in RED. 

(substringof('Global', Region) or substringof('US', Region) or substringof('EU', Region) or substringof('IND', Region)) and (substringof('Client Development ', Title))

There is a space after Client Development. So you would either need to either: 

• Fix your form so that you don't have a space after ANY of your choices
• OF if you actually mean to have spaces after the choice, then make sure all your values in your SharePoint list also have a space after the value.

[mithushalk]

Awesome! There was a space in my form & I fixed it.

Tested the flow & the new error occurred in Get Items 

The expression "(substringof('Global', Region) or substringof('US', Region) or substringof('EU', Region) or substringof('IND', Region)) and (substringof('Client Development', Title));" is not valid. Creating query failed.
clientRequestId: a553d0d0-5feb-4c0d-8854-637f96a7b9af
serviceRequestId: a553d0d0-5feb-4c0d-8854-637f96a7b9af

[ccc333ab]

Modifying the form should have no impact on your expression, so that is very weird. You should have gotten that exact error regardless of having that space or not in your form...did you simultaneously change something in your flow as well? 

The one thing that seems odd is the ; in the error message. Could you have accidentally added that into either the query filter box, or in your compose statement...(below in red)

The expression "(substringof('Global', Region) or substringof('US', Region) or substringof('EU', Region) or substringof('IND', Region)) and (substringof('Client Development', Title));" is not valid. Creating query failed.
clientRequestId: a553d0d0-5feb-4c0d-8854-637f96a7b9af
serviceRequestId: a553d0d0-5feb-4c0d-8854-637f96a7b9af

[mithushalk]

Yes, accidentally I added. Got that corrected.

My flow ran successfully & send an email picked both email addresses.

Thank you 🙂