Group BY in Flow to send email of aggregated data

(2) Share

Report

Posted on by livingstonep

352

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

All responses (9)

Answers (1)

chadwick_stokes 13 on at

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
riteshbti 8 on at

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
riteshbti 8 on at

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?
krk_2021 8 on at

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
jukow 17 on at

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'.
khjami 3 on at

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
Verified answer

livingstonep 352 on at

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
v-bacao-msft on at

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,
manuelstgomes 6,625 on at

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