cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ali_Nawaz
Level: Powered On

Aggregate gallery records

Hi PowrUsers, 

I am trying to make a time sheet PowerApp. 
The scenarios is, there can be multiple Check-ins and check-outs in one day and they will be displayed in a gallery. 
(Image attached)
TimesheetScreen.png

 

Now what I am looking to achieve in it is, when I click on submit there will be another screen which will show only one record for one day. And in that record there will be a sum of Total Hours and Hours worked against one date. 

Like in the current screen there are 2 records for 11/21/2019. When I hit submit there will be only one record for 11/21/2019 and it will show me a sum of both the records of 11/21/2019 in present screen. 

Looking forward to responses on this. 

Best Regards,
Ali Nawaz

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Aggregate gallery records

@Ali_Nawaz 

Assuming the data in your gallery Items looks like this.

ClearCollect(currentTimesheet,
{Date:Date(2019,11,20),'Hours Worked':7,'Total Hours':6.5},
{Date:Date(2019,11,21),'Hours Worked':0.2,'Total Hours':0.2},
{Date:Date(2019,11,21),'Hours Worked':0.3,'Total Hours':0.3});

 

You could perform a grouping by using some collections.  The mySum collection would have the aggregated results of 'Total Hours' and 'Hours Worked'.

ClearCollect(
    myGrouped,
    GroupBy(Gallery1.AllItems,"Date","GroupedColumn")
);
ClearCollect(
    mySum,
    AddColumns(
        myGrouped,
        "Hours Worked",Sum(GroupedColumn,'Hours Worked'),
        "Total Hours",Sum(GroupedColumn,'Total Hours')
    )
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Aggregate gallery records

@Ali_Nawaz 

Assuming the data in your gallery Items looks like this.

ClearCollect(currentTimesheet,
{Date:Date(2019,11,20),'Hours Worked':7,'Total Hours':6.5},
{Date:Date(2019,11,21),'Hours Worked':0.2,'Total Hours':0.2},
{Date:Date(2019,11,21),'Hours Worked':0.3,'Total Hours':0.3});

 

You could perform a grouping by using some collections.  The mySum collection would have the aggregated results of 'Total Hours' and 'Hours Worked'.

ClearCollect(
    myGrouped,
    GroupBy(Gallery1.AllItems,"Date","GroupedColumn")
);
ClearCollect(
    mySum,
    AddColumns(
        myGrouped,
        "Hours Worked",Sum(GroupedColumn,'Hours Worked'),
        "Total Hours",Sum(GroupedColumn,'Total Hours')
    )
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Ali_Nawaz
Level: Powered On

Re: Aggregate gallery records

@mdevaney  That worked great !!

Is there any chance we can incorporate one more logic in this ??
I also want to calculate the difference b/w clock-out time of 11/21/2019  and  clock-in time of 11/21/2019  and then show that sum of those differences in single row as we have calculated the Sum of total hours through your last provided function.

Timesheet2.png

The idea is to find out the time difference b/w preceding and  succeeding  clock-ins and clock-outs and showing their sum in one single record respective of their date. 

If we can work this  out, it would mean a great deal to us. 

Thanks for your support and assistance. 

Best Regards,
Ali Nawaz


Super User
Super User

Re: Aggregate gallery records

@Ali_Nawaz 

Can you please show what the end result should look like?  Table format would be great.

Ali_Nawaz
Level: Powered On

Re: Aggregate gallery records

Let me explain this in detail. 
We have multiple check-ins and checks out per day. 
As shown in this image below. 

Screenshot_1.png

what I want is when I click on Review Timesheet button there will be another screen opened with a gallery which will have one record per date.
The multiple check-ins and check-outs will be consolidated into one record for that particular date. 

Like in this image 

Screenshot_2.png

Now on the second screen, In the hours worked column I am using the formula you last sent. And it is working fine to group multiple record into one on the basis of single date and giving me a sum of all the hours worked of that one particular date. 

The next step is to calculate break hours as well. 
Breaks hours is the difference between clock-out of first record of date 11/1/2019 and clock-in of second record of date 11/1/2019. 

Clock-out time of 11/1/2019 = 12:00:00 
Immediate next Clock-in time of 11/1/2019 = 14:00:00

Difference b/w these two time stamps = 2:00:00 Hours ( Break Hours) 

So what I require is, the sum of difference of all clock-outs and clock-ins of same date records consolidated into one record like hours worked.
If there are multiple clock-ins and clock-outs the sum of the differences of those clock-ins and outs

It means I have to check what was the difference between when a worker last clocked-out and again clocked-in on the same date. 


This is how how i am capturing the break he took between his shifts. 

And after that i will be deducting his break from his hours worked to calculate Total Hours of that day.


I hope this would make sense. 

I am counting on the response I get from the community a great deal.
You guys have been life savers 👍

 

Best Regards,
Ali Nawaz

Super User
Super User

Re: Aggregate gallery records

@Ali_Nawaz 

Yeah, this post makes total sense.  Now I understand that you want to calculate break hours (or non-productive hours) for each workday.  This will take me some time to consider because I will have to build a little prototype.  I will need to wait until the evening to give it a try.

 

Essentially what I will try to do is create a row number INDEX column (e.g. 1,2,3,4) for each row and then use LOOKUP to reference the previous row.  If the date is the same the formula should return a value, otherwise 0, using a function such as IFERROR/ISBLANK/ISEMPTY.

 

If you want to try it for yourself I suggest you use my general method above.  But I will respond within a day or so if I can find how to do it.

Ali_Nawaz
Level: Powered On

Re: Aggregate gallery records

@mdevaney Thank you so much for your swift response. 

I'll be looking forward to your applied solution brother. 
Meanwhile I am trying this on my end as well. 

Bundle of thanks for your support. 

Best Regards, 
Ali Nawaz 

Super User
Super User

Re: Aggregate gallery records

@Ali_Nawaz 

After thinking about this problem for awhile I decided that calculating the individual break times when the user submits their timecard was not the way to go.  Instead, the break time should be calculated each time an employee punches-in.  Here's an outline of what you can do:

#1  Create a table with three columns: EmployeeID,'Employee Name', Status, 'Last Time Punched'.  Set the default value of status to "In" and 'Last Time Punched' to Blank.

#2  When a user performs a punch-out PATCH the employee record to change the Status to "Out" and the 'Last Time Punched' to Now().

#3 When the user performs a punch-in Set a variable called BreakTime equal to Now() - 'Last Time Punched Out if Year(Now) = Year('Last Time Punched Out') otherwise the value should be 0.

#4 Create a new timesheet record for the current shift and PATCH the BreakTime variable into the record.

That's the basic method.  Wish there was a more "magic" solution to calculate the break times but I feel this is a simpler approach.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

 

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,055)