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.
you have a table to store the a, b, c objects as three separate rows.
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
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:
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:
In your reference lookup, select not from rows but from the list of names filtered to be unique.
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.
I follow 1 and 2. But 3 sounds a bit to me like “and then the magic happens.”
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?