Comparing relative records

[Graham Hughes]

I am trying to write an application that processes sequential timestamps. Each record in the file has a sequence number and timestamp.

I am trying to create a relationship over the file that compares each record with the previous one and returns the time difference betwen each one. Should I do this purely with relationships or through lookups etc?

Example records;

Seq Timestamp

001 28/05/2007 16:45:00

002 28/05/2007 17:00:00 (difference = 00:15:00)

003 28/05/2007 21:30:00 (difference = 04:30:00)

004 29/05/2007 03:00:00 (difference = 05:30:00)

etc....

Can anyone give me a clue how to go about this?

Thanks, Graham

[Søren Dyhr]

None of them are particular good, if say you delete a record or omit it from the found set, won't it update accordingly ...and since you're on fm7 must this be done via a recusive script "IpSumQue" instead, such as:

If [ IsEmpty ( Get ( ScriptParameter ) ) ] 

     Go to Record/Request/Page [ First ] 

     Perform Script [ “IpSumQue”; Parameter: test::theTimeStamp ] 

Else 

     Go to Record/Request/Page [ Next ] 

     Set Field [ test::Diff; test::theTimeStamp - Get ( ScriptParameter ) ] 

     If [ Get ( RecordNumber )  ≠  Get ( FoundCount ) ] 

          Perform Script [ “IpSumQue”; Parameter: test::theTimeStamp ] 

     Else 

          Halt Script 

     End If 

End If

The use of Halt Script could seem a little daft, but it cancels the unwinding of the stack produced by the recursions, and makes it faster to complete.

--sd

[Graham Hughes]

Many thanks for the response - that has given me some useful ideas.

I was hoping to achieve something that joins the file to itself by using [sequence number -1) so that the value of the previous record is available via the relationship.

I have since found a way to do what I need by using a lookup and a separate "split time" field.

Thanks for your help.

[Søren Dyhr]

Yes it's pretty straight forward:

http://www.filemakerpros.com/LULAST.zip

...but I found the urge to tell you how vulnerable it is to deletion of records in the sequence.

An attempt to cure this, could be done with a plugin, such as it is, in this template:

http://www.nightwing.com.au/FileMaker/demos8/demo807.html

--sd