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?

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

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 ;-)

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