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

    Basic Relational Question

    BobCutler

      Title

      Basic Relational Question

      Post

       

      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

                                             PatientNum

       

      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.

       

      Bob

        • 1. Re: Basic Relational Question
          davidanders
            

          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?)

          etc? 

           

          • 2. Re: Basic Relational Question
            BobCutler
              

            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. 

             

            Bob

            • 3. Re: Basic Relational Question
              philmodjunk
                

              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
                BobCutler
                  

                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,

                Bob 

                • 5. Re: Basic Relational Question
                  philmodjunk
                    

                  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.