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!