I want to be able to put the name of medication in one field and then the side effects automatically enter into another field (on other layouts they need to be separated).
Create a table called Medications. First field should be MedicationID, auto-enter serial number increment by 1.
Create a table called SideEffects. This table (all tables) should have auto-enter serial, creation timestamp, modification timestamp so also create SideEffectID. In SideEffects, also create a number field called MedicationID. Relate them in the graph as:
Medications::MedicationID = SideEffects::MedicationID
Now on Medications layout, select Insert > Portal and select SideEffects. This will list all side effects for each medication. To select a MedicationID for a side effect (in the Side Effects table), create a value list (File > Manage > Value Lists) called Medications and select 'use values from field.' Select the MedicationID in the left pane and select the medication name in the right page (as also display). Below, check 'show values only from second field.' On your SideEffects MedicationID field, attach the value list (Inspector > Data tab > Control Style: Pop-up Menu. Pop-up Menu will display the name but insert the ID.
This is the basics for establishing a 1:n (one-to-many) relationship.
One more thing ... "automatically enter into another field"
Can you explain why you need all the related records entered into a field in Medications? Usually those side effects would simply be displayed through the relationship. If you don't want a portal but want a comma-separated list then create a calculation in Medications (after you establish the relationship I suggested) and the calculation would be:
Substitute ( List ( SideEffects::EffectName ) ; ¶ ; ", " )
This list of medication side effects should remain a calculation (unstored) so it will update. If you plant the side effects into a medication field, it will not update if you add or change a medication side effect.
One of the very big changes in what FileMaker can do is that you can now create more than one table in the same file. It's important to understand that layouts and tables are not the same thing. Every layout links to a specific "table occurence". This is one of the "boxes" you'll find in Manage | Database | Relationships and each of these boxes, in turn, link to a specific table you create on the tables tab of this same window.
You can have many layouts that refer to the same table occurrence and you can have many table occurrences that refer to the same table. For simplicity, we'll skip over the "table occurrence" part of this set up and just refer to "tables" for the moment.
You can define one table where each record documents information for a specific patient. You can define a second table where each record documents a specific medication--that's where you can list side effects and other drug specific info. You can then define third table to link a selected medication record to a given patient. That way you can document each drug once in the medication table, each patient once in the patient table, but use the linking table (called a "join" table in database jargon), so that a given patient can be linked to many different medications and a given medication can be linked to many different patients.
You may also, eventually, want to add yet another table to list multiple warnings, side-effects, etc. for a specific medication, but let's leave that issue for later. For now, we'll use a single text field to document side effects just to get a basic system up and running.
To set up what you need just so you can meet the specific needs of your post, define the following fields in each of these three tables:
PatientID : make this a number field and use field options to make it an auto-entered serial number. This is our primary key field for this table.
PatientName : Text field
MedicationID : Serial number field like PatientID. This is the Primary key field for this table.
MedicationName : Text Field
MedicationSideEffects : Text Field
PatientID : Number field DO NOT define as a serial number field. This is a foreign key field to the Patient table.
MedicationID : Number field. Foreign key to Medication table.
After creating these tables and fields. click on the relationships tab and drag from Patient::PatientID to Patient_Medication::PatientID to link these two tables in a relationship. Double click this relationship line to bring up a dialog of the relationship details and click "Allow creation of Records via this relationship" for the Patient_Medication table. Click the "Delete" option for this same table. DO NOT click the other delete option for the Patient table or deleting a patient's link record to a medication record will also delete the patient record.
Now drag from Medication to Patient_Medication to link them by the MedicationID fields. You don't have to specify any options on this relationship.
You should now have these relationships:
Patients::PatientID = Patient_Medication::PatientID
Medications::MedicationID = Patient_Medication::MedicationID
Click OK to dismiss Manage | Database.
Go to your Patient Layout. Enter Layout Mode.
Select the portal tool at the top of this window and draw a rectangle on your layout to add a portal. Select Patient_Medication as the table (Table Occurrence) for your portal. Enable the scroll bar and delete portal row options.
Click OK and then add these fields to the portal:
Now select Medication from the drop down and select:
Click OK to close the dialogs. You can resize the portal and the fields in them to better suit your preferences. Resize things to leave a small amount of space to the right of the last field. You can click this empty area to select a portal row and then use Delete Records from the Records menu to delete the portal record--which deletes the linking record to a medication, but does not delete medication records. Later, when you are up to date on scripting, you can add a button here to delete the link record from the portal.
Click on the MedictionID field and use the inspector's data tab to set this field up as a drop down list of Medication ID numbers from the Medications table. Click i at the top of your screen if you can't see the Inspector. On the data tab, select Drop down list from the Control Style drop down. Click the pencil icon to the right of the Values From: drop down to bring up Manage | value lists where you can create the value list for your drop down. Click New, name the value list and then click the Use values from Field radio button. Select Medications from the drop down and select MedicationID in the field 1 box. Click the "Also display values from second field" check box and select MedicationName in the Field 2 box. Click OK repeatedly until all your dialogs are closed.
Now enter browse mode and change to the Medications layout. You may want to set up your layout for View as Table to make it easier to manage a list of all medications you want to enter in this table. For testing purposes, create two or more new records for different medications and fill out all the fields for each. Now switch back to your patients layout and try selecting medications from the drop down list. Each time you select a medication, the value list enters the ID number and the other fields populate to show the relevant information from the Medications table. You can add as many medications as you want by selecing a different medication on each row of your portal.
This is just to get you started. There are ways to add buttons and scripts to this layout to facilitate adding a new medication record and a link to it for the current patient. You can add fields to the join table to document info, such as dosage information, specific to that pairing of Patient and medication records. There are scripted techniques that make it easier to select a medication by name than the drop down list you have currently setup. Reports layouts based on the join table can make it easier to print out a list of all medications listed for a specific patient or group of patients....
Hi LaRetta, thanks so much for your help and your quick responses.
Obviously, I'm confused so let me explain what I'm dealing with and see what you think the best way to
We have several clients who obviously will take different medications, we have different reports that will require just the medications they take and another report that requires the medication plus the side effects of that medication. So when the medication is being entered, we may not need the side effects for that particular report but will like the side effects of that particular medication to auto enter on the report that does require that information. I hope this better answers your question. Let me know if you need more info. Thanks so much!
"we have different reports that will require just the medications they take and another report that requires the medication plus the side effects of that medication. "
If you have the relationship as I've indicated (Medications::MedicationID = SideEffects::MedicationID) then we are only talking about a display issue:
All layouts (e.g. form, list, table) display whatever data is in fields that you choose to place on that layout. Create *two reports; one with side effects and one without. How you want the side effects displayed and how to report is created is another question - you can have the side effects in a list (just take the related side effects field and place in portal on your Medications layout if a form; print from Side Effects if a list) or comma separated (place calculation field in Medications).
Are we getting closer? Do you have this relationship in blue?
It is in blue, and I'm sure were getting closer, or maybe even there. I haven't had a chance to work with the suggestions that were given, I'll work with it on Monday morning and see what happens. I appreciate you and PilModJunk for all the help, and I'm going to look you guys up if I have any questions. Have a good weekend!