cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RyanFlow
Level: Powered On

Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

Hello everyone! I have been using Flow lately and have really been liking it. I have a problem though and was hoping for some help. I have a less advanced workaround for now, but ideally this is how I think it *should* work to be effective. I am new to SharePoint and Flow, so I apologize if I use any incorrect lingo.

 

I have a reference SharePoint item list with email addressess of people responsible for certain countries and it is on the level of the individual country. Each item/row contains an email and a country as seen below. This is going to be a master list which undergoes changes and will be sortable with different views for the "responsible person", region, email etc.... This cannot be stored within flow, since people not familiar with flow will be managing the list.

Country List.pngCountry List SharePoint 1

Column "Email" is a person/group column and contains the emails of people in the company network.

 

So i am having trouble with getting the Flow to do two things. 

First, go through the "Email" list and pull all unique emails into an array This array will be used for two purposes. First, it will send out an email to all email addresses whenever the flow is triggered (telling them I need something from them). The second thing is it will create an item on a Second Sharepoint, which is focused on the email recipient. One item per email address, like this (I apolgize that the data doesn't overlap):Second SharePoint Format.pngSharePoint 2 - By Email Recipient

To be clear, I only need help getting the unique email addresses into the array. The flow is already set up to email from the array and create items on the second SharePoint site. 

 

The second thing I am having trouble with is having the countries associated with an email address get pulled into one SharePoint field (Like Latin America in the second screenshot). It is important that these countries are pulled at the time the flow is triggered so its also a snapshot in time of the countries the email address is reponsible for.  In the event that a particular email address picks up responsibility for other countries for a period of time, this will capture that. This second SharePoint site will basically allow the person in charge of the email address to indicate that they did their job for the countries they are responsible for (in that last column).  The SharePoint list item will be created based on the values in the array above (the email addresses), and then pull all countries from the first SharePoint. Perhaps this could be done by adding items into a variable using IF statements? Such as create an item from the first object in the array, and then for each row in the first SharePoint if the email matches the object in the array, put the value (the country) into a string array? Then repeat with object 2 etc...  I have about 40 email addresses and only 10 or so have multiple countries. 

 

If this second part isn't possible, I will still be greatful if anyone can still help with the first. Please inquire if I left anything out. I'm open to all suggestions! Thank you very much ahead of time!

-Ryan

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RyanFlow
Level: Powered On

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

Ok, so I had some time this weekend and solved my own problem. I'll post here how I did it in case anyone else is experiencing the same thing.


Problem#1 - Having an array take only unique items.

Spoiler

This is solved by using a 'union' command, which returns a single array with all the elements that are passed through it. If there are two objects with the same name in the final output, the last object with that name appears in the final object. Therefore, if you make the union go over the same array twice, you get unique values.

Set up like this:

Problem1.jpgProblem 1

Problem 2: Its probably better you scroll back and read my other attempts to explain this. Basically, I have a list with one email and one country per item. Most emails have more than one country associated with them. So now that I have problem 1 solved, how do I create a new item on a SharePoint where it shows one email and then all of the countries associated to it?

Spoiler
This is much more complicated, but this works from my input SP and gives me my desired output, all which were explained in previous posts.

Problem 2a.jpgProblem 2aWe have a get items (list of countries SP), initialize a string variable, and then have some apply to each loops. Expanding the loops shows:
Problem 2b.jpgProblem 2bI'll explain the "email email" variable first. I have a people/group column in the SharePoint named "email". So its saying take the email of the people/group in the "email" column. Other options of what I can take here are email claims, email department, email name etc....
The apply to each 3, takes each item (value) and says "Does the Unique email address" (from the apply to each 2), match the email email of the item in currently in the apply to each 2? If so, that means that the countries in this item are relevant and need to be appended to our string variable. So then we have it appeneded in the string variable, with a little extra coding to put each country on a new line (the concat formula) rather than next to each other on the same line. Putting a comma after the dynamic country variable also works and gives you a list of countries separated by comma's.

Problem 2c.jpgPicture 2cFinal stretch! After the if-condition, we have a "create item". The item is composed of the "current item" in the apply to each 2, which is the unique email address. "Recipient claims" I believe just means that it is all of the information stored for a given people/group (the email, department, name etc...). Then we put the "associated country string" string we just created in. The field in SP is a multi line manual text field to support one country per line in the item. Finally, without the last "set variable" there with a blank space in it, the flow just adds the new countries on the previous email addresses countries. This erases the variable so its ready for the next unique email address.

Problem solved.

I hope that makes sense! I will try to answer any questions if people have them.

 

 

7 REPLIES 7
jake
Level: Powered On

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

If you want to grab all the email addresses from that first list, and then write them all to an array for use elsewhere in the Flow, have you tried:

 

1. Initialize an array variable

2. Retrieve all list items from List 1 using an unfilted Get Items action

3. Loop through each of the results with an Apply to Each action

4. Append the email value to the array variable you created in step one (action: Append to Array Variable)

5. Outside of the loop, create a Send an Email (V2) action

6. Insert the array variable in the recipients field. You will probably need to use a Split expression

RyanFlow
Level: Powered On

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

Hi Jake, thank you very much for responding!

 

I have tried this and it will not work. Your solution only works if email addresses are not repeated in the list, hence why I'm trying to only grab UNIQUE email addresses. If one shows up 10 times, I need to only capture it once in the array.

 

This is also true for problem #2, where I use the array of unique email addresses to create items on a second SharePoint. If I grab all email addresses, then I'll create an item for each time an email address appears rather than only one item for each (unique) email address. I need to use the information in the first screen shot to create the table in the second image.

 

On a side note, for step 6 in your answer, you don't need a split expression for the email. Simply putting the array variable followed by a semi-colon is sufficient to have each email address populate the "to" field in an email.

 

I understand this is not a trivial thing to accomplish, I have already tried many things and have researched a lot on the internet (mostly from this forum). I'm really hoping someone here will be able to come up with a feasible solution!

jake
Level: Powered On

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

Can you just run an If statement before every Append to Array action?

Revised Steps:

 

1. Initialize an array variable

2. Retrieve all list items from List 1 using an unfilted Get Items action

3. Loop through each of the results with an Apply to Each action

4. Check if the current email is already contained within the array using an If condition

5. If not... Append the email value to the array variable...

6. Outside of the loop, create a Send an Email (V2) action

7. Insert the array variable in the recipients field. You will probably need to use a Split expression

v-lin-msft
Level 10

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

Hi @RyanFlow,

 

According to your description of requirement 1, I can give you the following flow configuration reference:

 

Step 1. Initialize two variables, one is "Email", type is String ,the other is "HTML Table "and type is  Array, the former is used to store the value of the Email field, the latter is used to configure the data in the Html table.Annotation 2019-05-07 182105.png

 

 

 

Step 2.Use Append to string variable action to store the value of Rebate Type field contained in each item.

  Then configure the fields that need to be displayed in the Html table in Append to array variable action.

  After each item is traversed, need to empty the String variable "Email".

Annotation 2019-05-07 182157.png

 

Step 3. the Array variable is configured in Create HTML table action and sent as the body of the message.

Annotation 2019-05-07 182314.png

 

Email recipients will receive messages like the following:

Annotation 2019-05-08 091742.png

 

According to your description of requirement 2, do you want to create a flow to automate storage the countries filed in a SharePoint list into the other SharePoint list, and classify them into multiple region?

 

Could you please describe more clearly what you need to implement? Such as step1, step2, step3, etc. That way, we can more clearly and quickly capture the functionality you want.

 

Best Regards,

Community Support Team _ Lin Tu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

RyanFlow
Level: Powered On

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

Jake, sounds like it would work, but I am not sure how to set it up. If you could show me an example of exactly how to set it up in flow I'd appreciate it.

 

Lin Tu, thank you very much for your detailed response! I am not familiar with HTML tables and some of the steps in your description I have never done. I will spend some time today playing around with flow trying your suggestion. In response to you asking me what I am looking for with step 2, I will try to provide a more clear answer of the overall plan.

 

I have a SharePoint item list (named SP1) with one item per country, along with an associated email address who is responsible for that country. The "Email" column in SP1 is the People/Group field type in SharePoint. The "Country" item is Single line of text

 

 

 

SP1.jpgSharePoint 1

 

Step 1/Problem 1: Create an array of all unique email addresses. In this example, the array would have 3 components, ANZ, China, and Central Europe. They will be of the people/group type. Lets call it UniqueArray.

 

Current Workaround to Step 1:

 

Spoiler

This is how I am currently doing this, I created a different SharePoint (called Workaround sharepoint) and it is a custom list where I manually put in each email once. This gets rid fo the "unique" hurdle I'm trying to overcome. It is set it up as follows:

  • Get items - workaround sharepoint site
  • Initialize variable - UniqueArray
  • Apply to each
    • Get item - from workaround sharepoint site
    • Append to array variable - Name: UniqueArray - Value: Email Email (basically the email value of the People/Group item in the SharePoint).

Step 2: Send an email to each address in the UniqueArray Array. This is set up as follows, and works perfectly fine (I redacted the array name since its called something else in my flow). In this example, the email would be sent to 3 individuals:

 

Email.jpgEmail

Step 3/Problem 2: Create one item for each unique email address from SP1 (I assume this will utilize the UniqueArray variable) on a second SharePoint custom list (called SP2), and in that item have a list of the countries associated to that email in one field as shown below. I am guessing the field will be "Multiple lines of text". In this example, it is 3 email addresses, two of which are associated to more than one country. Please compare this to the first SS above of SP1. The highlighted boxes show how the countries associated to that email are all in one item.

 

SharePoint2.jpgSharePoint 2

My current workaround to step 3/problem 2:

 

Spoiler

I again utilize the workaround SharePoint where I have already manually copied/pasted the list of unique email addresses.  I have also manually copied the list of countries associated to each email in a column (type: Multiple lines of text). So this information is all manually complied ahead of time in the workaround SharePoint and ready to be used, and is basically in a format where I can directly pull it to populate SP2. Here is a screenshot:

 

Create Item SP2.jpgCreate Item SP2

 

 

I think this might require a new variable to append the countries to for each unique email address prior to creating the item in SP2. Another comment is currently with the workarounds, the "send email" and "create item in SharePoint" are completely independent from each other. While they both get their data (emails/countires) from the same master list, the people I send the email to may not all need an item creation on SP2 (i'll use conditions to pick/choose). So I'd like to keep these processes independent if possible - they can pull from the same variables of course, I just can't have the email depend on the SharePoint item creation or vice versa.

 

Again, thanks a lot ahead of time! I realize I'm asking for a lot, but I have read some very clever and complex answers to other questions here and an hoping for the same! If any additional information or clarification is needed, please just ask.

 

-Ryan

 

 

 

 

 

 

 

RyanFlow
Level: Powered On

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

Ok, so I had some time this weekend and solved my own problem. I'll post here how I did it in case anyone else is experiencing the same thing.


Problem#1 - Having an array take only unique items.

Spoiler

This is solved by using a 'union' command, which returns a single array with all the elements that are passed through it. If there are two objects with the same name in the final output, the last object with that name appears in the final object. Therefore, if you make the union go over the same array twice, you get unique values.

Set up like this:

Problem1.jpgProblem 1

Problem 2: Its probably better you scroll back and read my other attempts to explain this. Basically, I have a list with one email and one country per item. Most emails have more than one country associated with them. So now that I have problem 1 solved, how do I create a new item on a SharePoint where it shows one email and then all of the countries associated to it?

Spoiler
This is much more complicated, but this works from my input SP and gives me my desired output, all which were explained in previous posts.

Problem 2a.jpgProblem 2aWe have a get items (list of countries SP), initialize a string variable, and then have some apply to each loops. Expanding the loops shows:
Problem 2b.jpgProblem 2bI'll explain the "email email" variable first. I have a people/group column in the SharePoint named "email". So its saying take the email of the people/group in the "email" column. Other options of what I can take here are email claims, email department, email name etc....
The apply to each 3, takes each item (value) and says "Does the Unique email address" (from the apply to each 2), match the email email of the item in currently in the apply to each 2? If so, that means that the countries in this item are relevant and need to be appended to our string variable. So then we have it appeneded in the string variable, with a little extra coding to put each country on a new line (the concat formula) rather than next to each other on the same line. Putting a comma after the dynamic country variable also works and gives you a list of countries separated by comma's.

Problem 2c.jpgPicture 2cFinal stretch! After the if-condition, we have a "create item". The item is composed of the "current item" in the apply to each 2, which is the unique email address. "Recipient claims" I believe just means that it is all of the information stored for a given people/group (the email, department, name etc...). Then we put the "associated country string" string we just created in. The field in SP is a multi line manual text field to support one country per line in the item. Finally, without the last "set variable" there with a blank space in it, the flow just adds the new countries on the previous email addresses countries. This erases the variable so its ready for the next unique email address.

Problem solved.

I hope that makes sense! I will try to answer any questions if people have them.

 

 

jake
Level: Powered On

Re: Having an array take unique values (email addresses) from a SharePoint list and create an item on a second SharePoint custom list (which contains information from the first SharePoint list).

My head hurts... Smiley Tongue


Glad you got it sorted. 

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 137 members 4,512 guests
Please welcome our newest community members: