Multiple conditional drop-downs based on values from a single table.

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

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!

Photo of Juan

Juan

  • 74 Points

Posted 5 years ago

  • 0
  • 1
Interesting problem.  The only solution that I see would require manual updating each day, so I think to be practical you would need to hire a developer to give you a button to reload the required table automatically.

You will need a table of Years and their Makes populated out of the Cars table.
Then a table of Makes and their Model populated out of the Cars table.

So using native QuickBase there would be a single button to purge the data in the Year-Makes table and the Makes - Models table.

Then run a summary report of Year-Models from the Cars table and export that to the Year-model table.
Then run a summary of the Makes - Models and export that to the Makes - Models Table.

Then you would base your conditional drop down choices based off relationships to those tables so that all the weay down the selection process they only offer up valid choices which will result in at least 1 car to choose from.

I don't see any APIs that will copy the unique values from a summary report into another table, so I can't see that this will be possible using the low tech URL formula buttons which I love.  So right now I'm only seeing the option to reload those two tables manually each day or more often as cars get added and deleted, or get a programmer to write a script to reload those intermediate tables.
The problem intrigues me. I think I woke up this morning with the answer.

Since you already have the conditional drop-down tables being maintained for adding new cars into the car inventory table you should be able to take a free ride on the Those when placing an order for a sale. But the difference will be in report used for the the drop-down for placing an order at each stage of the conditional Dropdown will additionally filter the report where the number of Cars is  greater than zero.

I believe you should just be able to build some extra relationships so that each record in the conditional drop down tables you're now using now knows whether or not there are cars there based on a summary field.

I won't post this yet as an answer but I await your feedback to see if it works for you and then one or the other of us can push the button to turn this into an Answer.
Photo of Patrick

Patrick

  • 20 Points
I think QuickBaseCoach is right. I won't mark mine as an answer either, but I just tried it out and it worked  (I am working on something quite similar).

If Years have many Cars, then you can create a summary field for "# of Cars" on your Years table. On your form, use your own report for the "Year" pick list and filter it where "# of Cars" is > 0. That should take care of the year selection.

Next, since Makes have many Cars. You should be able to get the "Make - Related Year" field into you Inventory table. Then you need to make a "# of Cars" summary field on your Makes table.You need to filter this summary where the "Related Year" is equal to the value in the field "Make - Related Year". Now it's the same deal for the Make pick list: On your form, use your own report and filter it where "# of Cars" is > 0.

That should do it.