Selecting more than one option in Multiselect List empties data from formulas using the selection

1

Hi.
I’ve created a dashboard for a research I’m doing using Coda.
I have a main data table with lookup several lookup columns. I’m working along the lines of the great MEGA TRICK: Dashboard design — Two columns, smaller charts

My dashboard is called “Analysis_Table”. It has one row, several lookup columns, and a multiselect column that gets its values from another table, that aggregates data from the raw data table I’m using. The multiselect is used to filter data on the other tables, and consequently, on the dashboard.
Here is the formula for one of those tables: Main_Table.Filter([Currentvalue.type]contains([Analysis_Table.BType]))

It works great when selecting one option from the multiselect column. When choosing two options, data on the tables goes blank.
The strange thing is that it only happens when I’m using it to on another table. It works fine when I’m doing the same to filter columns in the analysis table itself: Main_Table.Filter([Currentvalue.type]contains([thisRowt.BType]))

What am I doing wrong?
I tend to think it’s not a bug.

Big thanks to all tips.

2

@Premshay_Hermon

What happens if you use this formula?

Main_Table.Filter([Currentvalue.type].Contains([Analysis_Table.BType].First()))

1 Like
3

Thanks, that worked!
I could’ve sworn I tried that myself before posting.
Do you know why it works, though?

4

@Premshay_Hermon

Expanding on this probably more than you require, in case others have this same question.

A table is a list of rows.
A table of one row is a list of one.
One row is a single object, not a list.
table.first() returns a single row object, not a list.

A multiselect field holds a list of objects (for this example, let’s assume it’s a list of row references).
A multiselect field with one item selected is a list of one.
A single select field holds a single object, not a list.
thisRow.[multiselect column].First() returns a single row reference, not a list.

[Analysis_Table].BType references the entire table, which is a list of rows, and accesses one specific column in that list of rows.

[Analysis_Table].BType.First() returns only one row, which is not a list, and also accesses the one specific column of that row.

This is Tricky :point_down:
A table (list of rows) of ONE row, with a multiselect column (list of row references) of ONE selection, is a list of one of a list of one:exploding_head:… which was working for you, but only because this is an edge case that slips through the cracks of the Contains() formula.

By appending .First() to [Analysis_Table.BType] you are reducing a list of rows (in this case a list of one row, but still a list), to just one row, a single object. So now, ONE row (not a list), with a multiselect column (list of row references), resolves to just one list of row references, which Contains() is designed to evaluate.

I’ve built some pretty serious multi-dimensional filters using a table to hold the filtering controls, just as you’ve done here with your table of one row. Except these tables have many rows. In this case, using .First() to access the control value won’t work, because there are many rows. So I tend to use @ access: @displayColumn.filterValueColumn
@ref access returns one row rather than a list of rows, so there’s no need to append .First() to reduce a list of rows to a single row object.

Clear as mud?

2 Likes
5

Wow, that’s a brilliant explanation, @Ander.
As clear as a sugar crystal.
Thanks for taking the time and giving extra tips for more complex cases.

1 Like