cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Collector
Kudo Collector

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
Highlighted
Kudo Collector
Kudo Collector

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

4 REPLIES 4
Highlighted
Super User II
Super User II

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

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.
Highlighted
Kudo Collector
Kudo Collector

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

Highlighted
Regular Visitor

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

Hi,

 

Just wanted to say thank you for the post and the solution. Found it very helpful.

 

Thanks

 

Kabir

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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!

Top Solution Authors
Top Kudoed Authors
Users online (7,768)