Parent Database: Name of Drug

Related Database (Formulation) by Drug ID

Related Database (Dosage) by Drug ID_Formualtion ID (concatenate the IDs
from the Drug and Formulation Database

In the Parent database define 2 relationships:

1. Formulation:: by Drug_ID

2. Dosage:: by Drug_ID&Formulation_ID

Create 2 portals for data entry based on the above relationships.

So, when you define a new drug in the Parent Database you can name the
formulations in the first portal.  Place a button on the portal row that
sets the Key field for the second portal.  Now you can enter the dosage(s)
for each drug formulation.  I recommend auto serial #s such as D1000000 to
start the Drug Database and F1000000 to start the Formulation database.

By parsing the linking field in the Dosage database using the Left and Right
functions in the Dosage database and making relationships back to Drugs and
Formulation you can have the names of the drug and formulation looked up or
calculated in the Dosage file.

As for reporting, I would make a third database and use a combination of the
above fields (Drug, Formulation and Doasage) to get a extract  the related
information.

If you need help email me.

Not an expert on drug naming, but from your description it sounds like
each manufacturer, formulation and dose of a drug might have its own ID.
Anyway, here is a way to proceed that I believe will do what you want:

Two tables:

Drug Name
Formulation Dose

Fields in Drug Name table:
DrugSerial, an automatically generated serial number, that can not be
modified.
Drug name, a text field, for the generic name of the drug.

Fields in Formulation Dose table:
DrugSerial, a number field
Formulation Serial, an automatically generated serial number that can not
be modified
Formulation, a text field
Dose, a text field (so that you can specify units; an alternative would
have a units field and a dose number field, but you probably don't need
that unless you want to do calculations upon the numbers)
Manufacturer, a text field
DrugID, a text field
Trade name, a text field

Relate the two tables by DrugSerial
Allow creation of records in the Formulation Dose table via the
relationship

Place a portal in the layout of the DrugName table, to take values from
the related Formulation Dose table. Allow deletion of portal records.

Fields in the portal:
Formulation
Dose
Manufacturer
Drug ID
Trade name

Do data entry in the Drug Name layout. Enter as many combinations of
formulation, dose, Drug ID, manufacturer and trade name as desired through
the portal. They will all be related to the parent generic drug name by
way of the Drug Serial number

You can put a related field for drug name in the Formulation Dose layout
if you wish. You can format the portal field for formulation as a pop-up
list with values Tablet, Ointment, Drop, etc.

You can make a layout of the Formulation Dose table, with Drug ID first,
followed by other fields as desired, including the generic name from the
related Drug Name record. You can mke this a columnar report for ease of
display, printing, etc.

You can define other layouts asdesired in order to present the data in
many differnet ways. You can define scripts to find and sort the data,
etc.

No doubt other refienments will come to mind.

I have worked this up in a skeletal way in FMP 7. Let me know if you want
me to e-mail it to you.

Bill