cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted
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
Highlighted
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

Highlighted
Helper III
Helper III

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


Highlighted
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.

Highlighted
Helper III
Helper III

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

Highlighted
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.

Highlighted
Helper III
Helper III

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 

Highlighted
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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,258)