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?
Thanks,
Allyson
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:
ID - A number field with auto enter serial number
Name
...
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:
Patients::ID-----[=]------Admissions::PatientIdFk
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.
http://dl.dropbox.com/u/18099008/Demo_Files/TwoTablesLinked.fp7
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.