What you need to setup here is a dynamic value list based on field values via a relationship set when you make your first selection.
First you system needs to have the PRODUCTs and SUB-PRODUCTs in two individual tables, with SUB-PRODUCTs related to PRODUCTs either via the ID or ProductName, For this we will say ProductName to simplify the setup but you can ammend accordingly, so each Sub-Product has a ProductName from the PRODUCT table
The you have your ORDERS table, for the purpose of this, will have a ProductName field that will be the key for your dynamic value list.
Set up a relationship between ORDERS and SUB-PRODUCTS
ProductName --=-- ProductName
And you will then setup two value lists, Products and SubProductsDynamic
To setup a value list based on a field you need to
File > Manage > Value Lists... Click "New"
Name the value list ProductList
specify Use value from field: Selecting the PRODUCTS table under Use Values from first field, and then selecting the Name field
Also make sure "Include all values" is selected
And now for the Sub-Products dynamic list, mostly the same
Name the value list SubProductsDynamic
specify Use value from field: Selecting the Orders_SUBPRODUCTS table under Use Values from first field, and then selecting the Sub-Products Name field
But instead of "Include all values" select "Include only related values starting from:" and specify ORDERS
Now attach these value lists to your fields by double clicking on them in layout mode and from the Field/Control Setup dialog select "Pop-up Menu" from Display as: and the from the options of Display value from: specify the appropriate value list you just setup.
Now in browse mode on an Order record make a selection in your ProductName field and then click into the Sub-Products name field and it should be filtered.
Please let me know if anything is unclear and needs more explanation and I will do my best.
Thanks Orlando, after reading your post, the workflow all seems to become much clearer!
I have a similar problem and your anser works just fine for showing the related values, but what if you want to use the 'lookup' feature as well? There may be information you want from the SUB-PRODUCT table to be inserted automatically in the ORDER table. How can you do it, considering the fact that the system always returns the first related value from the SUB-PRODUCT table (and not necessarily the one you choose)?
I found a solution using a portal and a set-up button, but is there a way to use a pop-up menu?
Thanks in advance.