Conditional find in related records

[cbum]

First off, sorry if the nomenclature is off, I'll try to describe what I need as best I can.

I have a large single file filemaker db (35k records) with medical data, which include multiple records for the same patient (different samples at different dates), each with a diagnosis field.

I'm using a portal to give me a snapshot of all records of the same patient, when present (not always).

Now, I need to find patients who have certain types of disease progression, i.e benign lesion -> cancer over time.

I can select cases with multiple record entries, specific diagnoses, and a minimum follow-up period, e.g. 1 or 5 years, but not all hits are useful: many cases have the earliest records start out with the endpoint disease (i.e. cancer).

I'm trying to devise a search strategy that only gives me cases where the index record (earliest date) is not the endpoint disease.

For example, give me all records showing a non-cancer diagnosis, with a related record (same patient) showing cancer at least 1 year later (or 5 years later etc).

Any suggestions?

thanks.

[cjaeger]

I 'd rather do a normal scripted search in the related file becaus it is more flexible, then "flag" the found set and relate it to a global in your patients file...

[cbum]

Christian,

I'm not sure I follow. There is only one file...

[cbum]

No takers?

Is the question poorly worded, or sould this be elsewhere, e.g. in the portals section?

[Ugo DI LUCA]

Hi,

Christian wasn't off-track !

Setting relationships to work with this kind of VARIABLE criterias would end to a real nightmare, as you'd need a lot of unstored calculations.

It could though be splitted into parts involving a relationship to the "Parent" disease by a global in some place because...

give me all records showing a non-cancer diagnosis, with a related record (same patient) showing cancer at least 1 year later (or 5 years later etc).

can also be turned into

give me all records showing a cancer diagnosis (Parent), with related child (not cancer) at least 1 year before (or 5 years before etc)

First, there may be a need to have the "ending point" stored as an index in all chil records, so that when searching for Cancer in an "ending point", you'd find ALL patients records, and not only the ending record.

For this same purpose, you may also have the "endingPointDate" stored and indexed.

Then, in this particular search request, you could either :

1. Go To the related records (g_Disease::EndingPoint)

2. Loop from first record to last and omit each record where "EndingDate-AppointmentDate" doesn't match your range.

Or

1. Define a calc field that will evaluate the gap from EndingPointDate to Appointment in any records in your file

2. Perform a 2 requests find (1. EndingDisease - 2. Gap)

As you can see, there can be so many variables that it is difficult to advise you more than this.

As a matter of fact, from some time now, I use a "Find file" and a "Criteria File" elsewhere, and store them there.

Then, a different find takes place according to this set of criterias and the "Find" I want to perform.

But this doesn't turn it to some simple solution for sure

[cbum]

Ugo, thanks for your response.

I need to let it percolate a bit to make sense of it.

You are right that both ways to formulate the query are equivalent, but I don't follow what

" there is a need to have the "ending point" stored as an index in all chil records, so that when searching for Cancer in an "ending point", you'd find ALL patients records, and not only the ending record"

means...

I assume you mean that a cancer diagnosis IS the ending point, so what does searching for Cancer in an ending point mean?

I like the idea of a gap calc field, but how can I "evaluate the gap from EndingPointDate to Appointment in any records" making sure I'm only evaluating related (same pt records) ?

It's late here, let me think about it a bit more in the morning.

thanks again.

[Ugo DI LUCA]

Hi,

It was late too here...

"I'm trying to devise a search strategy that only gives me cases where the index record (earliest date) is not the endpoint disease."

To have an efficient search executed, without any unstored calc, you may need to have both the Appointment Date and EndPoint Disease date in each record.

My understanding is that when a Diagnosis is established, a script should go back to all records for this particular patient samples and enter the "Final Diagnosis" and "Final Diagnosis Date" in all records.

Then, later, it would be easy to find all records matching a specific gap, as well as all records for a specific Diagnosis.

For instance

#1 Patient: X --- Sample:1 --- DiagnosisDate: Diagnosis:

#2 Patient :X --- Sample:2 --- DiagnosisDate: Diagnosis:

#3 Patient :X --- Sample:3 --- DiagnosisDate: Diagnosis:

#4 Patient :X --- Sample 4 --- DiagnosisDate: 02/11/04 Diagnosis: Cancer

So now, you could have a field c_Diagnosis = IsEmpty(Diagnosis) with a boolean result of 1 if empty, 0 if not.

Drop another global g_DiagnosisMatch with value=1

Create 2 concanations

c_RightKey =PatientID&"-"& c_Diagnosis

c_LeftKey = PatientID&"-"&g_Diagnosis

Establish a relationship "SamplesToDiagosis"

Create 3 other global fields

gID (text)

gDiagnosis (text)

gDiagnosisDate (date)

When a Diagnosis is entered, use a script

SetField[gID, ID]

SetField[gDiagnosis, Diagnosis]

GoTo Related Records [samplesToDiagosis-show only]

Go To Record[First]

Loop

SetField[Diagnosis,gDiagnosis]

SetField[DiagnosisDate,gDiagnosisDate]

GoToRecord[next-end after last]

EndLoop

GoToRelated Record["IDbygID"]

This still only solve one kind of scripted find, and doesn't offer much more flexibility

[cbum]

Luca,

thanks for your suggestions. My head hurts looking at it though. I guess this problem is more complex than I imagined... -(

I'll give it a try on a small subset of records and see if it works.