How to filter selectable tables

HI,
Having difficulty figuring out how to set up selectable drop menus with multiple look ups.

trying to fill out inventory table with info from other table(s) that are dependent on each other.

need to be able to start with “model year” , then “manufacturer name”, then “model name”, then “trim name”.

all these are dependent on previous selections.

as example, trying to end up with “-2018-Ford-Mustang-GT” in my inventory table but pulling and filtering from other table(s) that would be available to select from.

Thanking in advance

Hi Mario,

Welcome to the Coda Community. Glad to have you.

I am trying to work through your example, and have some questions:

  • Why do you have separate tables for the pieces of information that you are referring to?
  • Is this a hierarchical structure?
  • What additional info, if any are you storing in the various tables

Because in the end, you are going to have to build a table/ table structure where this hierarchical structure is maintained.

It would be great if you could provide an example doc…

Regards

Hi,
Thank you for the quick reply, much appreciated.

Good question, I actually have tried a couple of different ways to set up the data tables but getting confused with how to structure it.

ideally would be set up like this as a lookup reference table (this would be a large table of course as it would have to have all versiond of years / makes / models / trims available)
and then from my inventory table I would want to select from the corresponding drop menus year / make / model / trim but narrowing down the options with each selections dependent on the previous one.

Hope I am describing this correctly.

Year Make Model Trim
|2004|Toyota|Tundra|Access Cab V8 4WD|
|2004|Toyota|Tundra|Access Cab V8 4WD Limited|
|2004|Toyota|Tundra|Double Cab V8|
|2004|Toyota|Tundra|Double Cab V8 4WD|
|2004|Toyota|Tundra|Double Cab V8 4WD Limited|
|2004|Toyota|Tundra|Reg Cab V6|
|2004|Toyota|Tundra|Reg Cab V8 4WD|
|2004|Toyota|Tacoma|DoubleCab V6 Auto 4WD|
|2004|Toyota|Tacoma|DoubleCab V6 Auto PreRunner|
|2004|Toyota|Tacoma|XtraCab Auto|
|2004|Toyota|Tacoma|XtraCab Manual|
|2004|Toyota|Tacoma|XtraCab Manual 4WD|
|2004|Toyota|Tacoma|XtraCab V6 Auto 4WD|
|2004|Toyota|Tacoma|XtraCab V6 Manual 4WD|
|2004|Toyota|Sienna|4dr CE 7-Passenger|
|2004|Toyota|Sienna|4dr CE 8-Passenger|
|2004|Toyota|Sienna|4dr LE 7-Passenger|
|2004|Toyota|Sienna|4dr LE 7-Passenger 4WD|
|2004|Toyota|Sienna|4dr LE 8-Passenger|
|2004|Toyota|Sienna|4dr XLE 7-Passenger|
|2004|Toyota|Sienna|4dr XLE 7-Passenger 4WD|
|2004|Toyota|Sequoia|4dr Limited V8 4WD|
|2004|Toyota|Sequoia|4dr SR5 V8 4WD|
|2004|Toyota|RAV4|4dr Auto 4WD|
|2004|Toyota|RAV4|4dr Manual 4WD|
|2004|Toyota|Prius|5dr Hbk|
|2004|Toyota|Matrix|5dr Wgn 4WD Auto|
|2004|Toyota|Matrix|5dr Wgn Auto|
|2004|Toyota|Matrix|5dr Wgn Manual|
|2004|Toyota|Matrix|5dr Wgn XR 4WD Auto|
|2004|Toyota|Matrix|5dr Wgn XR Auto|
|2004|Toyota|Matrix|5dr Wgn XR Manual|
|2004|Toyota|Matrix|5dr Wgn XRS Manual|
|2004|Toyota|Highlander|4dr 4-Cyl|
|2004|Toyota|Highlander|4dr V6 4WD|
|2004|Toyota|Highlander|4dr V6 4WD 7-Passenger|
|2004|Toyota|Echo|3dr Hbk CE Auto|
|2004|Toyota|Echo|3dr Hbk CE Manual|
|2004|Toyota|Echo|4dr Sdn Auto|
|2004|Toyota|Echo|4dr Sdn Manual|
|2004|Toyota|Echo|5dr Hbk LE Auto|
|2004|Toyota|Echo|5dr Hbk LE Manual|
|2004|Toyota|Corolla|4dr Sdn CE Auto|
|2004|Toyota|Corolla|4dr Sdn CE Manual|
|2004|Toyota|Corolla|4dr Sdn LE Auto|
|2004|Toyota|Corolla|4dr Sdn LE Manual|
|2004|Toyota|Corolla|4dr Sdn Sport Auto|
|2004|Toyota|Corolla|4dr Sdn Sport Manual|
|2004|Toyota|Celica|3dr LB GT Auto|
|2004|Toyota|Celica|3dr LB GT Manual|
|2004|Toyota|Celica|3dr LB GTS Auto|
|2004|Toyota|Celica|3dr LB GTS Manual|
|2004|Toyota|Camry Solara|2dr Convertible SLE V6 Auto|
|2004|Toyota|Camry Solara|2dr Cpe SE Auto|
|2004|Toyota|Camry Solara|2dr Cpe SE V6 Auto|
|2004|Toyota|Camry Solara|2dr Cpe SLE V6 Auto|
|2004|Toyota|Camry|4dr Sdn LE Auto|
|2004|Toyota|Camry|4dr Sdn LE V6 Auto|
|2004|Toyota|Camry|4dr Sdn SE Auto|
|2004|Toyota|Camry|4dr Sdn SE Manual|
|2004|Toyota|Camry|4dr Sdn SE V6 Auto|
|2004|Toyota|Camry|4dr Sdn XLE Auto|
|2004|Toyota|Camry|4dr Sdn XLE V6 Auto|
|2004|Toyota|Avalon|4dr Sdn XLS|
|2004|Toyota|4Runner|4dr Limited V6 4WD|
|2004|Toyota|4Runner|4dr Limited V8 4WD|
|2004|Toyota|4Runner|4dr SR5 V6 4WD|
|2004|Toyota|4Runner|4dr SR5 V8 4WD|
|2005|Toyota|Tundra|Access Cab 128.3" V8|
|2005|Toyota|Tundra|Access Cab 128.3" V8 4WD|
|2005|Toyota|Tundra|Access Cab 128.3" V8 4WD Limited|
|2005|Toyota|Tundra|Double Cab 140.6" V8|
|2005|Toyota|Tundra|Double Cab 140.6" V8 4WD|
|2005|Toyota|Tundra|Double Cab 140.6" V8 4WD Limited|
|2005|Toyota|Tundra|Reg Cab 128.3" V6|
|2005|Toyota|Tundra|Reg Cab 128.3" V8 4WD|
|2005|Toyota|Tacoma|Access Cab 127.8" Auto|
|2005|Toyota|Tacoma|Access Cab 127.8" Manual|
|2005|Toyota|Tacoma|Access Cab 127.8" V6 Auto 4WD|
|2005|Toyota|Tacoma|Access Cab 127.8" V6 Manual 4WD|
|2005|Toyota|Tacoma|DoubleCab 127.8" V6 Manual 4WD|
|2005|Toyota|Tacoma|DoubleCab 140.5" V6 Auto 4WD|
|2005|Toyota|Tacoma|DoubleCab 140.5" V6 Auto PreRunner|
|2005|Toyota|Sienna|5dr CE 7-Passenger|
|2005|Toyota|Sienna|5dr CE 7-Passenger AWD|
|2005|Toyota|Sienna|5dr CE 8-Passenger|
|2005|Toyota|Sienna|5dr LE 7-Passenger|
|2005|Toyota|Sienna|5dr LE 7-Passenger AWD|
|2005|Toyota|Sienna|5dr LE 8-Passenger|
|2005|Toyota|Sienna|5dr XLE 7-Passenger|
|2005|Toyota|Sienna|5dr XLE LTD 7-Passenger AWD|
|2005|Toyota|Sequoia|4dr Limited V8 4WD|
|2005|Toyota|Sequoia|4dr SR5 V8 4WD|
|2005|Toyota|RAV4|4dr Auto 4WD|
|2005|Toyota|RAV4|4dr Manual 4WD|
|2005|Toyota|Prius|5dr HB|
|2005|Toyota|Matrix|5dr Wgn 4WD Auto|
|2005|Toyota|Matrix|5dr Wgn Auto|
|2005|Toyota|Matrix|5dr Wgn Manual|
|2005|Toyota|Matrix|5dr Wgn XR 4WD Auto|
|2005|Toyota|Matrix|5dr Wgn XR Auto|
|2005|Toyota|Matrix|5dr Wgn XR Manual|
|2005|Toyota|Matrix|5dr Wgn XRS 6-Spd Manual|
|2005|Toyota|Highlander|4dr 4-Cyl|
|2005|Toyota|Highlander|4dr V6 4WD|
|2005|Toyota|Highlander|4dr V6 4WD 7-Passenger|
|2005|Toyota|Echo|3dr HB CE Auto|
|2005|Toyota|Echo|3dr HB CE Manual|
|2005|Toyota|Echo|4dr Sdn Auto|
|2005|Toyota|Echo|4dr Sdn Manual|
|2005|Toyota|Echo|5dr HB LE Auto|
|2005|Toyota|Echo|5dr HB LE Manual|
|2005|Toyota|Corolla|4dr Sdn CE Auto|
|2005|Toyota|Corolla|4dr Sdn CE Manual|
|2005|Toyota|Corolla|4dr Sdn LE Auto|
|2005|Toyota|Corolla|4dr Sdn Sport Auto|
|2005|Toyota|Corolla|4dr Sdn Sport Manual|
|2005|Toyota|Corolla|4dr Sdn XRS 6-Spd Manual|
|2005|Toyota|Celica|3dr LB GT Auto|
|2005|Toyota|Celica|3dr LB GT Manual|
|2005|Toyota|Celica|3dr LB GT-S Auto|
|2005|Toyota|Celica|3dr LB GT-S Manual|
|2005|Toyota|Camry Solara|2dr Convertible SLE V6 Auto|
|2005|Toyota|Camry Solara|2dr Cpe SE Auto|
|2005|Toyota|Camry Solara|2dr Cpe SE V6 Auto|
|2005|Toyota|Camry Solara|2dr Cpe SLE V6 Auto|
|2005|Toyota|Camry|4dr Sdn LE Auto|
|2005|Toyota|Camry|4dr Sdn LE V6 Auto|
|2005|Toyota|Camry|4dr Sdn SE Auto|
|2005|Toyota|Camry|4dr Sdn SE Manual|
|2005|Toyota|Camry|4dr Sdn SE V6 Auto|
|2005|Toyota|Camry|4dr Sdn XLE V6 Auto|
|2005|Toyota|Avalon|4dr Sdn Touring|
|2005|Toyota|Avalon|4dr Sdn XLS|
|2005|Toyota|4Runner|4dr Limited V6 4WD|
|2005|Toyota|4Runner|4dr Limited V8 4WD|
|2005|Toyota|4Runner|4dr SR5 V6 4WD|
|2005|Toyota|4Runner|4dr SR5 V8 4WD|
|2006|Toyota|Yaris|3dr HB CE Auto|
|2006|Toyota|Yaris|3dr HB CE Manual|
|2006|Toyota|Yaris|3dr HB RS Auto|
|2006|Toyota|Yaris|3dr HB RS Manual|
|2006|Toyota|Yaris|5dr HB LE Auto|
|2006|Toyota|Yaris|5dr HB LE Manual|
|2006|Toyota|Yaris|5dr HB RS Auto|
|2006|Toyota|Yaris|5dr HB RS Manual|
|2006|Toyota|Tundra|Access Cab 128" V8 4WD|
|2006|Toyota|Tundra|Access Cab 128" V8 4WD Limited|
|2006|Toyota|Tundra|Double Cab 141" V8|
|2006|Toyota|Tundra|Double Cab 141" V8 4WD|
|2006|Toyota|Tundra|Double Cab 141" V8 4WD Limited|
|2006|Toyota|Tundra|Reg Cab 128" V6|
|2006|Toyota|Tundra|Reg Cab 128" V8 4WD|
|2006|Toyota|Tacoma|Access Cab 128" Auto|
|2006|Toyota|Tacoma|Access Cab 128" Manual|
|2006|Toyota|Tacoma|Access Cab 128" V6 Auto 4WD|
|2006|Toyota|Tacoma|Access Cab 128" V6 Manual 4WD|
|2006|Toyota|Tacoma|Access Cab 128" V6 Manual X-Runner|
|2006|Toyota|Tacoma|DoubleCab 128" V6 Manual 4WD|
|2006|Toyota|Tacoma|DoubleCab 141" V6 Auto 4WD|
|2006|Toyota|Tacoma|DoubleCab 141" V6 Auto PreRunner|
|2006|Toyota|Sienna|5dr CE 7-Passenger|
|2006|Toyota|Sienna|5dr CE 7-Passenger AWD|
|2006|Toyota|Sienna|5dr CE 8-Passenger|
|2006|Toyota|Sienna|5dr LE 7-Passenger|
|2006|Toyota|Sienna|5dr LE 7-Passenger AWD|
|2006|Toyota|Sienna|5dr LE 8-Passenger|
|2006|Toyota|Sienna|5dr XLE 7-Passenger|
|2006|Toyota|Sienna|5dr XLE LTD 7-Passenger AWD|
|2006|Toyota|Sequoia|4dr Limited V8 4WD|
|2006|Toyota|Sequoia|4dr SR5 V8 4WD|
|2006|Toyota|RAV4|4dr Auto 4WD Base|
|2006|Toyota|RAV4|4dr Auto 4WD Limited|
|2006|Toyota|RAV4|4dr Auto V6 4WD Base|
|2006|Toyota|RAV4|4dr Auto V6 4WD Limited|
|2006|Toyota|RAV4|4dr Auto V6 4WD Sport|
|2006|Toyota|Prius|5dr HB|
|2006|Toyota|Matrix|5dr Wgn 4WD Auto|
|2006|Toyota|Matrix|5dr Wgn XR 4WD Auto|
|2006|Toyota|Matrix|5dr Wgn XR Auto|
|2006|Toyota|Matrix|5dr Wgn XR Manual|
|2006|Toyota|Matrix|5dr Wgn XRS 6-Spd Manual|
|2006|Toyota|Matrix|5dr Wgn Auto|
|2006|Toyota|Matrix|5dr Wgn Manual|
|2006|Toyota|Highlander|4dr V6 4WD|
|2006|Toyota|Highlander|4dr V6 4WD 7-Passenger|
|2006|Toyota|Highlander|4dr V6 Hybrid 4WD|
|2006|Toyota|Highlander|4dr V6 Hybrid 4WD Limited|
|2006|Toyota|Corolla|4dr Sdn CE Auto|
|2006|Toyota|Corolla|4dr Sdn CE Manual|
|2006|Toyota|Corolla|4dr Sdn LE Auto|
|2006|Toyota|Corolla|4dr Sdn Sport Auto|
|2006|Toyota|Corolla|4dr Sdn Sport Manual|
|2006|Toyota|Camry Solara|2dr Convertible SE V6 Auto|
|2006|Toyota|Camry Solara|2dr Convertible SLE V6 Auto|
|2006|Toyota|Camry Solara|2dr Cpe SE Auto|
|2006|Toyota|Camry Solara|2dr Cpe SE V6 Auto|
|2006|Toyota|Camry Solara|2dr Cpe SLE V6 Auto|
|2006|Toyota|Camry|4dr Sdn LE Auto|
|2006|Toyota|Camry|4dr Sdn LE V6 Auto|
|2006|Toyota|Camry|4dr Sdn SE Auto|
|2006|Toyota|Camry|4dr Sdn SE Manual|
|2006|Toyota|Camry|4dr Sdn SE V6 Auto|
|2006|Toyota|Camry|4dr Sdn XLE V6 Auto|
|2006|Toyota|Avalon|4dr Sdn Touring|
|2006|Toyota|Avalon|4dr Sdn XLS|
|2006|Toyota|4Runner|4dr Limited V6 4WD|
|2006|Toyota|4Runner|4dr Limited V8 4WD|
|2006|Toyota|4Runner|4dr SR5 V6 4WD|
|2006|Toyota|4Runner|4dr SR5 V8 4WD|

Hi Mario

I did something different to what you specifically asked p because this was very simple to do :wink:
(Your hierarchical lookup is interesting, and I’ll look at that on Monday)

I have created a base table called All together, with all the details in columns. Then I concatenate() everything into a single column, and make that into the display column.

In the inventory table I then have a lookup from yeh All together table, like below:

Once you have selected an entry, the Model Year, Manufacturer, etc are filled through the related table functionality. The advantage of this way around (over and above it being extremely easy to implement) is that it is very flexible. You do not have to go step by step through the hierarchy, you can start with any characteristic, and add more as needed, until you are able to click on the one you need.

And what really impressed me, was Coda allows you to add the info out of sequence as well:
image

Regards
Piet

Hi Piet,

This is indeed a great solution however the reason for wanting it the other way is that a table like the “all together” table would be aprox 100K records and I was concerned that doing searches on so many records would be very resource intense and slow, Am I wrong to think this? I actually much prefer this and would no longer really need the drop menus as all I really need are the descriptions.

Thank you very much for you help

M.B.

HI Mario,

100k records is indeed a lot, the only way to judge would be to actually create the table and test it.

I am a bit busy at the moment, but when I have some time will get back to the puzzle.

R
P

Couldn’t resist…

I created 100k entries, and searching through that is exceptionally fast.

1 Like

You created the “all together” table with 100000 records!! Gotta love it.

Thank you very much, still would like to explore the original option if at all possible, Think it would be a great skill to learn and have the ability to use it in other cases.

Thanks again

Hi @Mario_Batista ,

I created a doc in the way you requested and imported your example data. You can add new properties like years, manufacturers etc directly in the DB Car list. In the property tables, you then can find all relations, like all models that a trim has been applied to.

In the “Structure” page, there is also a configuration “tree view”, made with grouping.

Feel free to ask if you have questions about the used structure/formulas!

1 Like

Hi,
Thank you very much, this looks like what I was asking for, will have a closer look later and try and figure how how it all works.
Much appreciated
M.B.