How to create conditional dropdown lists similar to Excel Indirect formula

Hello:

Is there a way to use a table and the values is a row to display a conditional select list?

E.g. the value selected in column A will determine the values available for selection in Column B, and the value selected in Column B will determine the values for selection in Column C?

In Excel you can create a list of top-level options, then sub-lists to display a different list of values using the indirect function. I’m looking for help on how to recreate this functionality in Coda.

In the image below, Parent would be the list of options displayed in Column A. Then O1-06 (Options) in the same row would be the selection list for column B.

Hi @John_Donaldson
and Welcome to Coda Community! :handshake:

Yes, it is possible and there are several ways, actually.

Have a look at this (over simplified) example:

You can add specific filters for every lookup:

Let me know if this goes to the right direction.

Cheers!

Hello Federico:

Thanks for the response, I may not be understanding your example so further clarification on my question below.

In your example I cannot specify the values for the second level list. And, ideally I would like multiple dependent drop downs for 2, 3, or more levels. So the use case is:

  1. The value selected in column A determines the values available for selection in column B.
  2. The value selected in column B determines the values available for selection in column C.

More Specific Examples:

List A: <–Top level items that users can choose from.

  1. Hardware
  2. Software
  3. Documentation

Conditional values determined by top-level selection I.e., which list is displayed in column B.

Hardware List<–List to display if user selects HARDWARE

  1. Keyboard
  2. Mouse
  3. USB Camera

Software List <–List to display if user selects SOFTWARE in first column, list.

  1. MS Word
  2. Coda
  3. Excel

Documentation List<–List to display if user selects DOCUMENTATION

  1. Incorrect documentation
  2. Missing documentation
  3. Update request
  4. Release Note

Example of Third Level Conditional Drop-down list

Keyboard List ← Options listed in third column select list if user selected HARDWARE in Colum1, then KEYBOARD in second column.

  1. Mechanical
  2. USB
  3. Wireless

Dear @John_Donaldson,

In the past somebody shared a sample doc in this community (sorry I don’t remember who it was), that does contain what you are looking for.

Please copy this doc and play around with it to see how to set up yours.

1 Like

@Jean_Pierre_Traets Sorry, Don’t see the ability to change the select list that is displayed in the subsequent column. I see you building the parent / child lists using group_by, but selecting Audio in category does not appear to offer the audio list in the item comlumn.

Sorry if I am missing the obvious.

OK, @John_Donaldson ,
I think i got your point.

I changed the above example with a hierarchical structure that should match with your request.

I think it should be self-explanatory, but tell me otherwise.

Cheers!

@Federico.Stefanato

Thank you, after some head scratching, I think I got your to work for my own data. Is there a way to have the filter pull from an independent table?

E.g. a filter that says if column c = “Hardware”, display the values in table Hardware, where hardware is a single-column table.?

I’m sorry… I don’t understand…
:grimacing:
Can you make (or describe) an example in your doc?

@Federico.Stefanato

Maybe a diagram will help. The current solution (1,2) consists of a two column table, where:

  1. A value in column 1, and a blank value in column B defines a parent entry.
  2. Colum A + a parrent value in Column B defines a child, grandchild, great grandchild entry.

Instead, is it possible for all children to live in a separate table where:

3 - indicates that parents Bug and Bug New reuse the same child table | list. And the solution parent has it’s own child table.

4 - Indicates a grandchild of child Best Practice

In this model the top row of the child table would not be displayed, only the rows below. The top row is the used to link between the parent and its descendant.

Hope this helps.

Hi @John_Donaldson,
sorry for my late reply…

Thanks a lot for the exhaustive - and formally flawless! - explanation :slight_smile:

I am not a fan of hybrid recursive solutions either, but they have an advantage: all the entries are of the same Type and this is why I suggested this implementation.

Based on your diagram, all the lists belong to their own table, meaning that a parent is a polymorphic object (it might be a L1: List type or L2: Solution, for instance).

On the other side of the implementation (the table where you select the filters), how can you define the column data type? (aka lookup) as it might be different according with the previous selection.

Also in a “conventional” application, you should have a chained set of ifs to query the right table or you would anyway create a, intermediate - hybrid - structure so that it’s easy to traverse the relations.
Coupling very tightly code logic and data.

So, for dynamic hierarchies I’m afraid I can’t see any other viable solution (let’s see what pops out from the community, though!)

My question is: what is the side effect of this solution for you?
What is the actual drawback that doesn’t… make you happy?

Let me know!

@Federico.Stefanato

My concern with everything in one table is that when when you have a lot of items in any level, or a lot
lists then the MOT (Mother of All Tables) can become difficult to manage or understand.

For the polymorphic approach, each list easier to understand. Since it is always a one-> many. One-Many. As long as you don’t have name collision between your links (parent → child) then a large number of lists should be easier to manage and revise. As you add levels, an entity relationship diagram can clearly illustrate where each list would appear, and what would be selected above it to get to that list.

Disclaimer: You have a deeper understanding of what is possible in Coda than I currently do. Or may see inherent pitfalls in the option I’m proposing.

Hi @John_Donaldson
I certainly see your point.
As usual, there is no a single strict way to do things right.

However, you can significantly mitigate the burden of the increasing complexity by ease handling and management of your MOT.

You know that in Coda you can play around with Views.
By taking advantage of this, I provided (in the same doc) few examples that might help in that direction, up until the very same structure of a multi tables design: feel free to extend them to your actual use-cases.

For instances, levels can have team-segregated management.

Let me know if this makes sense for you: happy to go deeper if you have a specific use-case that is not yet covered by this implementation.

Just a side note (this is my own personal opinion).
By their own nature taxonomies are dynamic. Meaning that you need to amend and maintain them with an arbitrary frequency connected to the domain.
Keeping a structure that allows you such dynamic, usually avoid some more complicated changes of implementation.
Again, this is not a rule and we can find several counter-examples. :slight_smile: