Retrieve the selectable items of select list column?

Ask the Community
#1

Is there a way to retrieve the selectable items of a select list in a formula? I know it’s possible to do so with a Lookup if the column is a lookup (and looks like a dropdown), but if the values are only defined in the select list column then I don’t see a way to do it.

My use case is wanting to have a select-list control that is automatically populated with the values from this select list column, so that I can filter the table based on the selected values in the control, and have the control values change automatically as I change the items in the select list column.

#2

Hi @loucadufault ,
I’m not sure if I correctly understood your need.

Please have a look at this and tell me if this is your desired output:

Cheers!

1 Like
#3

@loucadufault

Another possibility might be to define the values in a canvas variable where they can be accessed by the other elements: the canvas control, the column select list and the table filter.

2 Likes
#4

Yea this is what I had tried, but it is not the solution as the result is simply the unique values that have been selected in the rows. For example, although D and E are selectable items for the “Select Items” column, they do not show up in the first control as they have not been selected in any rows. So your “ALL rows” control really only list the selectable items that have been selected, not all the possible selectable items for the column.

#5

Yes that’s a good workaround. I may consider using that.

I assume there is no solution to this problem yet so I will leave the topic open.

#6

@loucadufault

I’m not following this entirely. Could you describe either the actual or a hypothetical real world example of your use case?

#7

Sure.

Say you have a Tasks table with a “Kanban” select list column. The selectable items for the “Kanban” column are “To Do”, “Doing”, and “Done”. Now say we have a control in that page called “taskFilter”, that we want to use to filter the table. The control is a select list control, with its selectable items manually set to “To Do”, “Doing”, and “Done” (i.e. the same selectable items as the “Kanban” column).

We can add a filter to our task table that only shows the rows that have their “Kanban” column matching the value of the select list control, with the formula: thisRow.Kanban.Matches([taskFilter]). Now, if we select “Doing” in our control, the table will show only those tasks where the selected item for the “Kanban” field is “Doing”.

The challenge is that if I now add a new selectable item to the “Kanban” column, say “Blocked”, I can’t filter the rows in the Tasks table that have value “Blocked” in their “Kanban” field, as it is not a selectable item in the control (essentially the control select-list is outdated). So any changes I make to the column I am filtering on will have to be manually done to the control as well to keep them in sync.

Ideally I would want to populate the selectable items in the control with the selectable items in the “Kanban” column so that the values are always the same.

#8

@loucadufault

Ok, I think I understand now.

You’re right, that can’t be done using Select list.

However, it can be done using a Lookup table, as you mentioned. See the example below comprised of tables testList3 and Table 3. I believe this meets all of your requirements, except for the fact that it uses controlled relational objects from a Lookup table rather than uncontrolled strings from a Select list.

You obviously have specific reasons for using Select list instead of a Lookup table, so you may be out of luck here.

For others who might read this in the future, I will share that I LITERALLY NEVER USE Select list, EVER, not even for throw away purposes – it promotes the injection of de-normalized data into the app, which is a big no-no for data management. Select list is basically a carry over from spreadsheets, which used them as a way to mimic Lookup tables, precisely because spreadsheets are not relational. But Coda is relational, so why ever use Select list at all? Using Select list in Coda largely defeats the point of using Coda.

In fact, I often think they should remove Select list from the app entirely because it creates more problems than it solves, and it confuses (especially for new users) one of the foundational concepts of the app, which is relational data. There is nothing that can be done with Select list that cannot be done with a Lookup table, but there are many things that can be done with Lookup tables that can’t be done with Select lists.

$.02. :grinning:

Here’s that example (testList3 & Table 3):

2 Likes
#9

I agree. And as someone who is experienced with relational databases I recognize that it’s an anti-pattern to use select lists across tables. However, I’m using the free version so there’s a limited number of tables I can use. Sometimes a select list is beneficial as it is not considered a table. This select list however is used 3 or 4 times so I may consider normalizing and create a table.

2 Likes
#10

Good point about the object count limitation on free plans.

#11

Hi @loucadufault ,
this is actually a good point.

As a workaround, you can create a single table - such as Select Lists, to be very creative - with Item and Category fields.
That way, you can have many Select Lists filtered by the Category.
You keep the relational consistency and save your tables pool.

Cheers!

1 Like