Group a set of users per country and once done with the grouping, and an email will be sent (one email per group)

[DMDChicmode]

Hi Team,

Requirement:

1. Group all users on dataverse according to country

2. Send an email to admin , one email per group where on the names of the users will be added on the body of the email.

I tried following these 2 solutions:

How to Group By ID, Year and Month (screencast.com)

Do Group By in Microsoft Flow with two simple elegant loops — John Liu .NET

Actions:

1. List Rows - Entity/table selected from Dataverse

2. Compose

3. Select - Output

[

  {

    "Brazil": ""

  },

  {

    "Sweden": ""

  },

  {

    "Sweden": ""

  },

  {

    "Singapore": ""

  },

  {

    "Singapore": ""

  },

  {

    "Brazil": ""

  },

  {

    "Sweden": ""

  },

  {

    "Brazil": ""

  },

  {

    "Sweden": ""

  }

]

4. Compose 2 -

Output:

[

  {

    "Brazil": ""

  },

  {

    "Sweden": ""

  },

  {

    "Singapore": ""

  }

]

Problem - I have the countries but no names of the users

[ManishSolanki]

Hi @DMDChicmode 

You could check my blog that explains the logic to group items in SharePoint:

Single consolidated email to user (owner) for single select user field in SharePoint - Microsoft Pow...

I hope it will help you in designing the logic.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[DamoBird365]

Hi Drizzle,

You’ve a few requirements to achieve this, a distinct list of countries as an array, a loop for each country, which will contain at least two actions, a filter array on the dataset with condition for each country and a send email based on the filter array, you may also need an action for getting the email address.

I've a few videos demoing similar but not exactly the same. 

This is the closest I’ve got: Transform Excel Data into HTML Table and Email: Power Automate #PowerAutomate #Outlook
https://youtu.be/pwJ73jFEtDQ

Here’s another that’s similar: Send Unique Emails with HTML Table using Data from Excel #PowerAutomate
https://youtu.be/5msxh5Ux8DY

And finally one for advanced grouping/aggregation, Power Automate - Fast Data Aggregation - Group By, Sum, Count #powerautomate
https://youtu.be/z5MxbwURV68

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

[ManishSolanki · Accepted solution]

Hi Drizzle,

Here is the sample flow.

I have used a custom table with 2 columns - Name & Country (both are single line of text):

Flow:

1. Add "List rows" action to get all the rows from the custom table:

2. Next, add "Select" action to create an array of countries from the output of list rows action. Pass the value object from the previous action in 'From' parameter:

Click 'Switch Map to text mode' button and enter an expression to get the country value for each row:

Below expression needs to be added in the expression box as shown below:

item()?['crc76_country']

pls replace the actual logical name of the column in the above expression.

3. Add "Apply to each" action to loop through the output of select action. To get distinct country name, we will be using expression as shown below:

union(body('Select'),body('Select'))

Add filter array action to get the users of a country in each iteration. Pass the output value object array from the List rows action as its input and enter the query to filter the results based on the country:

Click 'Edit in advanced mode' button on the bottom & enter the below query in the textbox:

@equals(item()?['crc76_country'], items('Apply_to_each'))

 pls use actual logical name of the column in the above query.

Add "Create HTML table" action to capture the users for each country in a table. Choose 'Custom' from the dropdown for 'Columns' parameter and pass the output body of filter array as its input:

Use the below table for the map object. Both keys are entered as plain text but for value, expression needs to be added:

Key	Value
Country	item()?['crc76_country']
User(s)	item()?['crc76_name']

Pls use the actual logical name of the column in each expression.

Finally, add "Send an email" action and pass the output of create html table action in the email body to display the list of users:

Output:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[DMDChicmode]

Thanks for the help @ManishSolanki , i followed the steps however i encountered this concern.

What is your formula or expression on the subject line. Please see below for my error 

[DMDChicmode]

 good now i used the

items('Apply_to_each'))