Time Calculations

[seaDog]

This is driving me round and round in circles.

I have a db where a person enters the hours they work in a day ie 0800 - 1800 10 hrs, no problem, on each record there are also 4 other fields that need to calculate the total of hours worked in the previous 3/7/28 and 365 days. This is to ensure the worker does not go over their limitations on working hours. Each day is on its own record.

I can provide the data when I use loops in a 'new record' script and pull up using a search the previous 3/7/28 and 365 days and total them together in a global field and add them to the new record. I am sure there is a better way but this worked ok. However when a new record is clicked the date is automatically set as the previous record plus 1 then the script carries out the searche to obtain the totals. My problem is if the user then changes the date after the script is run, all the 3/7/28 and 365 totals will be wrong.

Can anyone point me in the right direction or an example file would be good.

Thanks for your time

seaDog

[kennedy]

Is it really the previous 7 days, or is it within the defined week (Mon-Sun or Sun-Sat or whichever)?

Is it really the previous 28 days, or is it within the calendar month or pay period?

For work week or calendar month, I'd define a calculation field that computes which work week from the date and another that computes which month or pay period from the date. Then I'd define a self-join that matches on those calculations (work week or month) concatenated with the person's ID. Then define your sum fields to calculate the Sum over the hours field on the corresponding relationship.

For arbitrary N prior days, using relationships is a bit more clunky. For 3 days, its workable... define 3 fields and 3 relationships and add together what each finds. But doing that for 28 days would be real ugly.

[seaDog]

thanks Kennedy,

It is for arbitary previous days, irrespective of days of week etc.

It's for pilots to work out hours to make sure they are within flight limitation rules. We currently use an access spread sheet, but its ugly and clunky. Regulations state you can only fly so many hours in 3days/7days/28 days and 365 days.

thanks

seaDog

[kennedy]

Interesting problem... it could be done with Calc's if you don't mind having 365+4 calc fields and 365 relationships.

Otherwise, you could use 6 globals and a script. The script does a loop through a self-join to collect the four sums into four of the globals. The other two globals are used to indicate if the four sum globals are up-to-date for this particular record. If not, then the fields on the layout are blanked. If so, then the fields are shown. The button invokes the script to make them up-to-date.

The smart guys on this forum might have a much better answer for you.

[seaDog]

I see what you mean, I can get what I want by having the user enter his data and then hitting a totals button which runs a script and adds up all the totals, but I'd hoped I could do it with no user action or at the least hidden,

Thanks

seaDog

[seaDog]

Ok I've got it working with 365 fields plus 4 calcs and 365 relationships and it works fine. If the user changes any working times then it automatically updates fine.

The only problem is that when a new record is requested it is really slow to bring it up, I guess because it has to total 365 fields/relationships.

Anyone got any further advice or a better solution regarding this problem.

Or any pointers

Thanks

seaDog

[djgogi]

You don't need 365 calc but only 4 relationships:

First define value list "FlightDates" as use only related values from field "DateToNum" (it's simply stored calculation from field Date with result number) from relationships PilotRel (I suppose you allready have something similara to identify records for each pilot)

Also in the same db define an stored calculation PilotDateID

DateToNum & " " & PilotID (result text)

Now define 4 calculation fields, unstored result text

3DaysPeriod=Substitute(LeftWords(ValueListItems(Status(CurrentFileName), "FlightDates"), 3)& "

[djgogi]

I said, LeftWords?

Sorry you need RightWords everywhere in formula.

Dj

[seaDog]

thanks djgogi,

I actually stumbled across the solution very similar but not using smart ranges. Just using running totals with 4 calcs and four relationships, ie running total of current day-1 minus running total from current day -29 and added to todays total gives the right answer.

I got there eventually but I very much appreciate your time, I will probably change to your solution as its a lot neater.

Once again, thanks very much

seaDog

[kennedy]

Doesn't this solution assume that every date gets a record. I was assuming that only dates where they actually logged hours got a record. Thus, the last 3 dates in the ValuesList may be more than 3 days ago.

Or am I missing something?

[djgogi]

Nop, only related records.

BTW, probably the business rules in this case would require record for every day (even if no hours logged)

Dj