Group BY in Flow to send email of aggregated data

Like (2) Share

Report

Posted on 6 Nov 2019 09:55:33 by livingstonep

354

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

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

The fields are:

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

Categories:

Business Process Flows

I have the same question (0)

All responses (9)

Answers (1)

manuelstgomes 6,625 on 06 Nov 2019 at 10:22:58

Like (2)

Report

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

Was this reply helpful? Yes No
v-bacao-msft on 14 Nov 2019 at 05:48:27

Like (1)

Report

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,

Was this reply helpful? Yes No
Verified answer

livingstonep 354 on 19 Nov 2019 at 11:46:31

Like (5)

Report

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:
then a Data Operations SELECT step:
configured like this:
then we used an Append to Array Variable step with a UNION expression to return the Aggregated data:
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:
Then we use Set Variable to extract the current Volunteer Name and Volunteer Manager values:
and use those values for a Get Items step to retrieve the records that have that Volunteer Manager:

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

Was this reply helpful? Yes No
khjami 3 on 06 Aug 2020 at 09:08:33

Like (0)

Report

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

Was this reply helpful? Yes No
jukow 17 on 25 Mar 2021 at 07:42:01

Like (0)

Report

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

Hi, I am trying to build that logic, however, I get an error in the Append to Array Variable. How is created the Initialize Variable?

"The value '[]' of type 'Array' cannot be appended to the variable 'ResponsibleEmail' of type 'Array'. The action type 'AppendToArrayVariable' only supports values of types 'Float, Integer, String, Boolean, Object'.

Was this reply helpful? Yes No
krk_2021 8 on 16 Apr 2021 at 00:59:47

Like (0)

Report

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

Hi @livingstonep

Your post is very interesting and i am trying to build something similar.
Firstly, I have a sharepoint list with 100 lines and there may be 10 different managers assigned across these lines.

How can i group my sharepoint list, like the way you have done ? Can you guide me here ?

Once i group, then i can use your procedures to send them email.

Thanks

Was this reply helpful? Yes No
riteshbti 8 on 09 Jul 2022 at 11:00:44

Like (0)

Report

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

@livingstonep where you have initialized variable named "Unique Names"?
Can you please elaborate more as I have same requirement but stuck on selecting Variable Name as its not coming by default?

Was this reply helpful? Yes No
riteshbti 8 on 09 Jul 2022 at 11:03:44

Like (0)

Report

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

@krk_2021 Did you get solution? I have similar requirement. Can you please help?PFA

Was this reply helpful? Yes No
chadwick_stokes 13 on 12 Jan 2023 at 00:45:45

Like (0)

Report

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

This video walks you through this Group By scenario and includes Sum & Count features in case that helps.

https://www.youtube.com/watch?v=SQ5tQkeuUfo&t

Was this reply helpful? Yes No