How to do a foreach on unique values from a SP list

[Luigi312]

Hello,

I have managed to retrieve the content of a SP list, which looks something like this:

ID | Customer Name | Assigned person

1 | Customer 1 | Bob

2 | Customer 2| Bob

3 | Customer 3 | Jack

4 | Customer 4 | Jack

What I would like to do is send a single email (every two week for example) to each "Assigned person" with the list of all the opportunities assigned to him. Eg.:

Bob will receive an email with the information regarind Customer 1 & Customer 2.

Jack will receive an email with the information regarding Customer 3 & 4.

I have tried the following flow:

Recurrence > Get Items > Create HTML Table > Send Email

This works fine, but I'd like to send only the relevant information to the right people in "Assigned person".

Any thoughts?

[Meneghino]

Hi @Luigi312

I would suggest nested apply to each loops:

1) An outer loop for each assigned person

2) An inner loop that threads through the opportunities for that person and composes a single email text

Please let me know if you need details

PS Here is a reference:

https://flow.microsoft.com/es-es/blog/nested-apply-to-each/

[Luigi312]

Thank you @Meneghino. 

The issue is: I can't do an Apply to each with other values than the Sharepoint lists items, or the entire Output of a "Select". I can't do an Apply to each on single items. Eg. : Apply to each "Leader" 

So I've ended up with this, but this doesn't work ...

[Meneghino]

OK, there are two possible alternative approaches:

1. Add another 'Get items' step which will get the items grouped by Leader, so that you can have a list of leaders that you can iterate through for the outer loop. Please note that the send email should be outside the inner loop but inside the outer loop, since you want to send only one email per leader
2. Use only one 'Apply to each' loop, but use a variable to keep track of the current Leader.  If the leader changes, then you send the email and reinitialise the variables.  In this case your 'Get items' step will need to get the items ordered by Leader

Please let me know if anything is unclear

[Luigi312]

I couldn't make it work. Tried everything. I am giving up.

Thanks for your time and patience.

[Meneghino]

It can be done, please Skype me to discuss.

Skype is baizini

[Meneghino]

PS What type of column is customer? (text, choice or lookup)

What type of column is Assigned person? (person, text, choice, or lookup)

[anhthuch4ng]

Hi @Luigi312 and @Meneghino,

We you two able to meet on Skype to discuss a solution to this?

Hoping that conversation was helpful,

-AT

[Meneghino]

Yes, we did, thank you.  It was resolved, I believe.  Flow is a very good product, much more robust than PowerApps.

[TwoPuttTommy]

Can you post the solution on this board?  I'm having the same problem and can't figure this out.

Thanks! 

[Meneghino]

Basically:

1. Initialise Email body variable with nothing
2. Get the items
3. Initialise the OldAddressee variable with the first item
4. For each of the items
   1. If this item=last item or CurrentAddressee<>OldAddressee
      1. then
         1. If this item=last item add current item content to Email body else nothing
         2. Send email to OldAddressee with Email message
         3. Clear Email body variable
         4. Set OldAddressee=CurrentAddressee
      2. else nothing
   2. Add current item content to Email body

[TwoPuttTommy]

Thank you.  This makes sense.

Question... I may be making this more complicated than needed but I was hoping to queue up items so that each addressee only received one email with a list of their items.  The problem I'm stuck on is illustrated in this figure... specifically, how do I reference items stored in the respective arrays?  Can I use the item() reference for one of the arrays (e.g. in this case the Output from the Compose step (#2)?  If so, how do I reference the second array, i.e. the Body from the filter array (#1)?

Any/all insights greatly appreciated.

Thanks!

[Meneghino]

That is what my proposed solution does.  In step 4.1.1.1 you need to concatenate (in an HTML if you want) the current item content to the existing content.  An email is only sent once the addressee changes or the item is the last in the group.

[Anonymous]

Sorry @Meneghino I am still not too sure how I could build this logic in Flow. Will it be possible to take a screen shot of the Flow to show the building blocks?

Thank you

[thuff]

this i extremely helpful. thank you 

Quick question related to checking if the current item is the last item is that done by doing  condition and checking if 

item() is equal to last(body('Get_items'))

[Anonymous]

Please post the picture of the steps applied.

[Anonymous]

Can you please post the picture of the steps, it will make it easy for me as this is very new for me.