Adding Hours in Time Clock App

[Anonymous]

I am working on a Time Clock app for my employees. I am trying to figure out a way to add up the hours for each employee. Is there a simple way to do that? Right now, the data populates into a Data Table when the user and date range is selected. Is Data Table the right tool to use? Or would Gallery or something else work better? Thank you!  

[RandyHayes]

@Anonymous 

Either control would work for you as it is the data underneath of the control that you want to use to sum the values.

What I am not seeing in your picture is what you want to sum.  You have Activity Dates and the time is the same, is there a count of hours or something else needed to derive the hours?  That is what you would sum.

However in regard to DataTable vs Gallery - you will have more control over the Gallery from an interface perspective.  But, both use a table of data, and that table is what you want to sum.

I hope this is helpful for you.

[Anonymous]

@RandyHayes 

Thanks Randy! I went ahead and switched to using a Gallery (gallery seems to be more functional and user friendly to me). Here is a new screenshot of what I am working with. So it shows the Date and Time of Punches. So somehow I need to total total number of hours. Thank you.

[Anonymous]

@RandyHayes 

The other thing I would like to do is group the listings in the Gallery by Date, so I have the Date bolded then under each date is all the punches for that date, so I don't have to repeatedly list the date as in the previous screenshot.

[RandyHayes]

@Anonymous 

So, the challenge is that the items in the list do not specify working vs non-working.

There will have to be a couple of assumptions made and some formula put in place to pull this together...not a big deal, but it is not apparently obvious to begin with.

Since you are basing this off of the TimeTracker app that we either built on from before or the one that I did a video on, I see that the information is pretty much the same and I tested this out on the data I had in that sample app.

One thing that I did add to that list (was not in the video) is an ActivityDateTxt text column.  The TimeTracker app was modified to put in the Text version of the activity Date.  In the video (I believe) we put a text formatted column for the date AND time.  We need one for the Date only (for the grouping to work properly and without delegation warnings).  SO, at this point I have the following columns in my list (TimeTracker):

   Title  - (text) holds the user email address

   ActivityType - (text) holds Check In, Check Out, etc.

   ActivityDate - (date and time) holds the date and time the activity occurred

   ActivityDateTimeTxt - (text) holds the Activity date in a text string with the yyyymmddhhmm format

   ActivityDateTxt - (text) holds the Activity date in a text string with the yyyymmdd format

Setting my stage.  I have a Dropdown and two datepickers (to duplicate your scenario).

For the Dropdown (ddEmployee), I have the following formula on the Items property:

ForAll(Distinct(TimeTracker, Title),
    With({lclProf:Office365Users.UserProfileV2(Result)},
        {Name: lclProf.displayName,
         email: lclProf.mail
        }
    )
)

With that, the formula for the Items property of the Gallery becomes:

With({lclEndDate: DateTimeValue(Text(DateAdd(DatePicker7_1.SelectedDate, 1, Days), ShortDate) & " 00:00")},
    AddColumns(
        SortByColumns(
            GroupBy(
                Filter(TimeTracker, StartsWith(Title, ddEmployee.Selected.email) &&
                    ActivityDate>=DatePicker7.SelectedDate && ActivityDate<=lclEndDate
                ),
               "ActivityDateTxt",
               "_activities"
            ),
            "ActivityDateTxt"
        ),
        "_activityDateTime", DateValue(Text(First(_activities).ActivityDate, ShortDate))
    )
)

Starting from the inside...we first filter the TimeTracker list to match the employee email (the StartsWith) we also filter by the start and end date.  NOTE: the lclEndDate variable is used as it is a day adjusted date - in other words, it is really the end date selected plus one day at midnight 00:00.  That covers the entire actual selected end date.

Next, from that filter, we Group the records by the ActivityDateTxt (this is the additional column mentioned above).  That then gives us a table of grouped activity dates with a columns that contains all the matching records for that date.

Next, we sort that table by the ActivityDateTxt column.

And, finally, we add a column called _activityDateTime that contains just the Date of the first record in the grouping - this is really only for display purposes.

Now, in the Gallery, there is a label and the Text is set to : Text(ThisItem._activityDateTime, LongDate)

There is then a Nested Gallery in the Gallery (galDayActivities).  

That Gallery Items property is set to:

AddColumns(
    AddColumns(
        With({lclItems:SortByColumns(ThisItem._activities, "ActivityDate")},
            ForAll(Sequence(CountRows(lclItems)),
                With({lclItem: Last(FirstN(lclItems, Value))},
                    {actType: lclItem.ActivityType,
                     startTime: lclItem.ActivityDate,
                     endTime: Last(FirstN(lclItems, Value+1)).ActivityDate
                    }
                )
            )
        ),
        "duration", DateDiff(startTime, endTime, Minutes)
    ),
    "workTime", If(EndsWith(actType, "In"), duration, 0)
)

In this formula, we work on the _activities in the group from the parent Gallery Items formula.

First, we iterate through all of the activities (now sorted by the activity time and in the lclItems variable).

To do this we utilize the Sequence function with a sequence of numbers from 1 to the count of the activities (CountRows in the above).  Sequence gives us a nice Value column that we can use to find the end time of each activity by looking at the next activity start time.

So, ForAll of the sequence numbers, we first set a scoped local variable to the record in the list of items based on the sequence value Last(FirstN(lclItems, Value)).  Then with that we generate a record with a actType, startTime, and endTime column.  And we set as follows - the actType is just the lclItem Activity Type.  The startTime is just the Activity Date for record and then we determine the endTime by looking up the next record Activity Date - Last(FirstN(lclItems, Value+1)).ActivityDate

Finally, with that Table (ForAll always returns a Table) we then AddColumns to the table.  First we figure out the duration - just the difference in minutes between the start and end times.

Then (and we have to do this in another outer AddColumns), we calculate the workTime.  The logic - if the activity type ends with "In", then it represents a working time...in which case we use the duration from the inner add columns.  If it is not a working time, then it is just 0.

FINALLY...in the original outer Gallery (the parent gallery), there is another label and its text property is set to the following:

With({lclWrkhrs:Sum(galDayActivites.AllItems, workTime)},
    Round(lclWrkhrs/60, 0) & ":" & Text(Mod(lclWrkhrs, 60), "[$-en-US]00")
)

This gives a sum of all working hours for that day.

Here is the screen:

I did not label well, but in the nested gallery, the row label Text properties are : ThisItem.actType, ThisItem.startTime, ThisItem.endTime, ThisItem.duration, ThisItem.workTime

That's pretty much it.

Follow through the above and see how it works out for you.

[Anonymous]

@RandyHayes 

This looks great, however, I get errors when I use your code in my Items field of drop down for employee.  I think my TimeTracker list is a little different then your original one, as I am using Full Name for the Title. Here is my OnSelect formula for the Check In button:

Patch(TimeTracker, Defaults(TimeTracker), {Title:(User().FullName), ActivityType:"Check" & If("In" in Self.Text, "In", "Out"), Date:Text(Now(), DateTimeFormat.ShortDate), Time:Text(Now(), DateTimeFormat.ShortTime),   ActivityDateTime:Text(Now(), "[$-en-US]yyyymmddhhmm"), ActivityDate:Now()})

[Anonymous]

@RandyHayes 

Another thing is right now my Employee Dropdown is just pulling directly from the Title column since that is populated when the user clicks the buttons for clocking in and out.

[RandyHayes]

@Anonymous 

Yeah, I rarely use the full name as it is not unique, but if you are, then you need to adjust a little.

1) The Items property of the Main Gallery will now be:

With({lclEndDate: DateTimeValue(Text(DateAdd(DatePicker7_1.SelectedDate, 1, Days), ShortDate) & " 00:00")},
    AddColumns(
        SortByColumns(
            GroupBy(
                Filter(TimeTracker, StartsWith(Title, ddEmployee.Selected.Name) &&
                    ActivityDate>=DatePicker7.SelectedDate && ActivityDate<=lclEndDate
                ),
               "ActivityDateTxt",
               "_activities"
            ),
            "ActivityDateTxt"
        ),
        "_activityDateTime", DateValue(Text(First(_activities).ActivityDate, ShortDate))
    )
)

2) As for your list, I would seriously consider removing the Date and Time columns and creating new columns with different names (note: not just rename the columns as this will not truly rename them).  I say this only because Time and Date are reserved names and should be avoided to reduce conflicts between the Date and Time functions.

3) If you are only working with the Display Name of the user, then you can leave your dropdown Items as-is.

4) Other than that, you will need to either adjust your list to have columns with the type and names for the formulas, or adjust the formulas to reflect the column names and types in your list.

+ ALSO: Since your last message came through while typing this response...you'll need to adjust the above formula for the Items to reflect the column name of the dropdown that provides the user name.

[Anonymous]

@RandyHayes 

Should I disregard your formula for the Employee DD Items? Since I am using User().FullName for the Title. 

ForAll(Distinct(TimeTracker, Title),
    With({lclProf:Office365Users.UserProfileV2(Result)},
        {Name: lclProf.displayName,
         email: lclProf.mail
        }
    )
)

 Can I leave the Employee DD Items as is? And if so, will it work with the Gallery formulas?

SortByColumns(Distinct(TimeTracker, Title), "Result", Ascending)

I guess up until that point it works great! I would like to get the Gallery separated using the setup I already have if possible. I did change the Date and Time columns as you suggested. They are now PunchDate and PunchTime. 

[RandyHayes]

@Anonymous 

Yes, you can leave your dropdown.  You just need to change the part of the formula filter to be 

ddEmployee.Selected.Result

Based on your items formula.  Keep in mind though that Distinct is not delegable (currently) and so if your time entries grow over 2000, you'll want to look at other options to populate that dropdown.

I'm not sure what you mean by the "I would like to get the Gallery separated using the setup I already have if possible."  Can you explain?