I script it this way....
I have a timelines table that calculates hours worked for each job.
A person can work on multiple jobs per day.
Each clock-in / clock-out constitutes one timeline.
I have an auto-calc field called "StaffYearWeek" that concatenates the workerID, the Week of the Year AND the Year (based on the Work Date)
When the clock out occurs and the relevant fields are set in the timeline, I GTRR - Timelines >> Timelines_ID StaffYearWeek (self join) where the relationship is SORTED by:
- ID_StaffYearWeek
- Work Date
- Time In
The sort takes care of a timeline that may not have been entered in chronological order.
Once in this TO, a field called Hours_this_Timecard has already been calculated on each of the timelines: Time Out - Time In. In the first record, I set a number field called Hours_This_Week which will be Hours_this_Timecard for the first record.
Go to 1st Record
Set Variable $hours_this_week = Hours_this_Timecard
Loop
Go to Record Next (exit after last ON)
Set Field Hours_this_Week = $hours_this_week + Hours_this_Timecard
Set Variable $hours_this_week = Hours_this_Week
End Loop
I also have a Hours_Regular_Time field and an Hours_Overtime field.
Hours Regular is this auto-calc:
Let ( [
UntilToday =
T15_TIMELINES::Hours_This_Week - T15_TIMELINES::Hours_This_Timecard ;
ThisCard = T15_TIMELINES::Hours_This_Timecard ;
Now = T15_TIMELINES::Hours_This_Week
] ;
Case ( UntilToday >= 40; 0;
Now > 40; 40 - UntilToday ;
ThisCard ))
This is Hours Overtime:
Let ( [
UntilToday =
T15_TIMELINES::Hours_This_Week - T15_TIMELINES::Hours_This_Timecard ;
ThisCard = T15_TIMELINES::Hours_This_Timecard ;
Now = T15_TIMELINES::Hours_This_Week
] ;
Case ( UntilToday >= 40; ThisCard;
Now > 40; Now - 40 ))
I'll have to think about Holiday Hours. Somehow there needs to be a way to designate a day as a Holiday that will trigger the pay rate to be 1.5 the regular rate.