2 Replies Latest reply on Jul 8, 2012 1:01 AM by GuyStevens

    Pulling from two Tables?



      Pulling from two Tables?


      Hello Friends,


      New FMP-12 user.

      I have learned how to do an auto-complete to a field from another table BUT, can someone tell me (details please) if it's possible to do an auto-complete from more than one table?




        • 1. Re: Pulling from two Tables?

          Can you provide an example of what you are trying to do? (Perhaps there is an alternative approach that can be used.)

          Auto complete has only two options, to auto-complete from the values in a value list or to auto-complete from the index of the same field that you are entering the data into.

          But a number of auto-enter field calculations can pull data from any number of different tables.

          • 2. Re: Pulling from two Tables?

            Autocomplete is not what you are going to want to do here.

            What you want to do firstly is to make sure both tables each have their own ID field. An ID field is a number field set to auto enter a serial number.

            If you already had records in your table you need to make sure these ID's are filled in.

            Then in the table you want to link to you create a number field that stored the ID of the other table.

            For instance, in my example file below we have Patients and Admissions. A patient has an Id, An Admission has an Id as well, but also contains a Patient ID. Because every admission is for a certain patient.

            So the structure is as follows:

            Patients (table)

            ID - A number field with auto enter serial number


            ID - A number field with auto enter serial number
            AdmissionDate - A Date field
            PatientIdFk - A Number field

            This Patient IdFk (FK stands for Foreign Key) field stores the ID of the Patient. That way for instance Admission ID "14" can be linked to PatiendId "18"

            Then you need to create a relationship between these two tables in order to show Patient information on the Admissions layout.

            The relationship would be:


            Look at my example file to see how this is done.

            And you can link as many tables as you want, you only need to add IDFK fields in your Admissions table and then put the ID in that field.