5 Replies Latest reply on Feb 19, 2010 2:11 PM by philmodjunk

    Basic Relational Question



      Basic Relational Question



      I'm a relative newbie using FileMaker. I'm using FM Pro 10.0v3 on a Mac running under OS X 10.6.2


      I have begun a relational database setup which I will illustrate as follows.  A group of patients (the NAME Table) have had multiple surgeries (SURGERIES Table) and each surgery has several tables of data, ANESTHESIA being only one of them.


                                            SURGERIES                            ANESTHESIA

      NAME                                SurgNum  ----------------------- SurgNum

      PatientNum  -----------------  PatientNum                          ============

      ==========                ============                  Description

      PatientName                    DateOfSurgery                          SurgeryNum

       PatientNum                      SurgeryNum



      I need some ideas on how to quickly get from a selected patient to choose one of his surgeries (perhaps in a portal) and then jump to a selected layout of other data, such as Anesthesia.


      Thanks in advance for any suggestions.



        • 1. Re: Basic Relational Question

          A patient has single things unique to him included in Patient Table with PatientIDNumber.

          A patient can have multiple surgeries, each surgery Surgery Table has things unique to that surgery, anestheisa (gas and or intravenous?)

          A patient surgery could have multiple surgeons (Surgeons Table related to Surgery Table?)



          • 2. Re: Basic Relational Question

            David - Thanks for the prompt response


            Let me try to describe it differently:


            John Jones (Patient 14)


                 Surgery 1 - Date 2/3/2001

                      Anesthesia: Fields for Patient 1, Surgery 35


                 Surgery 2 - Date 5/6/2001

                      Anesthesia: Fields for Patient 1, Surgery 36


                 Surgery 3 - Date 8/9/2001

                      Anesthesia: Fields for Patient 1, Surgery 37


            Mary Smith (Patient 47)


                 Surgery 1 - Date 3/4/2001

                      Anesthesia: Fields for Patient 2, Surgery 86


                 Surgery 2 - Date 6/76/2001

                      Anesthesia: Fields for Patient 2, Surgery 87


            This is only the skeleton of the data structure. 


            The Name Table contains Name, Address, City, State, ZIP, Date of Birth, Phone Number, etc.


            The Surgeries Table contains, Date of Surgery, Surgeon, Location of Surgery, Hospital Name, etc.


            The Anesthesia Table contains approximately 15 fields, and there are other tables similar to the Anesthesia Table, each one related to a given surgery.


            Does this answer your question?


            My question is not about the data structure but is concerned more with the techniques used to move among the layouts.

            For instance, I would like to find a patient (I can do that) and see the surgeries (I can do that with a portal in the Patient layout).

            However I would like to click on a button associated with each surgery to select that surgery and then another button to select the Anesthesia layout or some other layout of data associated with the selected Patient and the selected Surgery. 



            • 3. Re: Basic Relational Question

              There are two basic techniques that can do this.


              You can use the data on the clicked portal row to perform a find on a different layout to find matching records in that layout's table.


              You can use Go To Related Records to use a relationship to pull up a matching group of records in another table.


              Here's a sample script for the first method:


              Set Variable [$PatientID; Value: PortalTableOccurrence:: PatientID]

              Go to Layout [Anesthesia (Anesthesia)]

              Enter Find mode[]

              Set Field [Anesthesia:: PatientID ; $PatientID]

              Perform Find[]


              That's just the skeleton of the full script but it should get you started. You can use Set Error capture [on] to keep filemaker from popping up a dialog when there are no matching records to find and you can then use Get ( LastError ) to check for error codes to see why there were no matching records just to name one enhancement.


              If you want to use Go To Related records...


              Go To Related Records is a very useful tool, but which is very poorly documented. To learn more about GTRR, click the following link:

              The Complete Go To Related Record

              • 4. Re: Basic Relational Question

                Phil - Thanks. This is just what I needed.


                I tried the first of the two methods and it works fine. The second method (using GTRR) seems a bit more complicated at first glance but I will study your paper and give it a try as well.


                Thanks again,


                • 5. Re: Basic Relational Question

                  I don't know that using GTRR has any advantage over the first option.


                  I used to use GTRR extensively back when filemaker 5.5 was state of the art. Due to the issues I documented in the other article, I find I am using scripted finds with the criteria captured in variables or passed as parameters in a lot of circumstances where I used to use GTRR.