Flow which looks at another list for matching information, then sends an email to a person depending on the match.

[jimitodd]

Hi all..

We have two lists, a main list and a users list.

The users list contains name, email and school.

When a person fills out the form in the main list, I would like to lookup the school name, match it with the school name on the users list and send an email to the addresses on the users list. 

Also there is generally two people assigned to a school both would need the alert.

Basically so the admin staff for that school get an alert to say a new form has been submitted for their school.

[ChristianAbata]

hi @jimitodd  what you need to do is an alert?

[jimitodd]

Sounded so promising.

I get the error when creating alerts.

"You cannot create alerts for lists where users can only read their own items."

This need to be the case as there's confidential info in the forms.

[jimitodd]

I've had a quick look at alerts on other lists.

How would I do a lookup and match.

[ChristianAbata]

@jimitodd  could you share an screanhot about what you are trying to do?

[jimitodd]

@ChristianAbata 

Some more context:

We have created a Custom SharePoint Form on PowerApps on our SharePoint List (List1).

We have Item Level Permissions set up for external users (Schools) on our List1. This was the only way to prevent them seeing what other schools were submitting (As it stands Folders and Folder Level permissions do not work on SharePoint Lists ).

On List2 it details the School/s for which that email address can submit on behalf of (using Lookups). i.e. A person can submit on behalf of more than 1 school and also a school may have more than one user submitting for them.

To counter the issue that a school may have more than 1 person submitting but can't see what the other person from that school has submitted, we would like to create a flow that:

• when a New Item is created, it looks at the School in List1
• We would like every user on List2 with that School to receive an email with some of the fields from the list on relating to the newly created item

Hope this makes sense (thought it was better than any screenshot I could provide!)

[v-bacao-msft · Accepted solution]

Hi @jimitodd ,

What info would the user fill in the main list? School Name or User Name? And what type of this field?

I assume that users will fill their name and school name in the form. And the type of these fields is single line of text. Maybe you could refer to the following method to configure Flow.

Please take a try and feel free to let us know if you have any questions.

To be better, please provide more details about both lists and the relation between these two lists.

Best Regards,

[jimitodd]

@v-bacao-msft 

Thanks the main list has a single line of text column for the school name.

The form looks at the user list to see which school the person filling out the for is allowed to use.

I tried something similar to your solution, I can't remember what error I got.

I'll have another go later.

Cheers

[jimitodd]

@v-bacao-msft 

Hi Barry.

Both lists have single line of text fields for school and email address.

the only dynamic options I get are value from the get items and a random choice field from the when a new item is created section.

I can't see how I can choose the emails assigned to the relevant school to send to in apply to each 2 and outlook.

[v-bacao-msft]

Hi @jimitodd ,

Could you provide the screenshot of the Flow configuration so that we could provide proper suggestions?

If they are all single line of text fields, you don’t need to use Apply to each 2.

You could search Dynamic content in search box according to the name of fields. Like:

Best Regards,

[jimitodd]

Thanks @v-bacao-msft 

When typing in the name of any field I get no results found.

[v-bacao-msft · Accepted solution]

Hi @jimitodd ,

As mentioned in my previous reply, you don’t need to add Apply to each to 2.

You could configure Send email action directly and select the dynamic content output from Get items action.

Please take a try again.

Best Regards,

[jimitodd]

@v-bacao-msft @ChristianAbata 

Thanks Both.

Got there with a Combined effort.