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).

[RyanFlow]

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 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):SharePoint 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

[jake]

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]

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]

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]

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.

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".

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

Email recipients will receive messages like the following:

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]

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

SharePoint 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

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.

SharePoint 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

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 · Accepted solution]

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:

Problem 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 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 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.

Picture 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]

My head hurts... 😛

Glad you got it sorted. 

[eliotcole]

Sorry to come to an old thread ... I saw the "Union" solution, and thought that it's beautifully elegant and succinct. But I couldn't get any real understanding of how that all works, and if it's how that function is intended to be used, so in my head couldn't be sure of repetitive use or out of context use.

So I thought I'd offer a solution for anyone that would like to rely purely on internal logic (the Union solution is better!! 🙂 ...) then feel free to look at the following. It's obviously longer, but I just needed to know what I was looking at, was all.

If anyone would like to make this more succinct, feel free. (I'm sure some of it can be more elegant or smooshed)

Full flow

Essentially the repetitiveArrayVAR in my flow is the data that anyone might be handling, don't take it too literally, but if you have to then assume that I've done a SELECT to achieve just that one value to check.

For each item in the repetitive data, I will filter the newly created singularArrayVAR (which is our unique values) by that value, and if the count of items in that filter is 0 then I know that I need to add the current item to the unique array.

This will work with any data, but don't copy me verbatim. I made mine using a labelled array because when you're working with data from other sources it's unlikely to just be a simple set of string values. Where I've built my array entry in the compose, build yours accordingly. If you want to go fully unlabelled it's also possible, maybe easier, even, I've placed a screenshot at the end with a run that works that way.

Going through it step by step.

1. Here's the variables, the left branch is the example data and a parse json for flow to "get" it, and the right containing the actual working stuff, you will likely not have much of the left branch since your flow will already have handled this.
   
2. Here's the setup in an individual 'Apply to each' run, the name variable picks out the current item's value, the Parse JSON of the unique array has to be here because if it's external it'll be static, the filter checks that Parse JSON for the current item, and whilst that's happening we'll build an array item (where I put "name" is what you'll change for your flow) with the current item's value in case it's needed to be added to the unique array.
   
3. The final condition to check if it exists by counting how many entries are in the filter that just happened, a zero value means that the item isn't currently in our new unique array, and thus can be added, anything else is a repeat condition.
   

That's basically it!

Here's the unlabelled version, which essentially treats the data completely without blinkers, making the flow exportable, I guess. Essentially, you're setting your parse JSON to simply check a sting array.

[Powerplatform01]

@RyanFlow 

You can watch this video to resolve your issue.Also request you to please subscribe this Power Automate  channel

https://youtu.be/HFghBAYX-oQ

[takolota]

If anyone wants to efficiently get all unique records based on set fields in a JSON array / dataset, you can try this template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Unique-Records/td-p/2191533

[Emilija10]

You are the best! 
Thank you for sharing your solution with us.