13 Replies Latest reply on Sep 15, 2011 3:33 PM by caitcreates

    Relationships/lookups linking to wrong fields

    caitcreates

      Title

      Relationships/lookups linking to wrong fields

      Post

      I am somewhat new to FMP, especially FMP 11.  I am trying to set up three databases with relationships from one database to the other two.  I am including a screenshot to my relationships.

      I have three databases:  yarn info, inventory, and people.  I am trying to link yarn info to inventory using the the relationship of Color Number to Color number.  I would like to run a lookup from inventory to yarn info which will give me a pop-up menu of color numbers from yarn info into color numbers in inventory.  Also in the inventory database, I would like to perform a lookup from the people database from the rav name field in people to the received from field of inventory.  Again, I would like inventory to have a pop-up menu of choices generated by the people database ravname field.

      I think I have that set up correctly.  In the inventory fields, I have set up the Color Number to be a lookup field (yarn info::color number).  In the same Manage Database window, I have set Received From as a lookup field (people::rav name).

      However, when I create a new record in the inventory database, neither the Color Number nor the Received from fields have pop-up lists.  They are indicated as pop-up lists, but there are no values in the lists.

      I don't know what I've done wrong.  Can anyone help?

      FMP_relationships.png

        • 1. Re: Relationships/lookups linking to wrong fields
          caitcreates

          Here is the Manage Database screen from Inventory from the previous post.

           

          Manage Database for Inventory

          • 2. Re: Relationships/lookups linking to wrong fields
            philmodjunk

            "Lookup" and "value list" are two different things and have two different purposes.

            You need to format the Inventory::color number and Inventory::Received From fields to be either pop up menus or drop down lists.

            Here's the step by step for one field and you can apply the same process to the other:

            Enter Layout Mode.

            Select the Inventory::Received From field

            Select either "drop down list" or "pop up menu" from the Control Style drop down in the Inspector's data tab.

            Click the pencil icon next to the values from menu to bring up Manage Value Lists...

            Click "New" and start a new value list. Use the specify Field button to select People::Rav Name.

            Click OK until all dialogs are dismissed.

            enter browse mode and click in this field and you should see your list of names appear.

             

            PS. Now for the more advanced concept: Names are not unique and subject to change. It's better to use a serial number field in People instead of their name. If you use both fields in this value list, you can specify the serial number in field 1 and Rav Name in field 2 so that you can select people by name, but enter their serial number into the field in inventory.

            • 3. Re: Relationships/lookups linking to wrong fields
              caitcreates

              Thanks for teaching me the difference between Lookup and Value List.  

              I have gone through each of the fields "Color Number" and "Received From" and changed them from Lookup to By Value List.  In each case, I selected the appropriate Value List in the Options -> Validation section by creating a value list from each based on the From Field in the Manage Value Lists screen.  The Color Number value list is generated from "Yarn Info::Color Number" and the rav name is generated from "people::rav name."

              In both cases, the fields are not populated.  What can I try next?

              p.s.  The rav name field is a user name on a website - it doesn't change like a first or last name and each one is unique.  I have separate fields in that database for first and last names.  Should I still use a serial number instead?

              • 4. Re: Relationships/lookups linking to wrong fields
                philmodjunk

                Please read my directions again. I did not mention the validation tab because this is not where you set up a value list. This is done through the inspector in FileMaker 11. If you have an older version, you'll have a similar feature, such as Field/Control Setup... for setting up this field format.

                The thing to keep in mind is that this is a format you apply to a field on your layout while in layout mode, not a field option you specify inside Manage | Database.

                The value list setting in validation, is a way to ensure that only values from your value list are entered in that field. (Some value list formats allow the user to enter data not listed in the value list.) So this has a different purpose.

                The rav name field is a user name on a website - it doesn't change like a first or last name and each one is unique.  I have separate fields in that database for first and last names.  Should I still use a serial number instead?

                Consider the following imaginary scenario: One day you go to that web site and the business responsible for it decides to change the names listed in some way. Now imagine what you'd have to do to make matching changes in your database. You'd have to find and update all affected people records AND all affected Inventory records. You'd have to make sure that the changes made in both tables are exactly the same so that the correct related records still match like they should. Now imaging what you would have to do if you used serial numbers for the link and just made Rav name an ordinary data field in People. Now all you have to do is update the much smaller list of records in People--and your relaitonships are unaffected by the change.

                • 5. Re: Relationships/lookups linking to wrong fields
                  caitcreates

                  I'm sorry, I didn't read your directions thoroughly.  I changed the Manage Database back so the Options/Comments columns are blank for both fields.  The Color Number field is set as a number field, the Received From field is a text field.

                  I went into Layout Mode.  In the inspector, I selected each field and clicked the Values From box.  I clicked the pencil button and added two Value Lists as pictured below.  I clicked OK until I was back to the layout screen.  I switched to Browse and looked at my record.  I still don't have a drop-down list for either one.

                  Here is the Manage Value List box for the Inventory database.

                   

                  Value List screen

                   

                  I thought I followed your directions exactly for each field.  Unfortunately, neither one is populated.

                  • 6. Re: Relationships/lookups linking to wrong fields
                    philmodjunk

                    Go to the layout where you have one of these fields.

                    Enter layout mode.

                    Click the field to select it.

                    Look at the data tab of the inspector.

                    What do you see in the Control Sytle drop down?

                    It should read "pop up menu" or "drop down list".

                    If it does, return to browse mode and click/tab into the field. You should see a list of values from the specified table appear.

                    • 7. Re: Relationships/lookups linking to wrong fields
                      caitcreates

                      I double-checked each field.  They both have "pop up menu" selected in the Control Style drop-down.

                      • 8. Re: Relationships/lookups linking to wrong fields
                        philmodjunk

                        And what do you see when you click on the field with your mouse while in browse mode?

                        • 9. Re: Relationships/lookups linking to wrong fields
                          caitcreates

                          In Browse mode, I have one record.  The field looks like a pop up list (the field has a slight shadow).  When I click on the field in that record, nothing happens -- no pop-up menu, no edit box.  I've also tried right-clicking on the field.  Again, nothing happens.

                          • 10. Re: Relationships/lookups linking to wrong fields
                            philmodjunk

                            Something's not set up right. Let's check a bit further. I'll focus on just the Recieved From field here.

                            First, when in layout mode, do you see Table: Inventory at the top of your screen, just to the right of the drop down list of layout names in the status area?

                            When you click on this field, do you see "Inventory::Received From" in the "Display data from" box in the top of the Inspector's data tab?

                            If you change to a layout for People, do you have records in this table? Are there names in the Rav Name field?

                            When you check the field definitions for both Rav Name and Received From in Manage | Database  | Fields, are both shown as fields fo type "text"?

                            • 11. Re: Relationships/lookups linking to wrong fields
                              caitcreates

                              1.  Do I see Table: Inventory at the top of your screen?  Yes

                              2.  Display Data from box? In this box, I do not see Inventory::Received From.  I see people::rav name.

                              3.  Records and data in People? Yes, I have 47 records and all of them have a rav name.

                              4. Manage Database field definitions type "text?" Yes, both the people::rav name and the inventory:: Received From fields are of type text.

                               

                              p.s. I really appreciate all of the help you're giving me here!

                              • 12. Re: Relationships/lookups linking to wrong fields
                                philmodjunk

                                #2 is the problem. This should be Inventory::Received From

                                You need to specify fields that are from Inventory, not the related tables.

                                • 13. Re: Relationships/lookups linking to wrong fields
                                  caitcreates

                                  You nailed it on the head!  I had the wrong fields from the wrong databases set.

                                  I changed the fields from people::rav name to Inventory::Received from and from Yarn Info::Color Number to Inventory::Color number.  That fixed the problem!

                                  Thank you so much for sticking with this problem and helping me solve it! :)