Calculate difference between fields in records

[Marcel1]

Hi,

I have a database containing records with fields for "serialID", "patient", "sample", and "time collected". Often, several records share the same values for patient and "time collected".

I would like to have a field that calculates "IntervalTime" for each record. This would be the elapsed time between when the sample from that record was collected and the time when the first sample from the same patient was collected. (Obviously, the entry for IntervalTime would always be = 0 for the first sample collected for any patient, and all subsequent samples from that patient would have values > 0)

I think this is crying out for a script, but I am not sure how to do it. Any thoughts welcome.

Thanks,

Marcel

[T-Square]

Marcel--

You might be able to accomplish this by using relationships and a calculation field. The first point to realize is that your IntervalTime field is really a calculation between two records--the current record and the previous entry.

Try this: go into the Relationship graph and add a second copy of the Sample table and call it "PreviousSample". Now, create a relationship between the copies based on the PatientID AND the Time Collected Field, and edit the relationship so that the match type for the Time Collected field is:

Sample::Time Collected > PreviousSample::TimeCollected

[PatientID should be a simple match]

Also, sort the PreviousSample table By Time Collected Descending. This should make it so that the PreviousSample "table" contains only earlier entries for that patient.

Now, you should be able to create a simple Interval field in Samples:

Interval [Evaluate from Sample]

If(PreviousSample::Time Collected; Sample::Time Collected - PreviousSample::TimeCollected; "")

Make sure the field is set up to receive the right type of info.

I may be wrong here, but that SHOULD use the first entry in the "related" table (which in this case is the same table) and give you a proper result.

David

[Marcel1]

Hi David,

Sounds like this approach might be helpful. One issue; the "IntervalTime" should always be the time from the 1st sample to the time of any other sample. (I should probably call it "elapsed time"). Any way to adapt your solution?

Marcel

Edited June 9, 2006 by Guest

[T-Square]

Yeah-- just change the sort order from Descending to Ascending.

This is making use of the fact that when you create a calculation on a related table, it uses the first entry it encounters there. When you sort the related table descending, it gets the entry immediately before; when you sort it ascending, it grabs the earliest entry.

David