4 Replies Latest reply on May 24, 2011 10:49 AM by CaitlinM

    Complicated relationships between related tables



      Complicated relationships between related tables



      I am trying to create a database that automatically creates separate tables based on a patient's status. For example, I have one table with all the biographical information related to a patient, another with insurance information, etc. One of the fields related to the patients' unique identifier is their status, in my case - their transplant status i.e. "transplant complete," "considered for transplant," "rejected for transplant," or "unconsidered for transplant." 

      For each status, their are associated tables specifically related to that status, for example if their status is "transplant complete," there are several related fields: "date of transplant," "type of transplant," etc. 

      Does anyone know how I can make that relationship that is based off the patient's unique identifier and their status? Can I just make two conditions that need to be met in the relationship before it relates to the other tables?

      Thank you for your help!


        • 1. Re: Complicated relationships between related tables

          Creating tables dynamically in FileMaker isn't really practical. While there are ways that a script can create the table and import data into it, no currently existing layouts, scripts, etc. can access the data in this new table until a person with full access then makes additional design changes to the file.

          Instead, all biographical records for all patients should be stored in the same table, but with a PatientID field that uniquely identifies each patient. Likewise a patient status table can record the patient status for all patients and for all their visits to the hospital, but other fields in the same record allow you to match the correct status record with the correct patient and visit to the hospital.

          • 2. Re: Complicated relationships between related tables

            Thanks for the response. All of the biographical information for all patients is on one table. Likewise, I have a related table with a patient's ID # and their transplant status. However, I am wondering how I can relate patients with "transplant complete" to a table that only displays information for patients who have had a transplant and all the information that is necessary for someone who has had a transplant, since that information will not be needed for patients who get rejected for transplant. 

            Do you know if I will need to write a script to somehow only display patients with a certain status or am I digging too much into this and there is an easier way to relate this information?

            Thanks again for your help.

            • 3. Re: Complicated relationships between related tables

              Use this relationship:

              Patients::PatientID = TransplantStatus::PatientID

              If the patient is accepted for transplant, create a related record in TransplantStatus. If they are rejected, do not create the related record.

              • 4. Re: Complicated relationships between related tables

                Great - Thanks again!