6 Replies Latest reply on Jul 14, 2014 8:42 AM by ABuchtel

    Trouble with Lookup



      Trouble with Lookup


           Hi. I have an "Event Menus" table where I build menus by choosing items from a Products Table. The Events Menus table has as fields: "ItemGroup", "ItemNum", "Item Name". 

           I have a Products table where all the available products are, and it also has the fields: "ItemGroup", "ItemNum", "Item Name".  In the "Event Menus" table, I have the "Item Name" set to LOOKUP from the "Products" table. 

           Every Product Item is a member of one of these groups: Food, Beverage, Labor or Equipment which exist typed into a Value List. I also have a Value List defined, based on "Products by Group" table which uses "ItemNum" in the first field and "Item Name" in the second. 

           I wanted the available items to be filtered by Item Group, so I have a duplicated the "Products" table and it's named "Products by Group", and created the relationship between these 3 tables using the "Item Group" field (see attached image).

           Now: I'm in a layout based on "Event Menus", and I click the drop down list for "Item Group" (typed values), I choose any Group, like "Equipment". Then I click on the drop down list for attached to "ItemNum" (Products by Group) and see only the products for the chosen group. That work's perfectly.

           When I choose an "ItemNum", what I want to happen is for the correct "Item Name" to be looked up in the "Products" table and copied into the "Item Name" field of Event Menus. Instead, Filemaker copies the "Item Name" of the first item of the Item Group. 

           For example, within the "Products" Table, Row 9 is the first "Equipment" product: "Seating Tables (60" round)". When I build a menu and choose "Equipment", it doesn't matter what "ItemNum" I select in the "ItemNum" field, the Item Name is always set to "Seating Tables (60" round)". 

           I believe this is a problem with my relationships, but I I'm unable to see the forest for the trees. Any help is greatly appreciated. Thank you!



        • 1. Re: Trouble with Lookup

               Products should be linked directly to Event Menu by ItemNum. Then your fields that look up product data should specify a field from Products, not products by group as the "table" from which to look up that data. But your value list should remain based on Products by Group and it should stay linked as you have defined it.

               And keep in mind that data does not have to be physically copied via this relationship unless you need a separate copy of the data that won't change should you later edit the corresponding data in Products. You can also just put fields from Products on your Event Menu layout to show that related data without defining fields and copying data into them from Products.

          • 2. Re: Trouble with Lookup

                 Good morning. I made the changes you recommended, or at least I did them as I understood them. Now, I choose a group, like Equipment, and when I click on the "ItemNum" field to see the drop down of only Equipment Items, I instead get "No values defined" message. I've attached a screenshot of what is set now. Thank you very much for your help. I really appreciate it.

            • 3. Re: Trouble with Lookup

                   Doesn't look like I need to download your file.


                        I instead get "No values defined" message.

                   Then your value list would appear to list values from Products instead of from Products by Group.

                   Change your value list to list them from Products by Group and you should have things working as you need.

              • 4. Re: Trouble with Lookup

                     That's what I thought too. But I double-triple-checked. The "ItemNum" field is using the Value List called "Products - By Group" and that Value List is pulling it's data from the "Products By Group" table using the "ItemNum" and "Item Name" fields. That Value List is set to Related Values Only, because I only want to see items that are within the Group I chose for "Item Group". I've attached a screenshot of the Value List. Thank you again.

                • 5. Re: Trouble with Lookup

                       What is the "starting from" table occurrence that you've specified? It should be Event Menus.

                  • 6. Re: Trouble with Lookup

                         Oh boy. That was where the problem was. Thank you SO much!