9 Replies Latest reply on Sep 21, 2014 9:03 AM by philmodjunk

# Get sum of hours since previous Monday for an employee

### Title

Get sum of hours since previous Monday for an employee

### Post

I am trying to create a calculation field on a time log database but I've run into a problem.

Picture a table with the following fields.   EmployeeID, Hours (time data type) DateWorked, HourlyRate, HourlyOvertimeRate.

OK, that's just the basics but here's what I have done.  I created a calculation field that returns the Monday before the DateWorked value to determine the week's start date.  Now I want a calculation that will return the sum of hours for that employee since the value of the Previous Monday field.  Then I can determine if the new record puts the employee into overtime (over 40 hours since Monday) and calculate the cost of this hour entry based on his straight time hourly rate and overtime hourly rate.

Keep in mind there may be several records for an employee in a single day broken down by different jobs.

So say an employee has 39 hours already since monday and a new record is created for 4 hours.  I need to create a calculation field that will do something like 1 hour times straight time rate + 3 hours times overtime rate.  (with of course consideration for minutes as the hour field is a time datatype.

Any suggestions would be greatly appreciated!

• ###### 1. Re: Get sum of hours since previous Monday for an employee

Is this calculation needed from the context of a table of employees or the context of your timeLog table?

Either way, a relationship can be set up that matches by employeeID and the calculated Monday date to match to all records for the same employee and in the same week.

The following example assumes TimeLog as the context:

TimeLog::EmployeeID = TimeLog|SameEmpWeek::EmployeeID AND
TimeLog::cMondayDate = TimeLog|SameEmpWeek::cMondayDate

From TimeLog, Sum ( TimeLog|SameEmpWeek::Hours ) will return the total hours worked by that employee over the same week.

TimeLog|SameEmpWeek::sTotalHours, with sTotalHours defined as a summary field computing total hours, if also referenced from TimeLog would show the same total.

Caulkins Consulting, Home of Adventures In FileMaking

• ###### 2. Re: Get sum of hours since previous Monday for an employee

Thanks a million Phil!   Let me see if I can make that work.  If nothing else you've given me a great starting point.

Thanks again.

• ###### 3. Re: Get sum of hours since previous Monday for an employee

OK, thanks to Phil I now can get the sum of all hours from the start date (previous monday) to the current date.

However as this is a calculation field what I really need is the sum of all hours from the start date to the date of the current time log entry (ignoring values from entries after the date of the current time log entry).

What I'm hoping to accomplish is:

log hours this entry - Date of this log hour entry -   Start of week date  - sum of log hours from start of week to this  log entry date (and it can include or not include the hours from this entry) so that the sum is sequentially increased record by record.

I have everything but the sum entry at the end.

Again, any help is greatly appreciated and thanks again Phil.

• ###### 4. Re: Get sum of hours since previous Monday for an employee

Can you define the Week a number using WeekOfYearFiscal.  Then summarize hours on this.  You could summarize on:

WeekNumber
Employee
Job Codes

• ###### 5. Re: Get sum of hours since previous Monday for an employee

Use this relationship:

TimeLog::EmployeeID = TimeLog|SameEmpWeek::EmployeeID AND
TimeLog::cMondayDate = TimeLog|SameEmpWeek::cMondayDate And
TimelLog::Date > TimeLog|SameEmpWeek::Date

and you can change > to > if you want the current record to match to itself.

• ###### 6. Re: Get sum of hours since previous Monday for an employee

Thanks TK

I considered that but that won't give me a breakdown by day for each record.

• ###### 7. Re: Get sum of hours since previous Monday for an employee

If you have subsummaries at

WeekNumber
Employee
Job Codes

You can add a Date break before or after the Employee depending if you want to see all employees grouped by date or all dates grouped by employee.

Personally I set up my layouts for each.

• ###### 8. Re: Get sum of hours since previous Monday for an employee

Phil you nailed it!  That was EXACTLY what I needed.

I can't thank you and TKnTexas enough.

• ###### 9. Re: Get sum of hours since previous Monday for an employee

The solution suggested by TKnTexas is also a very good one and these methods are not mutually exclusive.