cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
livingstonep
Level 8

Group BY in Flow to send email of aggregated data

We have a SharePoint List with a column VolunteerManager - here is a view of the list Grouped By that field:

VolunteerManagers1.PNG

 

The list consists of Volunteers and each VolunteerManager can have several Volunteers

The fields are:

VolunteersList.PNG

We need to send an email to each of the Volunteer Managers with a table of the Volunteers that they are responsible for...

 

So we need to perform a GROUP BY then loop through and send the email containing the names and contact of the Volunteers...

In SQL this would be simple, in MS Excel this would be done with a PIVOT table...

 

How can I do that in my Flow

1 ACCEPTED SOLUTION

Accepted Solutions
livingstonep
Level 8

Re: Group BY in Flow to send email of aggregated data

here is how we did the Group BY in MS Flow, grouping our records by the field VolunteerManager

First Get items:

Group BY 1.PNG

then a Data Operations SELECT step:

Group BY 2a.PNG

configured like this:

Group BY 2c.PNG

 then we used an Append to Array Variable step with a UNION expression to return the Aggregated data:

Group BY 3.PNG

Next we want to loop through the aggregated/grouped data and inside the loop retrieve all the records for each Volunteer Manager:

First we execute a PARSE JSON step:

Group BY 4.PNG

Then we use Set Variable to extract the current Volunteer Name and Volunteer Manager values:

Group BY 5.PNG

and use those values for a Get Items step to retrieve the records that have that Volunteer Manager:

Group BY 6.PNG

 

and that's it...after that loop through those records, and we append to an HTML table variable the data for that individual record, Update the item as Processed and move to the next Volunteer Manager ...

 

I hope this helps

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Group BY in Flow to send email of aggregated data

Hi @livingstonep ,

 

Bad news first. There's no automatic way to do that so you have to do it yourself using variables. Good news is that I found an article that may help you:

http://johnliu.net/blog/2018/6/do-group-by-in-flow-with-two-simple-elegant-loops

 

I haven't tried what's in the article, so if something doesn't work, let me know, and we'll try to figure this out together. 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Highlighted
Community Support Team
Community Support Team

Re: Group BY in Flow to send email of aggregated data

 

Hi @livingstonep ,

 

You can try the method mentioned in the article provided by @manuelstgomes , it is valid. I have used a similar method to provide solutions for other users.

When you configure Flow, you need to extract all the managers in the list and then remove the duplicates.

Then iterate through all the items in the list, and then according to the manager obtained above, the corresponding Volunteer is integrated and sent to the corresponding manager.

Please take a try. If you have any questions, please let us know.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
livingstonep
Level 8

Re: Group BY in Flow to send email of aggregated data

here is how we did the Group BY in MS Flow, grouping our records by the field VolunteerManager

First Get items:

Group BY 1.PNG

then a Data Operations SELECT step:

Group BY 2a.PNG

configured like this:

Group BY 2c.PNG

 then we used an Append to Array Variable step with a UNION expression to return the Aggregated data:

Group BY 3.PNG

Next we want to loop through the aggregated/grouped data and inside the loop retrieve all the records for each Volunteer Manager:

First we execute a PARSE JSON step:

Group BY 4.PNG

Then we use Set Variable to extract the current Volunteer Name and Volunteer Manager values:

Group BY 5.PNG

and use those values for a Get Items step to retrieve the records that have that Volunteer Manager:

Group BY 6.PNG

 

and that's it...after that loop through those records, and we append to an HTML table variable the data for that individual record, Update the item as Processed and move to the next Volunteer Manager ...

 

I hope this helps

View solution in original post

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

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 Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,751)