2 Replies Latest reply on Jul 29, 2013 10:16 AM by swillette

    Can't get Lookups to Look up....



      Can't get Lookups to Look up....



      I'm using FileMaker Pro 10, trial version, Windows XP Pro, Service Pack 3.

      I want to be able to auto-populate fields from one Database (or Table) into another based on the business name I choose. (All names are 100% unique.) 

      1. I have created a master database Called TEST1 (also tried with tables) by importing from a spreadsheet the basic info for my accounts: Name, Address, City, etc.
      2. I created a second database Called TEST2 where I track items related to those accounts. Some of the data will be the same such as name, address, etc.
      3. I created relationships between the two databases (tables) for common fields. I.e Name, Address, Phone, etc.
      4. For the NAME field on TEST2 I created a Drop-down field using the entries in the NAME field from TEST1 as a value list so all of my accounts are listed. This works perfectly.

      BUT, No matter what I do I cannot get the related fields from TEST1 to populate in TEST2 when I select the account from the NAME drop down. The rest of the common fields in TEST2 remain blank.

      I have tried Lookups and Portals. I have checked the box for "Allow creation of records in this table via this relationship."  in the Relationships tab.



      Any help would be greatly appreciated.







        • 1. Re: Can't get Lookups to Look up....

          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:

          1. 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.
          2. 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.

          • 2. Re: Can't get Lookups to Look up....

                 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.