top of page
  • Writer's pictureEd G.

Group a List for a Summary Using Power Automate

This request comes up a few times each week where folks have a list of tasks or items assigned to a user (or some other field), and they want to send a regular summary of open things to each respective user/field. Typically, we start building the thing and end up with a single eMail being sent for each item which is less-than-ideal. If you've bumped into this, chances are you have your trigger, then an action to get your list of items, and then you have a Send eMail step (or some other action) inside an Apply to Each loop.

 

The List


For my example data source, I am using a SharePoint list but this technique could really be applied to anything, as long as the results coming back are in an array.

For the first demo, we want to send a regular summary to each person in the eMail list that would only include their items. Lisa should get an email with boat and scooter, while Bob would get plane, ship, and motorbike.

 

The Build


For my trigger, I am simply using a manual trigger followed by the SharePoint "Get Items" to get the above list.

After that, we will want to narrow down to just a list of the unique email addresses.


To do this, follow the Get Items with a "Select" action and in the From part, put the 'value' from the "Get Items", and then in the map, put "eMail" (without the quotes) and where it says "Enter value" add the dynamic value from the SharePoint list for the eMail addresses  (Special note here that there are some field names that SharePoint uses inherently behind-the-scenes, naming a column 'Email' might be problematic later, but worked ok here...maybe because of my casing "eMail").


This will produce a list with as many rows as you have items, but will only be the email address column.  To get just the unique email addresses, add a "Compose" action and use the expression builder with the Union() function to self-reference...this will produce a list of 3 email addresses in our example.   Here's my expression (I renamed my Select to "Get Mails" and the Compose action to "UniqueEmail").

union(body('Get_eMails'),body('Get_eMails'))    

Next, we want to cycle through each of those unique email addresses to use them as a filter on your Get Items results.  Add an "Apply to Each" loop, and put the output of the above Compose in the "Select an Output" box.


Inside the apply to each loop now, we will add a "Filter Array" action and put the Value from your "Get Items" in the From box.


For the criteria, put the dynamic value "eMail" from your "Get Items" on the left, select "is equal to" and then this expression in the right: 

items('Apply_to_each')?['eMail'] 

This says 'get me all of the rows that match whatever email from our list of unique emails, we are focusing on right now'.  The Apply to each will cycle through each one.


Next, I added a step to "Create an HTML Table" from the body of the Filter Array.


And finally, you can use a "Send eMail" using items('Apply_to_each')?['eMail'] in the "To:" bit, and then the HTML table output as the body.

For added sparkle, check out April Dunnam's article,
Formatting HTML Tables in Flow
 

The Curveball: Grouping on a Lookup Field in SharePoint


Sometimes, I get so enthusiastic about quickly solving an issue that I miss something important in the scope. With this one, I had missed that the grouping column was a lookup from another list..but just a few minor adjustments and we can pivot. Here's the same list with the added column:

This adds another layer for us to dig when we get our list of items and create our unique list as we can see from the output below:

 

Making the Changes


The first step we want to adjust is our "Select" action up at the top so that we are grabbing the values from the owner column. If we hover over the old field, we can see the expression behind it and adjust accordingly:

By getting rid of the eMail dynamic value, and replacing it with our own expression, we can grab the email addresses of the Owners:

item()?['Owner']?['Value']


Since everything else is referencing previous steps, we only need to use the same expression on the left side of the "Filter Array" action to complete the pivot.

This will result in two separate tables being sent because our list only had two owners.


I liked this one because it comes up quite a bit and there are plenty of different nuances that might affect how the problem is approached.


If you'd like to see how a Business User and a Developer collaborate on Power Platform solutions, be sure to check out Ashley Rogers' new channel on YouTube. Here's our introduction to the series, Everyday Automation:


If you're just getting started in Microsoft Power Automate and are looking for a quick guided-learning session, find my Micro-Job on Collab365. We can chat about what is getting in the way of your flow, and I can point you in the right direction.




11,273 views13 comments

13 comentarios


Punith Gowda
Punith Gowda
21 jun 2023

I followed same steps triggered multiple emails? send email inside loop

Me gusta

rflinn61
20 jun 2022

Good article. Still working on my final result; however, having issues with modifications to the HTML table. As you suggested in the video, not all SharePoint information is needed. I have attempted to "customize" by only selecting (3) columns but continue to get the same results. Any insight regarding this issue?

Me gusta
Vijay s
Vijay s
28 jul 2022
Contestando a

you got the solution?

Me gusta

charlottetang08
24 jun 2021

I've followed the tutorial however when I trying to send the email and using the 'items('Apply_to_each')?['eMail']' expression it doesn't seem to pick up the list of unique email addresses. I'm wondering if it has something to do with the data type of the email column - in your example is email address 'Multiple lines of text' in your sharepoint list?

Me gusta

Ray Handley
Ray Handley
16 abr 2021

All working fine... until...the email bit at the end doesn't play nice. It's sending an email for each record instead of one list. If done outside the 'Apply to each' it can't find the table info and/or creates a new 'Apply to each'. So close but the last hurdle :)

Me gusta
Ray Handley
Ray Handley
18 abr 2021
Contestando a

Thanks Ed. I followed the tutorial directly and got everything working fine except for the emailing of the table of results. I did use the 'Output' to get the unique email addresses though.


Me gusta

lx_b
21 jul 2020

Reply, i 'poked and hoped' i got it right on first guess - i put that function in the Create HTML Advanced fields header / value and it worked, thanks for the pointers!

Me gusta
bottom of page