cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AC_Flow
Frequent Visitor

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

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    
JohnAlfy22/07/202040Incomplete
JohnAlfy15/07/202038Incomplete
JohnRoss22/07/202036Incomplete
JohnKaty22/07/202038Complete/Unsigned
second    
HelenPet15/07/202040Not Initiated
HelenPet22/07/202042Incomplete
HelenPat8/07/202044Incomplete
HelenPat24/06/202046Incomplete
1 ACCEPTED SOLUTION

Accepted Solutions

AC_Flow_2-1595828758924.png

 

 
 

All sorted now.

 

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

 

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

 

 

 

View solution in original post

6 REPLIES 6
JohnAageAnderse
Memorable Member
Memorable Member

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 🙂

Flow.AC_Flow.2.Apply loop to get unique team leads.jpg

Kind regards, John

 

 

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 CodeLead NameLead EmailStaffdateTotal_HoursStatus
1StuStu@  Alfy17/07/202032incomplete
1StuStuSusy17/07/202040incomplete
1StuStuSharon17/07/202040Missing
2SallySallyMike17/07/202040incomplete
2SallySallyKim17/07/202040incomplete
3GeorgeGeorgeHelen17/07/202040incomplete

 

flow:

 

 

 

AC_Flow_0-1595824842144.png

detail

AC_Flow_1-1595824882083.png

AC_Flow_2-1595825075000.png

AC_Flow_3-1595825155983.png

 

 

@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
Frequent Visitor

@v-bacao-msft , @edgonzales ,

 

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

 

thank you...

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_2-1595828758924.png

 

 
 

All sorted now.

 

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

 

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

 

 

 

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (2,933)