Do you have this setup in Manage | database | Relationships?
Clients----<Invoices---<LineItems>-----Products (---< means one to many)
One record in invoices records one sale. LineItems in a portal on the Invoices layout is used to list the individual items sold and Products is your 'catalog' of goods and/or services you are offering for sale.
Assuming you have that structure and assuming that selecting a value for the "type" selects a specfic product... (It's possible however, to add a second conditional value list so that you can select from multiple products of the specified category and type.)
Define a category field and a type field in Products. Create separate products records with separate Product ID's for each Product Type. In your Shovel example, you'd have a product record for round shovels and a different product record with a different product ID for square shovels. Define a category field in LineItems.
In Manage | Database | relationships, make a new table occurrence of Products by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as ProductsByCategory.
We have not duplicated a table. Instead, this is a new reference to the same products table already present in your database.
Add it to your existing relationships like this:
LineItems::Category = ProductsByCategory::Category
Define your value list with the use values from field option and sepcify that ProductID from ProductsByCategory be listed in the first box and that ProductsByCategory::Type be listed for Field 2. You can specify that the values in field 1 be hidden if you wish. Then select "Include only related values starting from LineItems."
Format the LineItems::category field with a value list taken from Products::Category and format the LineItems::ProductID field with the conditional value list. You can format this field as a pop up menu and it will display the type (if you hide first field values) when you exit the field. You can also place the type field from ProductsByCategory next to the ProductID field to display the type if you use a drop down list instead of the pop up menu.
It's displaying the product types based on the product category that I choose.
Couple of issues.
1. If the Product Category I select has no product type then it continues to display product types from another Product Category (when I go to select the product type though it give me <No values defined>). How can I clear Product Type if Product Category doesn't have any associated Product Types.
2. Is it possible to select a "default" Product type based on Product Category. For example 1 particular product type is used more often than the others for a specific Product Category.
1. let me see if I picture this correctly. You have already selected values in both fields and then change the category. This will do nothing to change the value already entered into the type field. That what you mean? You can add an OnObjectSave script trigger on the category field that uses Set field to clear the type field whenever a different category is selected in the category field.
2. You can set a field to auto-enter data by opening field options in Manage | Database | fields and entering that default value into the data box on the auto-enter tab.
The OnObjectSave script now clears the Type field when a new Category is selected. Thanks
I'm still having issues with filling/pre-filling the Type field with a preselected (default) value depending on the Category Selected.
If I have 3 Categories with 3 types in each
ShovelSquare, Round, Flat
SawCircular, Table, Rotary
HammerBall-peen, Lump, Sledgehammer
When creating the user selects the Shovel Category they can then choose one of the 3 Types, what I'd like to do is pre-fill the Type field with the most common Type in this case Flat. In the case of the other Categories Saw-Table, Hammer-Sledgehammer.
Ahh, an interesting challenge. We need an auto-enter calculation that enters the default type for the specified category.
Add a number field, Default, to your Products table. You can format it as a check with with a single value check box. Click the check box to enter a 1 into the field to designate it as the default product for that category. You can resize the field so that only the check box is visible--hiding the 1.
make a new occurrence of products for yet another relationship:
LineItems::Category = DefaultProductsByCategory::Category AND
LineItems::constOne = DefaultProductsByCategory::Default
Define constOne as a calculation and enter a 1 as the sole term in its calculation.
Now use the looked up value field options for the Type field to auto-enter DefaultProductsByCategory::type into the LineItems::type field.