Very complex field calculation

[lsinger]

Hi, I am new to Filemaker and to programming in general. I am trying to define a very complex field and my brain is fried...hope somebody can help figure this out.

I have a database containing serial measurements of a pulmonary function test called FEV1, for a number of subjects defined by medical record number "MRN". The layout looks, in part, like this:

MRN (text, unique for each subject)

PFT number (auto-entered, unique for each FEV1 measurement)

Date

FEV1 (number)

Each subject has a variable number of FEV1 measurements in the database.

I would like to define a new variable called BOS score. Here are the rules for defining BOS score:

1. Determine the baseline FEV1 value. This is defined as the average of the two previous highest consecutive measurements, such measurements being obtained 3-6 weeks apart.

2. Determine the current FEV1 measurement. This is the average of the two most recent measurements that are at least 1 month apart.

3. Determine the fractional decline in the FEV1, defined as the percent decline in the current FEV1 measurement compared with the baseline measurement. For example, if the baseline measurement is 2.0 liters and the current measurement is 1.0 liters then the fractional decline is 50%.

4. Define BOS score as follows:

Frac decline (FD) < 20%...BOS 0

FD 20-34%...BOS 1

FD 35-49%...BOS 2

FD >= 50%...BOS 3

Thanks!

------------------

Lianne Singer, MD

Clinical Research Fellow, Lung and Heart-Lung Transplantation

Stanford University

[Keith M. Davie]

A quick read and it seems to me like you need some other calculation fields. FEV1, e.g., may need a field bFEV1 (baseline FEV1) and a field cFEV1 (current FEV1) and another field dFEV1 (fractional-decline FEV1).

BOS then may be definable with either an IF or a CASE caluclation.

Hope this is useful.

Peace

Keith M. Davie

[LiveOak]

I would approach this with two database files. The first with one record for each subject/MRN and the second containing the individual FEV1 measurements. If you are using at least FM 4.0, you can create a portal in the subject/MRN file showing all the FEV1 measurements for a given subject sorted in an appropriate order. Processing of FEV1 records would be done with a script.

Determining the baseline need some clarification. The calculation of the baseline using "two previous highest consecutive measurements, such measurements being obtained 3-6 weeks apart" needs further definition. For a given measurement, which records are the "two previous consecutive" measurements is clear. For measurement 3, this would be measurements 1 & 2 (if they meet the 3-6 weeks criteria and previous means immediately previous). I'm not sure how to apply "highest" to this. If the previous two consecutive measurements are not the two highest, is the consecutive and/or immediately previous requirement relieved? Sometimes it is much easier for a person to look at the data and select the measurements to use, than to create a mechanical procedure to pick the correct two measurements for the baseline. Also would a new baseline be used for each new FEV1 measurement? -bd