A conditional value list is what you are describing here:
Custom Value List? (Use option 2)
http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (This is a knowledgebase article on the subject.)
I agree that a conditional value list would be the way to go in order to solve your issue. That way it will selectively show the certain sets of items if the specific conditions exist in order to limit down your product list.
Thank you for the quick responses. I will try this and see what happens and let you know if it works.
Okay, I've looked over the link sent about setting up the conditional value list, Option 2 and looked at my database. I have a feeling I'm making more of this than I should, but I have not been able to get it to show correctly. Here is how I have it set up currently:
Table: Products, Table: Manufacturers, Table: Order, Table: Lineitem
In my order detail layout, i have a portal set up with a field order_LINEITEM::kf_ProductID. This is a pop-up menu based on a value list from Products::__kp_ProductID and Products::z_MfgAndModelNum (so that when I click in the ProductID field, it shows a 3 digit manufacturer code and the model number). Once you select which product you want, it automatically puts in the model number in the next field in the portal.
In the order detail portion of the layout, I have a field order::_kf_MfgID which is a pop-up menu that shows the mfg ID and Mfg Name.
I'm confused as to the relationship that I need to set up between which tables to get the order_LINEITEM::_kf_ProductID to only show the products that relate to the mfg that is selected for the order. I'm very new to this stuff and I have trouble with the many-to-many relationships and the table occurences.
Also, right now, when you select the product you want for the order, it automatically fills in the model number. Is there a way to set up the model number field so that if you already know the model number, you can just type it in, basically override selecting the product ID?
I appreciate any help you can give me.
I think you have these table occurrences in Manage | Database | Relationships:
and a relationship that matches LineItems to products like this:
LineItem::kf_ProductID = Products::kp_ProductID
presumably, you have a field in products that Identifies the Manufacturer (3 digit manufacturer code?)
Products::MfgID = Manufacturers::MfgID
You can use Products::MfgID in a new relationship to LineItem for your conditional value list:
LineItem::MfgID = ProductsByMfg::MgfID
Where ProductsByMfg is a new table occurrence of Products. (Click products, then click the button with two plus signs in lower left corner, then rename the new table occurrence box "productsByMfg".)
Now you can specify that your value list will use values from ProductsByMfg and select the "Include only related values starting from LineItem" option to limit the values to just those for the selected manufacturer. LineItem::MfgID should be formatted with a value list drawn from Manufacturers that lists the ID and the name of each Manufacturer.
If "Table Occurrence" is a new term, you might want to read this thread: