My hypothetical scenario looks like this:
I have a "Car Inventory" table that has all information about the cars that we sell and have on the lot; year, make, model, color, etc.
I have a typical "Customers" table.
I have a "Transactions" table where I select a Related Customer and a Related Inventory Car for a sale.
Selecting the customer is standard. There is a drop-down from which users select a Related Customer and it's done.
When it comes to selecting the car, however, I want to be able to do this:
1. Select the Year from a drop-down. Only the years listed in my "Car Inventory" table should be displayed. Then...
2. Select the Make from a conditional drop-down. Only the makes listed in my "Car Inventory" table <<that match the year I selected above>> should be displayed. Then...
3. Select the Model from a conditional drop-down. Only the models listed in my "Car Inventory" table <<that match the year AND make I selected above>> should be displayed....
4. Select the Inventory Car from a conditional drop-down. Having made all the other selections above, I want this drop-down to show me only the cars that match those selections.
I have separate tables for Years, Makes, Models, Colors, etc, and I use those tables to build the records in my "Car Inventory" table. I have conditional drop-downs in the "Car Inventory" table as well, and when entering new records there this approach works like a charm.
However, this approach does not work in my "Transactions" table, because the conditional drop-downs do not filter to show me <<only the values that exist in my "Car Inventory" table>>; rather, they show me all the possible values from the Years table, and then all the values from the Makes table that match that year, and then all the values from the Models table that match that year and make, etc.
I need the conditional drop-downs in my "Transactions" table to show me only values that actually exist in my "Car Inventory" rather than all the available choices in my individual reference tables. Otherwise, users will be selecting combinations of year/make/models that don't even exist in our inventory, and when they get to the last drop-down (select the Inventory Car) the drop-down will be blank.
Hopes this makes sense, and hope that there is a solution for this out there.
Thanks in advance!