5 Replies Latest reply on Nov 23, 2009 6:25 AM by comment_1

# Calculated Time fields results

### Title

Calculated Time fields results

### Post

Hello

I have theexperimental version of Filemaker Pro 10 for Windows. I am trying to see if Icould solve this problem:

In mydatabase I have a few records and one of the fields is a time value.

Imagine 15 records and  witch one with a different time value.

How can Icalculate de difference between time values from one record to another, no matterthe sort order and save the result on a field: I think it shouldbe something like this but I can put this in Filemaker’s language

The time valueof the second record less the time value of the first record;

The time valueof the third record less the time value of the second record

And so one…..UntilI reach the last record

Is itpossible to do. Can someone help me?

Regards

Paulo

• ###### 1. Re: Calculated Time fields results

Hi Paulo

try:

Case(
Get ( RecordNumber ) > 1 ; YourTimeStampField - GetNthRecord ( YourTimeStampField ; Get ( RecordNumber ) - 1 )
)

result of type time.

• ###### 2. Re: Calculated Time fields results
P.S.: the result of that calculation must be UNSTORED
• ###### 3. Re: Calculated Time fields results

Ciao Daniele
La ringrazio per il vostro aiuto

In fact the solution you achieved works very well for an unsorted database.

But what I need is a solution for when I have to group and sort data.

I will try to explain

A - I have a race with 10 racers.

B - When the race starts I reset the system clock to 0h 0m 0s

C - every time a racer cross the finish line, after a lap, I create a new record with the racer number (manually) and the time he cross the finish line (automatic),

D - I do this until the race finishes for every pass at the fish line and obviously for every racer.

E - Then I grouped the records by racer number and I would like to calc the time by lap for each racer

Regards,

Paulo

PS: Your English is very good

I am portuguese, from Azores islands

• ###### 4. Re: Calculated Time fields results

paulo.casa wrote:

...the solution you achieved works very well for an unsorted database.

?

The solution works on each found set and, to give exact results, MUST be sorted.

• ###### 5. Re: Calculated Time fields results

You should define a self-join relationship between the lap records, something like:

Laps:RacerID = Laps 2::RacerID

AND

Laps::LapID > Laps2::LapID

with records from Laps ID sorted by LapID, descending. Then you can auto-enter the finish time from the first related record in Laps 2 into the start field.