tricky date calculation

[Andreas Zankl]

I am a doctor and working on a research project. I have patients who have a lab value measured several times a day. If the value is above a certain level, we start a medication and we adjust the dosage several times a day. I record the lab value in LabValues:value and the time when the lab value was measured in LabValues:timestamp. I record the medication dosage in Medication:dosage and the time when the medication dosage was adjusted in Medication:timestamp. I want to find the last lab value that was recorded before the medication was started and color it red. I cannot figure out the formula to do that. Can anybody help?

Thanks

Andreas

(Using FM11 on a Mac)

[comment]

This part is not quite clear:

I want to find the last lab value that was recorded before the medication was started and color it red.

The "last lab value" is different for each change of dosage, isn't it? So where exactly do you want to find it? You could, if you wanted, link each dosage to its own "last lab value" using a relationship.

[Andreas Zankl]

No, that's part of the problem. The lab values get measured independently of the dosage changes. So I want to find the lab value that triggered the first dosage change (i.e. starting the medication). That would be the lab value that was measured just before or right at the time of the first dosage change. I tried the following formula to conditionally format the LabValues:value field:

If ( LabValues::Timestamp <= Min ( Medication::Timestamp ) ; 1 ; 0 )

This allows me to color all lab values that were measured up to the time when the medication was started, but I only want to color the very last value that was measured before the medication was started.

Hope this is more clear. Thanks for your help!

Andreas

[comment]

I see (the word "find" has a specific meaning in Filemaker, so that was a bit confusing). Anyway, how about this way?

LastLab.zip

[Andreas Zankl]

That works.

Thanks a lot!

Andreas