Filtering lookup column with select control = no results 😞

1

Ok going to need some help here. Have searched the community for an answer with no joy!

I have section which features a single select control, the contents of which are column 1 of table 1.
Table 2 uses a table lookup for column 1 table 1.

I want filter a view of table 2 based on single select of table 1 column 1 value. This doesn’t seem to work!

First table
image

Second table with lookup from first table

image

Single select control
image

Filter on view of second table using select control
image

This shows no results. I have tried using = or contains with no joy.

I presume it’s because it’s trying to match a select control with lookup results?

Any suggestions? Many thanks

Here is the sheet for reference -> https://coda.io/d/_d7XqHIIq98e

2

first comment: great description of the problem, makes it SUPER EASY to reconstruct it! thanks for that.

(turns out i was completely wrong)

3

Thanks for the quick response @Philipp_Alexander_Asbrand-Eickhoff

I tried your steps to get it to work, but even with the table 2 view deleted, and the filter set on table 2, it still shows empty.

Could you share the document you managed to get it working on? Or perhaps request access to this one? https://coda.io/d/_d7XqHIIq98e

4

to anyone else reading this:

The mistake I did was to use table 2 as a source for the control - which worked fine.

What did not work was to use table 1 as a source for the control; however, tim needs it this way.

Now the question for the coda-team would be: Bug or feature?

5

I’ve updated the Coda doc with some more real life context. The key issue as @Philipp_Alexander_Asbrand-Eickhoff mentioned is the need to use Projects as the source of the control.

image
image.png325×582 6.98 KB

6

I think you don’t get results because your filter does just not match.

In your select you have:
[Table 1].[Column 1] -> Only the column 1 of the selected row.

In the table lookup you have:
[Table 1] -> The whole row.

So the selection does not match the lookup selection.

I changed the select formula and filtered the table with “Contains”, which should work with multiple and single selects. Works like a charm. Same should work with “matches” or with just “project = projectfilter”. Does this help you/was that what you ment?

55
Bildschirmfoto 2018-07-05 um 16.40.55.png880×482 27 KB

33

3 Likes
7

@Daniel_Stieber to the rescue. This does indeed work like a charm :star_struck:

However, I don’t quite understand why [Table 1].[Column 1] doesn’t work and [Table 1] does, given there is only 1 column which matters in this table. What else is contained the the row which needs to be referenced in this case? Some hidden fields?

1 Like
8

There are many use cases, where you need the whole row (e.g. Patching together fields based on multi-select) and most of the tables have more than one column that matter.

Coda does not make a difference and can not really know, if your deadline column is important or not (and there might be hidden fields like “modifed at”, “created at” and stuff like that, but it does not really matter.)

With [Table 1].[Column 1] you select something like
"Build a house"
while with [Table 1] you select something like
{ “rowid”: 1, “Project Name”: “Build a house”, “Deadline”: 13423345145, “createdby”: “Tim_Kalic”, “link”:"/d/Tests_dhU3Fhzs99K/Filtering-with-other-row_suX9a#Projects_tuzpL/r1" }
(simplified) and probably way more.

The table lookup always goes for the whole row, that’s why the two were not matching.

9

Understood. Many thanks for your help. Always such a simple solution :slight_smile:

1 Like
10

Ah and one handy tip: If you are sometimes not sure if you have to deal with a string (text) or an object (like a row), an easy workaround is to format both to text with the “toText()” Formula. That way, both will be minified to just the visible text and can be compared again.

So in my example, I can also use toText(Project) = toText(projectfilter) in the table filter, and it will work as well.

4 Likes
11

of course it turns out to be a feature :wink:

Thanks for clearing that up daniel!

12

Can’t tell you how helpful this was and just how many times I make this mistake.

Something about it still isn’t intuitive. The other columns that were not provided are specific to that table and should have nothing to do with the table being search.

But I understand that we need to return an array or table not a single item…somehow these things are related