4 Replies Latest reply on Jun 21, 2010 1:03 PM by GregP

    Getting a Record (by key) in a Related Table



      Getting a Record (by key) in a Related Table


      I am using FileMaker Pro 11 on Windows and have the following question regarding getting a record by key in a related table.  In my database I have 2 tables:  Patients and Appointments.  These 2 tables are related via key fields (k_ID_Patient and k_ID_Appt).  They are serial number type fields (unique).


      I have created a Patient form for adding/updating patient information and an Appointment form for adding/updating appointment info.  On the Patient form, I have also added a Portal to display the Appointment records for a given Patient.  This is working fine.  What I would like to do is select an Appointment record from the Portal and then go to the selected Appointment record by retrieving the record from the Appointment table and then displaying the Appointment form (via a script).


      I have this partially working.  On the Patient form, my portal will display all Appointments for this patient.  I can then retrieve from a selected Appointment the k_ID_Appt value (in this case 59).  What is not working is how do I retrieve via a script the Appointment record with this key value and then display it in the Appointment form?


      Thanks for your help.

        • 1. Re: Getting a Record (by key) in a Related Table

          You can do this with a script that I'll post below, but Go To Related record is made for this. You can place a button in your portal row and this step can both switch you to the other layout, bring up all the records listed in the portal and make the record that you clicked in the portal the current record.


          Without GTRR, your script would look like this:


          Set Variable[$ApptID ; Value: Appointments::ApptID]

          Go To Layout [Appointments]

          Enter Find Mode[]

          Set FIeld [Apppointments::ApptID $ApptID]

          Perform Find[]


          ApptID must be a field that uniquely identifies one record in the appointment table.


          For the above use, there's no need to read further, but GTRR has many options. If you want to learn more, check out this link:

          The Complete Go To Related Record

          • 2. Re: Getting a Record (by key) in a Related Table

            That worked great - thanks!


            One other related question.  When I display my appointment data in the portal, I want this data to be read only  - prevent editing of the data.  Any easy way to do this?


            Also, in my portal I always get a new or additonal row.  Meaning, if this patient has three appointments, I get those three appointments and what looks like a blank row (a fourth record).  How can I prevent this last row?

            • 3. Re: Getting a Record (by key) in a Related Table

              To make a field read only, change its behavior so that it can only be entered in find mode. In earlier versions of filemaker, this is in Field/Control | Behavior. In filemaker 11, this is an option on the Data tab of the Inspector.


              To remove the extra blank line, Open Manage | Database | Relationships.

              Find the Relationship line linking your layout's table occurence to the portal's table occurrence and double click it.

              Clear the "Allow creation of Records via this relationship" for the Portal side of the relationship.

              • 4. Re: Getting a Record (by key) in a Related Table

                Thanks - that worked great!