Working with related records

[kraftyman]

I have two databases. One lists patients, and the other lists all their medications, and they usually take many different drugs. ( one to many relationship )Each drug belongs to a specific class, like "contracepttive pill" etc. So there is a Class text field in the drugs database.

I want Filemaker to do the following:

While I am in the "Patient" database, to search through the drug database and tell me if this specific patient is on a drug which belongs to a specific class. Then I can use the result of this search to make a comment like "Patient is on the contraceptive pill." in a calculated field.

There doesn't seem to be any way of doing this, because Filemaker is unable to filter related records based on criteria.

Any ideas?

[LiveOak]

Sure it is. You just have to build a compound index with both the patient key and a key based upon the drug class. Perhaps the key in the drug file is:

CompoundKey (calculation,text,indexing to ASCII)

Patient ID & "-" & Class

Create fields in Patient file:

gClass (global text)

CompoundKey (calculation,text,indexing to ASCII)

Patient ID & "-" & gClass

A relationship based upon these two keys might be called patient.drug. A portal would display all the drugs of a given class for the current patient when the gClass field is set to a drug class. -bd

[kraftyman]

Thank you very much. It works! Still,it's a very clumsy and cumbersome way to do it.( I come from MSAccess land, where this sort of thing was a snip ;-)

[danjacoby]

You have two files, called "Patients" and "Drugs".

The first file has a field for the patient's name.

The second file has "PatientName", "Class" and "Drug" fields.

In the first file, create a relationship based on the patient's name. Then create a portal which has the "Class" and "Drug" fields.

And you don't even have to do a search.

Now, if you want to get really creative, create a third file, with "Class" and "Drug" fields, and use that to create related value lists, so that when you choose a particular class, only the drugs in that class come up in the popup list for drugs.

HTH,

Dan