Group SQL Rows based on Field and send email

[EMSSS]

I have a SQL database with ID, Name, Unit Number, Line manager email, Senior line manager email, Date Inputted

I have set up a flow that sends an email notification  when there are new rows with todays date (this flow will run daily) 

I would like to however group the emails based on the unit number so that if there is multiple rows (with same unit number) the line manager and senior line manager only receive ONE email detailing all the information about the different units

How do I go about doing this?

[grantjenkins]

Hopefully this is what you're looking for. I'm assuming you already have the data from today (new rows), and just want to understand how to group the data by Unit Number.

See full flow below. I'll go into each of the actions.

Data is a Compose that contains some sample data. You would already have this from your SQL table - I just used a Compose for this example as I don't have a SQL table setup.

Select extracts out the Unit Number, Line manager email, and Senior line manager email. I'm assuming the emails would be the same across each Unit Number. The expressions used are:

//You would use the field names from your SQL output

item()?['Unit Number']
item()?['Line manager email']
item()?['Senior line manager email']

HTML table style is a Compose that contains some CSS for styling the HTML table, so it looks nicer in the email.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Apply to each iterates over each unique Unit Number using the following expression that removes duplicates.

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

Filter array will return only the items where the Unit Number is equal to the current Unit Number we are iterating over. It uses the output from Data, and uses the following expressions:

//You would use the field names from your SQL output for the first one and the name you specified in the Select for the second one

item()?['Unit Number']
items('Apply_to_each')?['Unit Number']

Create HTML table uses the output from Filter array and the following expressions for each of the fields.

//You would use the field names from your SQL output

item()?['ID']
item()?['Name']
item()?['Unit Number']
item()?['Date Inputted']

Send an email uses the output from HTML table style and Create HTML table and uses the following expressions to get the emails (which need to be separated by a semicolon).

//You would need to separate these with a semicolon in the To field

item()?['Line manager email']
item()?['Senior line manager email']

In this example, two emails would be sent out to the respective Units.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[EMSSS]

Thank you so much this was great!! Just one thing to note is different unit numbers will have different line managers/senior managers - does this affect the format I will need to do for the flow?

Thanks again 🙂

[grantjenkins]

It should work as it is. Can you try it out and let me know if any issues, etc.