4 Replies Latest reply on Dec 3, 2012 8:35 AM by philmodjunk

    A couple of questions regarding relationships to help me finish up my database

    WendellNeeley

      Title

      A couple of questions regarding relationships to help me finish up my database

      Post

           I think I've finally gotten this thing mostly beaten, which is a great accomplishment for me considering my computing background.  Suddenly all of this clicked after spending many hours reading a couple of books and perusing posts here.  I still have a couple of more questions that I need to have answered to help me finish up.

      Background-  I'm creating a database to monitor what happens to the patients of my orthodontic practice after they schedule a new exam.  The primary use for this database is to determine key statistics related to the referring doctor (average patient fee, total referral fees, total patient referrals, etc.).  I have several one to many relationships that I am dealing with:

           1.  One Doctor to many Patients

           2.  One Patient to many Exams

           3.  One Exam to many Starts

      Current Situation-  I have tables for Doctors, Patient Information, New Exam Information and Start Information.  I have them relating the way I want and everything seems to be working appropriately.  I have portals set up on the doctor page to enter in all the information for the other tables and thereby create the properly related records.  Still, I need to know a couple of things to make sure that it works perfectly for the long haul.

           1.  If I have a Start for a patient that has many Exams, how does Filemaker know which Exam it should go with if I am doing this from a portal on the Doctor Table?  

           2.  Is there a way to direct this relationship without putting in the New Start information in a portal in the appropriate exam?  

            

           Thanks again, and please excuse my completely noobish questions.  

        • 1. Re: A couple of questions regarding relationships to help me finish up my database
          philmodjunk

               Here is what you should have for your relationships:

               Doctors---<Patients------<Exams----<Starts

               Doctors::__pkDoctorID = Patients::_fkDoctorID
               Patients::__pkPatientID = Exams::_fkPatientID
               Exams::__pkExamID = Starts::_fkExamID

               If this notation is unfamiliar, see: Common Forum Relationship and Field Notations Explained

               Given your description of "doing this all from the Doctors layout", I am not at all sure this is what you actually have.

               FileMaker won't know which exam links to a given record in Starts unless you first do something that puts a value in a field in the start record that matches to the primary key for a selected record in exams. The simplest solution is to use a layout based on exams to fill in information on each "start", but this is not the only way to do this. You can use supplementary relationships to the current record in Doctors with scripts and script triggers or buttons to enter this information for you from the Doctors layout.

               Here's one way:

               Define some global fields in Doctors: gSelectedPatient and gSelectedExam.

               add two more relationships to new occurrences of Exams and Starts

               SelectedStarts>-------Doctors-----<SelectedExams

               Doctors::gSelectedPatient = SelectedExams::_fkPatientID
               Doctors::gSelectedExam = SelectedStarts::_fkExamID

               You can then put portals to SelectedExams and a portal to SelectedStarts on your Doctors layout in place of any portals to Exams and Starts.

               The trick is to use script trigger performed scripts to update the values in the two global fields. Here's an example for "synching" the gSelectedPatient field:

               Select the OnObjectExit trigger for the portal to Patients and have it perform this script:

               Set Field [Doctors::gSelectedPatient ; Patients::__pkPatientID]

               Then, you can click into any row in the portal to Patients and the portal to Exams will update to show all Exam records for that patient and you can add new Exam records for that patient directly in the portal.

               You can use the same method to synch a portal to SelectedStarts each time you interact with the portal to SelectedExams.

               To add a new table occurrence:

               In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

               We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          • 2. Re: A couple of questions regarding relationships to help me finish up my database
            WendellNeeley

                 That's a great answer, and I think I understand what you are saying.  I have a question about your answer, however.  Should I set the script trigger for the New Start to the exit from the Exam?  Or can I put both script triggers in the same script with the exit from the New Patient Info?  

                 Also, if you don't mind my asking, why does creating a new table occurrence work with this?

            • 3. Re: A couple of questions regarding relationships to help me finish up my database
              WendellNeeley

                   For anyone that is interested and may have a similar issue, I have mine solved.  What I did is put the primary entry portals into the "Exam" table.  I then added the doctor information and linked to that table via a drop down menu and then used a portal to the New Exam page used the above method for the New Start page.

              • 4. Re: A couple of questions regarding relationships to help me finish up my database
                philmodjunk
                     

                          why does creating a new table occurrence work with this?

                     Using a new table occurrence allows you to create a new relationship between two tables without having to change an existing relationship. This can also be necessary to avoid getting a "circle" in your relationships graph.

                     For more on Table Occurrences and how to use them: Tutorial: What are Table Occurrences?