Display multiple names from name lookup in a gallery field

[HWib]

Hi there,

I have two lists in my sharepoint, and I would like to run flow so it looks up the names (multiple people) associated with each countries (multiple countries), and send individual e-mail to each name (apply to each).

The first sharepoint (SharePoint_Country_Contact) list contains list of countries and name associated with each country (one name per country).

The second sharepoint list (SharePoint_Survey) contains an information associated with a survey (survey ID) and have a multiple selection field containing the list of countries (there can be multiple countries in a survey).

When Flow is initiated in SharePoint_Survey (I have a button that user can click), it will do the following:

1. Parse out the countries from SharePoint_Survey

2. Look up the names for each country from (SharePoint_Country_Contact list)

3. Obtain the e-mail for each of the name

4. send individual e-mail to each individual

Thank you in advance for suggestion

[v-bacao-msft]

Hi @HWib ,

How is your current progress and has you completed the initial creation?

What are the current problems with your configuration?

If there are any problems, please provide details.

Best Regards,

[HWib]

hi @v-bacao-msft ,

I have not completed the initial creation, as the first thing I need to do is to parse out the field.

Currently I have the trigger as:

For a selected Item In SharePoint

Get Items

However, there are two actions that I do not know what to do (see underlined and bold):

1. Parse out the data in the field. The field is currently listed as 'Multiple Selection' in SharePoint Field. This field contains the name of multiple countries, for example: Macedonia, Thailand, S.Korea, Panama, etc. Once I can get the output of each country, I am thinking of adding these into array, which I will use as an input for the next action.
2. The next action will to look up the contact for each country (in another SharePoint list), and the output would be the names associated with the countries.

For each name (apply for each), I would like to obtain the e-mail ID, and send out an email with specific verbiage containing link to another sharepoint.

Thanks in advance!

[HWib]

Hi @v-bacao-msft 

I tried to parse the value in the country field (in this example, Albania;Singapore;Japan). using the following formula

Split(body('Get_item')?['Country'],';')

and create a new item in another SharePoint to test the output (output is ["Albania","Singapore","Japan"]).

I am thinking after the parsing out is complete, I can use update field to populate the contact for each country.

The problem when I split it, is that it takes all of the countries and create it as one item, instead of one country per item.

[HWib · Accepted solution]

I was able to figure it out. Hope this may help those who may need it later on.

I am using the steps outlined here and make modification to meet my need:

https://flow.microsoft.com/en-us/blog/advanced-flow-of-the-week-when-a-new-item-is-created-automatic...

I create Two Sharepoints. One sharepoint is where the flow is being triggered (let's call this SharePoint A), and another one to look up the countries and contact associated with each country (let's call this SharePoint B).

These are the columns in SharePoint A.

Country field is a look up field that looks into SharePoint B. see below:

I bring the ID from SharePoint B (to call the value later on), and allow multiple selection values.

Meanwhile, in SharePoint B have the followings columns:

Country Name is just a text field, whereas Country Contact is a person field that allows multiple values (I have countries that have multiple contacts).

Below is the snap shot of the Flow:

1. Trigger by when item is created (for ease of testing), I am going to change this so the user have to trigger it.

2. Initialize variable to store the array.

3. Add apply to each (for each of the country in the field), input is from step 1. Country ID

4. Get item from SharePoint B

5. For each of the output, append to variables (created on step 2). Since I only need the e-mail address, I append the e-mail address

6. Add Compose and Union (to remove any duplicate e-mail address)

For detail on removing duplicate email address refer to this post: https://powerusers.microsoft.com/t5/General-Discussion/Flow-How-to-remove-duplicate-email-addresses-...

7. Join the e-mail address together with ';' (see link on step 6)

After this I will have it send an e-mail to the recipients (not included here)

When tested, I got e-mail address associated with each country (any duplicate email addresses are removed thanks to step 6 and 7.