1 Reply Latest reply on Aug 2, 2012 8:02 AM by Sorbsbuster

    Unable to create a relationship between tables



      Unable to create a relationship between tables


      I am using a MAC with OSX 10.6.8 and FMpro 11.0v1.  I was able to create a relationship in the tutorial example but cannot do so in my created database.  I have two tables, the first has an auto generated serial ID to be used as a unique patient identifier.  The second table will have auto generated unique sample identifiers.  I would like the patient information to link to the second table in a drop down menu.  There will be multiple samples per patient so I would like to be able to enter the patient ID in more than one entry on a layout referencing the second table to correspond to additional patient samples.  This seems so simple and having tried so many different iterations of linking the records I am at a loss.  I also tried using the patient name as a field to link the first table to the second but that did not work either.  Not having a background in programming I need help.  

        • 1. Re: Unable to create a relationship between tables

          Do not use the patient name, ever, to link tables.  Use the PatientID.  You should have two tables:

          - Patient Table (with the PatientID field just as you have)
          - Sample Table, with a SampleID (just as you have), and a PatientID.  This PatientID is a number you type in (for the meantime).

          Try this:

          - Create a few Patient Records
          - Create a few Sample records.
          - In each Sample Record, enter one of the PatientIDs you created.  Use the same ones several times.

          Create a link between the two tables using PatientID on both sides of the relationship.

          Go to a layout based on the Patient Table.  Insert a portal based on the Samples table (strictly: 'Based on the Table Occurance that links the Patient Table to the Samples Table by PatientID')

          In the portal drop the Sample ID field (and any other field you populated) from that related table.  (The 'wizard' will guide you.)

          Go back into browse mode and you will see all the samples records listed for that patient.