cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luigi312
New Member

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

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?

16 REPLIES 16
Meneghino
Community Champion
Community Champion

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/

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
Community Champion
Community Champion

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

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

 

Thanks for your time and patience.

Meneghino
Community Champion
Community Champion

It can be done, please Skype me to discuss.

Skype is baizini

Meneghino
Community Champion
Community Champion

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

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

Hi @Luigi312 and @Meneghino,

 

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

Hoping that conversation was helpful,

 

-AT

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

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

 

Thanks! 

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

 

 

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!

 

flow.png

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
Not applicable

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

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
Not applicable

Please post the picture of the steps applied.

Anonymous
Not applicable

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Users online (2,648)