How to create a Daily instant flow linked to a SharePoint task list

[DavidHiscutt]

Hi,

I have been battling with this for months. I would like to achieve a daily instant flow linked to the below data to send a HTML set of data to the relevant TaskOwners (seperately) on the below list. An example of the HTML table for the one recipient "Mia

Ford" is at the bottom to give context to the scenario. I do not code, so I am looking to use Power automate without code/JOSN, instead using variables and the inbuilt flows. The idea is we have a list of tasks for financial month held in SharePoint (below table example data), we want to schedule an email at 9am to send to each 'TaskOwner' individually their own individualised HTML table with their tasks, only the 'incomplete' status tasks. 

I get so far with this then lose it with too many apply to each and sends too many messages and or the HTML table repeats tasks. 

One feature which seems tricky is that the 'TaskOwner' is a multi choice, so it is an array which needs breaking down into individuals to use in the email V2 send to - I perform some bits and it works, then I lose it - any support from the community would be appreciated. 

SharePoint list
SharePoint columns >>>	Title / renamed 'Task'	TaskOwner	TaskStatus	DueDate	NeedsToKnow	Date completed	Reviewer	TeamOwner	Period
Field type >>>	Title field renamed	Multi choice people picker	Choice	Date dd/mm/yyyy	Multi choice people picker	Date dd/mm/yyyy	Multi choice people picker	People picker	Text
Row tasks >>>	Task 1	Ben Yeo / Eva Wu / Lia Cruz	Complete	01/06/2023	Trish King / Jay Lin	01/06/2023	Trish King	Jay Lin	P04-23
	Task 2	Mia Ford / Lia Cruz	Incomplete	05/06/2023	Trish King / Jay Lin	[blank]	Trish King	Jay Lin	P04-23
	Task 3	Mai Ford	Incomplete	05/06/2023	Trish King / Jay Lin	[blank]	Trish King	Jay Lin	P04-23
	Task 4	Trish King	Incomplete	05/06/2023	Jay Lin / Kim Ray	[blank]	Jay Lin	Jay Lin	P04-23
	Task 5	Ben Yeo / Eva Wu	Incomplete	05/06/2023	Trish King / Jay Lin	[blank]	Trish King	Jay Lin	P04-23
	Task 6	Kay Shaw / Ben Yeo	Incomplete	05/06/2023	Trish King / Jay Lin	[blank]	Trish King	Jay Lin	P04-23
	Task 7	Trish King	Incomplete	07/06/2023	Trish King / Jay Lin	[blank]	Jay Lin	Jay Lin	P04-23
	Task 8	Mia Ford / Lia Cruz	Incomplete	07/06/2023	Trish King / Jay Lin	[blank]	Trish King	Jay Lin	P04-23
	Task 9	Lia Cruz / Mia Ford	Incomplete	07/06/2023	Trish King / Jay Lin	[blank]	Trish King	Jay Lin	P04-23
	Task 10	May Day / Lia Cruz	Incomplete	15/06/2023	Trish King / Jay Lin	[blank]	Trish King	Jay Lin	P04-23
	Task 11	Trish King	Incomplete	15/06/2023	Jay Lin / Kim Ray	[blank]	Jay Lin	Jay Lin	P04-23

Example desired output for Mia Ford in a HTML table to be embedded in an "Email V2" sent to Mia Ford only:

Task	TaskOwner	TaskStatus	DueDate	Period
Task 2	Mia Ford	Incomplete	05/06/2023	P04-23
Task 3	Mia Ford	Incomplete	05/06/2023	P04-23
Task 8	Mia Ford	Incomplete	07/06/2023	P04-23
Task 9	Mia Ford	Incomplete	07/06/2023	P04-23

[lbendlin]

I guess you mean a scheduled flow.

Have a list of all people (as an array variable)  and then iterate through each person and check if they are part of the TaskOwner array.

You don't need JSON for this but you may need to learn a bit about PowerFX.

[DavidHiscutt]

Thanks, @lbendlin , if I shared snippets of my power automate would you be able to flag where I have gone wrong. This is my first post so not sure how detailed we can go

[lbendlin]

give it a try.  You could also export your steps to the clipboard (put them in a scope).

[DavidHiscutt]

Hi @lbendlin, thank you for your support. As I snippet the steps I'm not surprised I can't get this to run, if you can make any comments it would be great otherwise I will scrap and restart and just focus on the single taskowner (splitting the people picker). One issue I seem to have is using a 'modified' trigger I can rapidly initialise the TaskOwner 'email' attribute, but the reason for my convaluted steps are because using the 'recurrence' starting point I can't call on the 'email' attribute early in the process - bit weird and frustrating my progress on this one. But of course I don't want a 'modify trigger' I want to say ultimately ... here are your open tasks in a table. 

As you can see, all of my steps are no code which does protract things, but I need to try to keep as 'user' understandable as possible. 

Snippets:

union(variables('VarTaskOwnerEmail'),variables('VarTaskOwnerEmail'))

[lbendlin]

This one looks iffy

You would only need one loop, and an array comparison in it.

[DavidHiscutt]

Great, thanks @lbendlin   - will look at that one and re-run. I find apply to each are created very quickly / automatically by the system and you need to manually pull them back out so very likely I missed this one. 

[DavidHiscutt]

Hi @lbendlin - this is the apply to each I mentioned previously. I tried to edit the flow and create a single apply to each the flow automatically creates the 'value' input again. This is what was confusing me from the outset, for some reason the field Task Owner Email (which is the email attribute of the Task owner column) does not appear in the 'dynamic content' so the first item i try to use it the system forces that 'value' piece before it makes the email attribute / task owner email field to be available in the dynamic content. When I look at other dynamic content they all operate as normal. Have you experienced this type of thing before / any ideas, could I need to create a dummy branch further up the flow to initialise it prior to then trying to use it further down the flow, seems long winded. Any ideas welcome and thanks for the help so far @lbendlin 

[DavidHiscutt]

Is anyone having this same issue so it might be good to explore further ?