Cascading Choices Leading to Price
I wonder if anyone can tell me how to set-up the following:
I have a Main table which holds all the data. I want to be able to enter all of the following data based upon a sequence of cascading choices primarily from two other tables, these two other tables for the lack of a better expression are editable dictionaries that can be updated as needed:
1. ProductsTable contains the following fields: Type, Component, Price Code, Market (Export or Domestic), Channel
2. The PriceTable contains a series of records against individual price codes/sales people: PriceCode, Price, SalesPerson, Commission (fixed)
The data I want to enter in the Main table is a choice based upon the following sequence:
1st Choice, select Market (either Export or Domestic from a picking list, the picking list is from ProductsTable::Market).
2nd Choice, select Channel (either Wholesale or Retail from the ProductsTable, this is here rather than in the PriceTable because some items due to their size or quantity are only suitable for Wholesale Channel while others, for Retail)
3rd Choice. Depending upon which channel has been selected, a range of ProductTypes suited to their channel become selectable from a drop down picking list based upon the ProductsTable::Type (e.g, Body Parts, Ignition, Engine, Cooling etc.)
4th Choice, depending upon the previous choice, a picking list from the ProductsTable::Components can be selected (so for example if Body Parts were selected, only related items would be displayed e.g door, fender, hood, or, for Ignition: coil, points, leads etc). Selection will then autofill the Price in the Main table along with all the other previous selections in their respective fields.
5th Choice of SalesPerson will then autofill Commission.
I hope this all makes sense!
Appreciate any help out there.