A filter formula is driving me nuts

[Pilar_Azagra]

I can’t understand why the following formula doesn’t work

let’s say that I have two tables with 2 columns. First (display) column contains strings. second column of first table contains number. What I want is to filter first table using first column of both tables and obtain the number associated with a name as a result

I have tried both .filter() and .lookup(), although I would assume that lookup would be the advisable option. I also have tried to avoid the comparison between two display columns, copying Table1.name1 into Table1.name2. Finally, I have tried to force string conversion to display columns using ToText

Formulas used in Table2:

[Table 1].Lookup(name2, thisRow.name1)

[Table 1].filter(name2 = totext(thisRow.name1))

What is wrong with both of them?

Thanks in advance!!!

[Asaf_Dafna]

Hey @Pilar_Azagra
Your filter formula is just fine (although you don’t need the ToText part), the problem is that the names in table 1 starts with blank space so it’s different from the names in table 2 and therefore don’t get filtered correctly.

About the filter vs. lookup issue, see this post:

Filter() vs Lookup() — what's the difference really?

@Paul_Danyliuk I am not entirely aware of the history on Lookup vs Filter (I will ask one of other engineer heavily involved here to see if he has context) but our official recommendation is to use Filter (I believe behind the scenes we convert Lookup to Filter anyway ) here’s a recommendation in product - agree, easy to miss, [image]

[Pilar_Azagra]

!!! you are totally right!!! THANKS a lot.

That culprit space had let me without options.

Now, given that Coda is mainly a no-code resource, such tiny details that are even less visible for no-coders greatly deserves clearer hints or expressions at formula’s editing space. Maybe to show spaces and control characters would be of great help for this long-tail situations…

[Asaf_Dafna]

You can use Trim and Lower to overcome this kind of problems.