Jump to content
Sign in to follow this  
Graham Hughes

Comparing relative records

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.