Several different issues here to look at:
"I created relationships between the two databases (tables) for common fields. I.e Name, Address, Phone, etc."
You should only define one relationship to match the record containing your "common fields". That one relationship should work for all such fields when you specify the looked up value option in Auto-Enter field options for them. The use of the word Relationships instead of Relationship, suggests that you've attempted to specify a separate relationship for each field and this is unecessary and won't work.
You need a field in Test1 with a matching field in Test2 that uniquely identifies the customer. Best practice is to create an auto-entered serial number field in Test2 and a number field in Test1 to match it with in your relationship. Let's call it CustomerID.
In Manage | Database | Relationships, you should create the following relationship:
Test1::CustomerID ---- = -----Test2::CustomerID
Now, on your layout, place the CustomerID field from Test 1 on it and use Field/Control... | Setup to format it as either a drop down list or pop up menu. Define its value list to display Test2::CustomerID in column 1 and Test2::CustomerName in column 2.
Now, when you click into this drop down and choose a customer, you are able to enter that customer's ID number into Test2::CustomerID to match your current record to a record for that customer in Test2.
Now you have two options for displaying name and address data from Test2:
- If you want actual copies of the name and address data stored in fields defined in Test1, use the looked up value option, specify the above relationship (Test2), and select the appropriate field from Test2 that holds the data you want to copy.
- If you don't want actual copies of the data stored in Test1, simply use the field tool to add the name and address fields from Test2 to your layout. When you select a customer in the drop down, these fields will automatically display the matching data from Test2.
How to choose between 1 and 2. If you use option 1, you are saving a "snap shot" of the contact info that was current at the time you created your record. Changes made to a customer's name and address fields will not automatically appear in Test1--you'll have to specifically perform some kind of update operation to update the data in test1. If you use option 2, all records for a given customer will automatically show the updated data when you edit that customer's contact data in the Test2 table.
I understand and use this in regards to using the IDs for each side of the relation, but my user wants to type in the name field (autofill) and have all the related fields flow in. The name field is not the field that the relation is based on. The relationship is based on CustomerIDpk and CustomerIDfk.
I've tried a drop down with CustomerID and Name showing, but the user wants to type in the customer's name in the name field for the autofill.
I'm using lookup values so I can get the customer name in the name field to fill in if they are in the database, but if I select that user, the rest of the fields do not fill in.
Any way to fix this? Thanks Steve
I'm using FileMaker Pro 12 Advanced on a Mac.