Dynamic select list items based on a lookup column

Hi all,

Here’s a link to the test Coda doc

Here’s what I want to do:

In the test table, I want the list column to only show a select list of the corresponding values from the reference table, for example, for the first row, it should show 1 and 2, while the second row should show 3 and 4, and the third row should show 5 and 6.

Is this possible?

Thank you

Dear @Dylan_Wu,

Thanks sharing your doc, it makes it more easy to understand your case: :trophy:

Please have a look at the embedded doc and see the formula.
I added also my approach, when:

  • A = 1, 2
  • B = 3, 4
  • C = 5, 6

Just to add them as a list

2 Likes

Agree with @Jean_Pierre_Traets, best to reorganize your data so that:

  1. you have a table to store the a, b, c objects as three separate rows.
  2. you either store 1, 2 in a multi-select column for each of those objects’ rows, or have a separate table where you store relations of one letter row -> one number entry
  3. after that you can easily aggregate.

The problem you’re having right now is that when you’re selecting a “reference”, you’re already selecting a letter-number pair, not just a letter, even if you can’t see it:
image

I.e. the row you’re selecting here already specifies one letter and one number. Which is not what you want, I guess.


But just in case this is absolutely the data structure you must keep (e.g. you’re working with some import that is not easy to reorganize), then you can do the following:

  1. In your reference lookup, select not from rows but from the list of names filtered to be unique.
  2. Construct the second column drop down to only filter for values that correspond to this name.

Again, this is not the approach I encourage, but sometimes this may be a valid requirement if reorganization is not an option.

2 Likes

I follow 1 and 2. But 3 sounds a bit to me like “and then the magic happens.” :slight_smile:

My apologies to the OP if I’m completely misunderstanding the original question, but let me try to pose it in a more concrete way that has utility for me. In the real world this could get complicated but let me try to keep it as simple as possible. Say I have two tables: PRODUCTS and ORDERS. Products contains values like

Tee Shirt
Button-up Shirt
Hoodie

and each of these has different options with regard to size, color, price. To make it simple, lets just say we’re talking only about COLOR options. So

Tee Shirt: White, Red
Button-up Shirt: White, Blue, Green
Hoodie: Gray, Black

Over in orders, my user will create a new row and select a product (say, Tee Shirt). The next column in Orders is Color, and I’d like this to be a single-select list that shows only the colors for tee shirts: White or Red. And if the product being ordered is not a tee shirt but a Hoodie, I want the select list to show only the two colors Hoodies come in: Gray or Black.

I can’t make it work. Whenever I try to create a select list by going to the Color column > Format > Select List > PRODUCTS, Coda seems to ignore the fact that I’m asking for a select list and immediately reformats this field as a Lookup. Don’t understand that. And if instead I try putting a formula into the ‘Selectable Items’ field of the Format dialog, I get what appears to be a calculated result that cannot be edited.

Seems like a classic, basic use case. What am I missing? Is the solution here a many-to-many relationship between PRODUCTS and ORDERS?

William

1 Like

Dear @William_Porter

:bulb: I recommend to study the really great sample of @Saul_Garcia

In the same way you can relate the colors to the garments as you requested.

And another source:

1 Like