Automating an email with grouped data

I'm hoping someone can help me out here because i've been trying to do this for days.

I have a select_1 that spits out the following (I've edited for data protection):

{
    "body": [
        {
            "Visit name": "The Flying Unicorns",
            "Study name": "The Matrix",
            "Quantity": 50,
            "Actor name": "Keanu Reeves",
            "Actor email": "keanu.reeves@hollywood.com"
        },
        {
            "Visit name": "Time Travel Kit",
            "Study name": "Inception",
            "Quantity": 123,
            "Actor name": null,
            "Actor email": null
        },
        {
            "Visit name": "Space Pizza Delivery",
            "Study name": "Star Wars: A New Hope",
            "Quantity": 20,
            "Actor name": "Carrie Fisher",
            "Actor email": "carrie.fisher@hollywood.com"
        },
        {
            "Visit name": "The Epic Journey",
            "Study name": "The Lord of the Rings: The Fellowship of the Ring",
            "Quantity": 1,
            "Actor name": "Steve Martin",
            "Actor email": "steve.martin@hollywood.com"
        },
        {
            "Visit name": "Mysterious Ancient Artifact",
            "Study name": "Indiana Jones: Raiders of the Lost Ark",
            "Quantity": 5,
            "Actor name": "Harrison Ford",
            "Actor email": "harrison.ford@hollywood.com"
        },
        {
            "Visit name": "Wizard’s Duel",
            "Study name": "Harry Potter and the Sorcerer's Stone",
            "Quantity": 2,
            "Actor name": "Steve Martin",
            "Actor email": "steve.martin@hollywood.com"
        },
        {
            "Visit name": "Time Bomb",
            "Study name": "Mission Impossible",
            "Quantity": 2,
            "Actor name": "Tom Cruise",
            "Actor email": "tom.cruise@hollywood.com"
        },
        {
            "Visit name": "Alien Encounter",
            "Study name": "E.T. the Extra-Terrestrial",
            "Quantity": 6,
            "Actor name": "Drew Barrymore",
            "Actor email": "drew.barrymore@hollywood.com"
        },
        {
            "Visit name": "Superhero Suit Fitting",
            "Study name": "Avengers: Endgame",
            "Quantity": 3,
            "Actor name": "Chris Hemsworth",
            "Actor email": "chris.hemsworth@hollywood.com"
        },
        {
            "Visit name": "Secret Potion Ingredients",
            "Study name": "The Hunger Games",
            "Quantity": 10,
            "Actor name": "Jennifer Lawrence",
            "Actor email": "jennifer.lawrence@hollywood.com"
        },
        {
            "Visit name": "Robot Upgrade Kit",
            "Study name": "I, Robot",
            "Quantity": 5,
            "Actor name": "Will Smith",
            "Actor email": "will.smith@hollywood.com"
        },
        {
            "Visit name": "Classic Detective Work",
            "Study name": "Sherlock Holmes",
            "Quantity": 2,
            "Actor name": "Robert Downey Jr.",
            "Actor email": "robert.downeyjr@hollywood.com"
        },
        {
            "Visit name": "Revenge of the Jedi",
            "Study name": "Star Wars: Return of the Jedi",
            "Quantity": 2,
            "Actor name": "Mark Hamill",
            "Actor email": "mark.hamill@hollywood.com"
        },
        {
            "Visit name": "Fantasy World Exploration",
            "Study name": "Alice in Wonderland",
            "Quantity": 2,
            "Actor name": "Johnny Depp",
            "Actor email": "johnny.depp@hollywood.com"
        },
        {
            "Visit name": "Time Capsule Retrieval",
            "Study name": "Back to the Future",
            "Quantity": 1,
            "Actor name": "Michael J. Fox",
            "Actor email": "michael.j.fox@hollywood.com"
        },
        {
            "Visit name": "Magic Show Setup",
            "Study name": "The Prestige",
            "Quantity": 1,
            "Actor name": "Christian Bale",
            "Actor email": "christian.bale@hollywood.com"
        },
        {
            "Visit name": "Monster Investigation",
            "Study name": "Jurassic Park",
            "Quantity": 10,
            "Actor name": "Sam Neill",
            "Actor email": "sam.neill@hollywood.com"
        },
        {
            "Visit name": "Cloning Experiment",
            "Study name": "Jurassic World",
            "Quantity": 1,
            "Actor name": "Bryce Dallas Howard",
            "Actor email": "bryce.dallas@hollywood.com"
        }
    ]
}

What I need from my flow is to send a single email to each Actor per study with the list of visits associated to that study. So Steve Martin would receive 2 emails. One for Harry Potter and the sorcerer's stone (Wizard's duel x2), and one for The lord of the rings: the fellowship of the ring (The epic journey x1). I hope that makes some sense to someone.

I have limited understanding of what i'm doing with Power Automate but this is out of my reach... even with the ever enfurating Chat GPT.

This is what we've come up with. It almost works, as it does send the email, but it doesn't group them correctly. For the above example, i would receive 2 emails, both with all the information i need for both instanced (a duplicate).

This is how the flow looks (described by GPT and edited by my to correct errors i could see).

Select_1 output…

1.Initialize a Variable:
Action: Add an Initialize Variable (Initialize Variable) action.
Name: GroupedEmails
Type: Array
Value: [] (leave this blank to start with an empty array).

2.Group Visits by Study Name:
Action: Add a Select (select_3) action to transform your input data.
From: body(‘Select_1’)
Map:
Visit name: item()?['Visit name']
Study name: item()?['Study name']
Quantity: item()?['Quantity']
Study nurse email: item()?['Study nurse email']

3.Initialize a Variable:
Action: Add an Initialize Variable (Initialize Variable 1) action.
Name: currentStudy
Type: String
Value:

4.Initialize a Variable (Initialize Variable 2):
Action: Add an Initialize Variable action.
Name: currentEmail
Type: String
Value:

5.Initialize a Variable (Initialize Variable 3):
Action: Add an Initialize Variable action.
Name: currentVisits
Type: Array
Value: []

6.Apply to Each - Group Data by Study Name:
Action: Add an Apply to each action.
Select an output from previous steps: Use the output from the Select_3 action above.
onside this loop, use the following steps to create the grouped structure:

a. Set Variables:
Action: Inside the Apply to Each, add another Set Variable action.
Name: currentStudy
Value: item()?['Study name']

b. Action: Add another Set Variable (Set Variable 1) action.
Name: currentEmail
Value: item()?['Study nurse email']

c. Append Data to the Visits Array:
Action: Add another Append to array variable action.
Name: currentVisits
Value: concat('{"Visit name": "', items('Apply_to_each')?['Visit name'], '", "Quantity": ', items('Apply_to_each')?['Quantity'], '}')

d. Add Grouping Logic:
Action: Add a Condition to check if the grouped email for the current study name already exists in the GroupedEmails variable.
Condition: Use the expression:
contains(variables('GroupedEmails'), item()?['Study name']) is equal to true
If Yes: Do nothing (the email group already exists).
If No:
Add a compose action (compose_2)
json(concat('{','"Study name": "', variables('currentStudy'), '", ','"Study nurse email": "', variables('currentEmail'), '", ','"Visits": ', json(string(variables('currentVisits'))),'}'))

Action: Add a Compose (compos_3) action to create the HTML table for the email body.
Input: Use the expression:
join(variables('currentVisits'), '<br>')
This will convert the visit details into a formatted string for the email.

7.Send an Email:
Action: Add the Send an email (V2) action.
To: item()?['Study nurse email']
Subject: Use the expression:
concat(item()?['Study name'], ' stock')
Body:
Hi [Your Name], here's your stock for [Study Name].
<br><br>
<table>
<tr>
<th>Visit Name</th>
<th>Quantity</th>
</tr>
@{outputs('Compose_3')}
</table>

8. Append to array variable 2
Name: GroupedEmail
Value: outputs(‘Compose_2’)

Any help would be hugely appreciated!

Categories:

Business Process Flows

Community site session details

Automating an email with grouped data

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Kudos to the February Top 10 Community Stars!

Announcing Our 2025 Season 1 Super Users!

Leaderboard

Featured topics

Product updates

Community site session details

Automating an email with grouped data

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

Kudos to the February Top 10 Community Stars!

Announcing Our 2025 Season 1 Super Users!

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates