Flow Design Help: Looping through and emailing each team leader the list of staff,hours,date, timesheet status

[AC_Flow]

I am new to flow.

I am trying to loop through and email each team leader the list of staff,hours,date, timesheet status. This will be scheduled to run on Thursdays.

So far i have created below steps:

-Schedule

-SQL Power Query ( that gives 1 flat table as below + email address + employee code etc.)

Please advice how should i proceed or if there is an alternative better design? 

( Note on premise does not support native sql queries , so it was a bit painful to create in Power Query with all the relationships to filter )

E.g. John will get Blue table while Helen will get Pink

Team Lead	Staff Name	Weekdate	Weekly Hours	Timesheet Status
first
John	Alfy	22/07/2020	40	Incomplete
John	Alfy	15/07/2020	38	Incomplete
John	Ross	22/07/2020	36	Incomplete
John	Katy	22/07/2020	38	Complete/Unsigned
second
Helen	Pet	15/07/2020	40	Not Initiated
Helen	Pet	22/07/2020	42	Incomplete
Helen	Pat	8/07/2020	44	Incomplete
Helen	Pat	24/06/2020	46	Incomplete

[JohnAageAnderse]

Hello @AC_Flow 

Can I assume that your output from the SQL Power Query action will be sorted by Team lead?

Assuming that, you can then loop (Apply to each) through each record/item and use a variable to keep track of when the team lead changes.

When the team lead changes, you can then extract all records for that team lead using a Filter action, have the output converted to HTML and sent to the team lead in an email.

My test flow (part) looks like this - note that in the loop the "Body" is my "SQL Power Query" output 🙂

Kind regards, John

[AC_Flow]

thanks @JohnAageAnderse ,

Your design seems more logical/easier to digest than what i been through.

Could you please tell me why below flow would send 6 emails (1 for each row) instead of 1 for approver. (i.e. testing)

Is it something to do with not using right inputs to apply? or table/data, or my union not working 

union(variables('email'),variables('email'))

Appreciate your help...

Data: 

Lead Code	Lead Name	Lead Email	Staff	date	Total_Hours	Status
1	Stu	Stu@	Alfy	17/07/2020	32	incomplete
1	Stu	Stu	Susy	17/07/2020	40	incomplete
1	Stu	Stu	Sharon	17/07/2020	40	Missing
2	Sally	Sally	Mike	17/07/2020	40	incomplete
2	Sally	Sally	Kim	17/07/2020	40	incomplete
3	George	George	Helen	17/07/2020	40	incomplete

flow:

detail

[AC_Flow]

@JohnAageAnderse 

by the way how do you get around not sending multiple emails to same team lead? i dont see grouping/union etc...

[AC_Flow]

@v-bacao-msft , @edgonzales ,

could you please help with above? as i am going in circles... 

thank you...

[JohnAageAnderse]

Hello @AC_Flow 

In my test flow, I have a variable "TeamLead" that is used to track the current team leader's name. So in the loop, I check to see if it changes, and when it does, the flow filters all the items for the new team leader and ... The last step in there is to set the variable "TeamLead" to the new team leader's name.

Thus I avoid sending an email for every item beloning to the team lead.

Kind regards, John

[AC_Flow · Accepted solution]

I added a step to set the variable to union/find unique email .

All sorted now.

Thanks you for your help @v-bacao-msft , @edgonzales ,@JohnAageAnderse 

@JohnAageAnderse  ( I will try your approach for next project)

[AC_Flow · Accepted solution]

All sorted now.

Thanks you for your help @v-bacao-msft , @edgonzales ,@JohnAageAnderse 

@JohnAageAnderse  ( I will try your approach for next project)