I am on a new project and still mapping a few things out. The requirement is relatively simple but haven't got a solution in my mind yet and know there are some keen minds here who just love problem solving!

I will have a simple job table in which a start date and time are defined (as a timestamp) along with the duration of the job rounded to the nearest full hour. I simply need to calculate the end date and time but need to take into account limited hours on certain days.

For instance Monday to Friday we have 24 hours a day midnight to midnight, Saturday we have 12 hours from 7am to 7pm, Sunday there are none. A job starts on a Thursday at 2pm and needs 58 hours to complete. How do I calculate finish date and time? Sunday overtime is possible and would be specified in advance which should then change the finish date and time of any jobs running over that weekend.

I am thinking a secondary table for the available hours by day but still not sure how to use this data in a calculation or cover the partial days. Only an indication to the nearest hour is needed which may make this easier?

I would estimate 250 jobs will exist in this table and the start dates and times will be changed regularly.

A nudge in the right direction would be much appreciated!

I am using the PC version of Filemaker 16 advanced.

One way to approach this is to have three tables all together:

1. Jobs

2. Job Hours

3. Available Hours

You already have the Jobs table. The Job Hours table is a line table or a child table of Jobs. A simple data model to show a one to many relationship:

Jobs ---< Job Hours

And in the relationship graph:

Jobs::ID = Job Hours::Jobs ID

The Available Hours table is the table you have in mind (you called it "secondary table for available hours" in your original post). This is a reference table. The table would have 7 records, one for each day. I'd also include a start time, end time, and total time:

IDDay NameStart TimeEnd TimeTotal HoursWhen adding a job, you would have a script that determines the number of days and hours in that day/those days and write that to the Day Hours table.

I'm just working out the script in my head, as opposed to creating a test file to verify it, so take this for what it's worth. Having said that, I'd write something like this:

$hoursRequired = 58

$jobStartDay = Thursday

$thisDay = Thursday

$hoursAvailableThisDay = 14:00-23:59 = 10 hours (I rounded up)

$hoursNeeded = 58-10=48

That's where I'd start. I'd create a new record in the Job Hours table for that Thursday (presumably on a specific date). Then, I'd go to the reference table and start looping through the days and adjusting the scripts as needed. Exit the loop when the hours needed is 0.

$hoursNeeded >= 0 so continue with the loop:

Friday has 24 hours available, so

$thisDay = Friday

$hoursAvailableThisDay = 00:00-23:59 = 24 hours (I rounded up)

$hoursNeeded = 48-24=24

$hoursNeeded >= 0 so continue with the loop:

Saturday has 12 hours available, so

$thisDay = Saturday

$hoursAvailableThisDay = 07:00-19:00 = 12 hours

$hoursNeeded = 24-12=12

$hoursNeeded >= 0 so continue with the loop:

Sunday has 0 hours available, so

$thisDay = Sunday

$hoursAvailableThisDay = 00:00-0:00 = 0 hours

$hoursNeeded = 12-0=12

$hoursNeeded >= 0 so continue with the loop:

Monday has 24 hours available, so

$thisDay = Monday

$hoursAvailableThisDay = 00:00-23:59 = 24 hours

$hoursNeeded = 12-12=-12

$hoursNeeded >= 0 is now false, so the loop ends.

The Job Hours table would then look something like this

IDJob IDDateHoursStart TimeEnd TimeYou may notice that the last $hoursNeeded is a negative value. Cleaning that up with an Abs() function will make it positive before creating the Job Hours record.

The missing piece that I notice is that this doesn't take into account capacity. I presume you have that worked out.

HTH