Send email (1 only) to every user with summary of outstanding tasks

[Micmic]

I've only ever built very basic flows thus far, so appreciate any help with this task which is a bit more complicated (for me).

I have a Sharepoint list with tasks. These tasks get picked up by various members of the team, and the tasks have various statuses, from 'Submitted' to 'Completed' and about 10 statuses in between. Some tasks might still be unassigned.

The list has more data than this, but this is basically what it looks like

Project number	Status	Submitted date	Assigned To	Project Manager
11111	Completed	01-04-2023	Alice	David
22222	Started	02-04-2023	Bob	David
33333	In progress	03-04-2023	Charlie	David
44444	Validation	04-04-2023	Alice	Emily
55555	Started	05-04-2023	Bob	Emily
66666	In progress	06-04-2023	Charlie	Emily
77777	Started	07-04-2023	Alice	Frank
88888	In progress	08-04-2023	Bob	Frank
99999	Validation	09-04-2023	Charlie	Frank
00000	Submitted	10-04-2023		George

What I am trying to accomplish is this: send a weekly email to every team member, listing all their tickets (and only their tickets) which are in progress (ie, not 'Completed').

So Alice should get 1 email with this 

Project number	Status	Submitted date	Assigned To	Project Manager
44444	Validation	04-04-2023	Alice	Emily
77777	Started	07-04-2023	Alice	Frank

Bob should get 1 email with this

Project number	Status	Submitted date	Assigned To	Project Manager
22222	Started	02-04-2023	Bob	David
55555	Started	05-04-2023	Bob	Emily
88888	In progress	08-04-2023	Bob	Frank

The only thing I've managed to do so far is this: send an individual email for each individual task (so a team member is receiving multiple emails instead of one), and each email contains the full list of all the tasks (so all emails are identical and contain the tasks for every team member, not just the person receiving the email).

I'm not having much luck with various attempts of 'Apply to Each' (which I suppose is what I would need to figure out). Not sure if some tickets being unassigned is part of the problem. (A nice bonus would be that if a task is unassigned, the email gets sent to the Project Manager instead, but I can live without that)

Will be grateful for any pointers!

[Pstork1]

The key here is to use a Select to isolate the user names into an array.  Then do a Union() of that array with itself.  The result will be an array of the unique user names in the list.  You can then do an apply to each on that array and use the current username to filter the get items array for the tasks of that user.  Then do a select on that to get the columns you want and create the HTML table and send the email.  That way you only get one email per user.

[Micmic]

Thank you so much for replying.
I'm not sure how to do most of this, so apologies in advance for the follow up questions.

I've gotten as far as doing a Union() on the array with itself to create an array of unique users (neat trick!!)

I'm stuck on the following step

You can then do an apply to each on that array and use the current username to filter the get items array for the tasks of that user.

---

I am not sure how to use the current username to filter the get items array.

I obviously can't do this (use a filter query that compares my Assignedto string from my list to my Outputs array). Any tips?

[Pstork1]

Use a Filter Array instead of going back to Get the items again.  Something like this

You'll also need a Parse JSON after the filter array to regain access to the individual fields in the array for the HTML table.

[Micmic]

Hi, sorry again, you might be overestimating my level here. You lost me at 

---

You'll also need a Parse JSON after the filter array to regain access to the individual fields in the array for the HTML table.

---

I have no idea how to complete this step or what this means...

[Pstork1]

This article explains Parse JSON and its usage fairly well. How to use Parse JSON action in Power Automate (m365princess.com)

[Adam_Milczarski]

@Pstork1 

For your filter on the Array...what Array is the ITEMS expression based on?

Thank You

[Pstork1]

The same as yours. Its the original Get items array.

[Adam_Milczarski]

Thank you...but I'm not the OP...I'm trying to do something similar using Power BI, and am having several issues (I actually posted a new topic yesterday). At this point, just trying to follow along and piece together a solution.

[Pstork1]

Then please post your question as a new question and send me a link.  You'll get better help from others if its posted as a question rather than a reply.

[Adam_Milczarski]

https://powerusers.microsoft.com/t5/Building-Flows/Power-BI-and-Power-Automate-E-Mails-with-Table-ba...