cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
3R-Automation
Helper I
Helper I

Grouping by Employee and Weekly Hours

I want to build an MS Flow which would organize the List data by Employee and then tabulate the Employees monthly total hours and post the hours in an HTML table, then process the next Employee.  

I already have a list that captures the Employee hours each week and captures the total hours for that week.  

Might there be an example which would show how to group by Employee and then further group by total hours?  I am assuming this will be two Apply to Each action which first passes in the employee as a set into the next.  But I am not clear how to organize this into HTML as we have 5 employees.  All the data has to appear in an email as the accountant likes that format. 

1 ACCEPTED SOLUTION

Accepted Solutions
jinivthakkar
Resident Rockstar
Resident Rockstar

@3R-Automation this is what I have tried

jinivthakkar_0-1620832866719.png

jinivthakkar_1-1620832896937.png

jinivthakkar_2-1620832931655.png

In first Get items I have filter which is hard coded as highlighted 

jinivthakkar_3-1620832979221.png

then I create table and in second Get items I have filter which is hard coded with Jane then I create html table and then pass to Email action. You can repeat the same for other employees as well.

You can work on formatting of email. 

 

This is not the best approach but considering this is only for 5 employees I won't invest a lot of time/efforts. Also for total I have another flow (when item is created/updated) which would update total so in the above flow I can just extract the value of total.

The total at the bottom of table for Monday, Tues etc is not possible.

 

Hope this helps.

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

4 REPLIES 4
jinivthakkar
Resident Rockstar
Resident Rockstar

@3R-Automation I tried this not sure about your list schema, I have a week column so it can be used to filter week or you can also use month values from Jan to Dec and it is needed so we don't hit the threshold limit and get only limited records

jinivthakkar_1-1620385914110.png

 

jinivthakkar_0-1620385881269.png

jinivthakkar_2-1620386035136.png

jinivthakkar_3-1620386057591.png

jinivthakkar_4-1620386086414.png

You can also format and make the table more beautiful but I leave that to you, this is just high level approach you can make changes as per your use case.

 

--------------------------------------------------------------------------------

If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Hours.jpg

 

 

The inserted image is what the accountant would like the report to be organized like.  The employee hours data is in a list and does contain the hours for each day and the calculated total of the hours for the week.  What I need to do is to build a flow which passes in the Start Date and End Date and then groups the data by Employee, organizes the Start and End dates so they correspond by the Weeks in the month.  And then calculate the totals for each column based on the employee.  If I had hours during the month of January for employees:  Bob, John and Clint then the script should group the hours for Bob, John and Clint to do the calculations on totals and organize the hours into tables with the totals under each block.  

jinivthakkar
Resident Rockstar
Resident Rockstar

@3R-Automation this is what I have tried

jinivthakkar_0-1620832866719.png

jinivthakkar_1-1620832896937.png

jinivthakkar_2-1620832931655.png

In first Get items I have filter which is hard coded as highlighted 

jinivthakkar_3-1620832979221.png

then I create table and in second Get items I have filter which is hard coded with Jane then I create html table and then pass to Email action. You can repeat the same for other employees as well.

You can work on formatting of email. 

 

This is not the best approach but considering this is only for 5 employees I won't invest a lot of time/efforts. Also for total I have another flow (when item is created/updated) which would update total so in the above flow I can just extract the value of total.

The total at the bottom of table for Monday, Tues etc is not possible.

 

Hope this helps.

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

Very nice, I never thought about using hard-coded employee names.  I was off on a tangent using JSON to collect all the hours and then figuring out how I would total.  But I could technically have an array of the employees and then have a loop that steps through that array and builds the HTML one at a time and emails them.  The Accountant is OK with having the employee hours sent one at a time, and that might be easier for her to tabulate.  I already can do a calculation of the total hours to produce a total.  

 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Users online (58,657)