8 Replies Latest reply on Mar 2, 2013 7:41 AM by C.GusCristo

    Novice: Can't get simple lookup working on related table

    C.GusCristo

      Title

      Novice: Can't get simple lookup working on related table

      Post

           I set up (2) Tables:   COMPANIES & PEOPLE   (each with 2 records-- 2 people and 2 companies

           In COMPANIES I set up Company (name) and Location (for multiple location companies)

           In PEOPLE I set up personal info plus  Company and Location in a drop down list from COMPANIES

      In PEOPLE the 2 records work fine.  A drop down list appears with both company names.

      But when I attempt to add a new record to PEOPLE, the  Company  field does not respond in any way.

      I'm sure this is a function of my ignorance, but I can't find a solution.  Going mad!

            

      CGC

        • 1. Re: Novice: Can't get simple lookup working on related table
          philmodjunk

               Can you describe the relationship linking the two tables?

               Generally speaking the simplest way to link the two is like this:

               Companies::__pkCompanyID = People::_fkCompanyID

               This assumes that many people can be linked to a given company but that any one record in people can only be linked to one company.

               Then you would format _fkCompanyID on your People layout with a value list that lists __pkCompanyID in field 1 and the Company Name in Field 2. (Field 1 and Field 2 are columns in the Value List Setup dialog that appears when you create a value list with the "use values from a field" option.)

               This allows you to select a company by name, but the corresponding ID number is entered into the _fk field to link your people record to a selected Companies record. IF you then place fields for Campany name and location from the Companies table on your People layout, they will display data from the selected company record once you have selected a Company using the value list set up on _fkCompanyID.

               What I am describing is the simplest method for linking two tables. There are more sophisticated ways to set this up to produce a value list that is easier to work with when the number of values in the value list starts to become a long list of values.

          • 2. Re: Novice: Can't get simple lookup working on related table
            C.GusCristo

                 I tried to set up as you suggested but I still can't comprehend the correct setup.  I've attached a screenshot to see if you can see what I'm doing wrong.  But now, the field on the layout does not function at all.

            • 3. Re: Novice: Can't get simple lookup working on related table
              philmodjunk

                   Company would not be the field to set up as a drop down list. And note the <Table missing> error message in both your layout and Manage | value lists. This indicates that these objects currently do not link to any table occurrence in Manage | database | Relationships. Perhaps you deleted a table occurrence box from there?

              • 4. Re: Novice: Can't get simple lookup working on related table
                C.GusCristo

                     Sorry, I guess I grabbed the wrong screenshot.  I did notice the table missing and corrected that (I thought).  I've attached the later one.  You indicate that .

                     You indicate that Company would not be the field to set up as a drop down list.  Your earlier message said "This allows you to select a company by name, but the corresponding ID number is entered into the _fk field to link your people record to a selected Companies record."

                If not Company then what other field?  If the ID is field 1 and company is field 2, I thought that was your instruction.  Sorry again.  I feel completely incompetent.

                • 5. Re: Novice: Can't get simple lookup working on related table
                  philmodjunk

                       You would have two fields in Companies:

                       Companies::__pkCompanyID
                       Companies::Company

                       Company would be a text field with the company's name
                       __pkCompanyID would be an auto-entered serial number.

                       But on your layout, you format: People::_fkCompanyID as the field with the drop down list, using a value list that enters the value of __pkCompanyID into the _fkCompanyID field.

                  • 6. Re: Novice: Can't get simple lookup working on related table
                    C.GusCristo

                         I'm not quite sure I understand what you are pointing out.  The first two paragraphs are as you suggest, but where you begin from 'But on your layout. .

                         .    I see that the drop-down list is in Company not    __fkCompanyID.  And yes, I thought the point was to copy COMPANIES::__pkCompanyID  into PEOPLE:: __fkCompanyID.  

                         I'm a bit lost as to what I have wrong.

                          

                          

                    • 7. Re: Novice: Can't get simple lookup working on related table
                      philmodjunk

                           Your layout shows this: ::Company as the field name for your drop down list

                           This is the comapny field from (I assume) the Companies table. The :: indicates that it is from a related table.

                           What I am telling you to do is add the

                           _fkCompanyID field to your layout. Then use the data tab to format it as either a drop down list of pop up menu.

                           Keep the ::Company field, but format it as an edit box. (unless you use the pop up menu option and have set up the value list to only show values from the second field.In that case, you can remove the field completely.)

                           This also requires that you have linked the __pk field to the _fk field in Manage | Database | Relationships

                      • 8. Re: Novice: Can't get simple lookup working on related table
                        C.GusCristo

                             Eureka!  Thank you so much for your patience.  

                             The blinders are off; the planets have aligned and Filemaker is doing what it is supposed to do.

                             Best Regards

                             CGC