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

A question to Codans.

Is there any difference (performance, functional etc) between
Table1.Filter(X = thisRow.X)
and
Table1.Lookup(X, thisRow.X)?

I know that in Filter one can have more complex logic with multiple conditions etc, while in Lookup it’s only looking for a match. What I’m basically wondering is if there’s any advantage to using one over another for a simple lookup scenario.

7 Likes

Going to ask for the same…
Any Codan who could tell if there are performance (or other) advantages in ths underlying implementations?

Thanks!

And while we’re at it, is there any advantage to using
Table1.Filter(X.Contains(thisRow.X))
over just filter or lookup when it’s known that Table1.X is a single select?

That’s because the Coda UI to add column by lookup via the drop-down menu always creates the Contains() variant, even if the column is set up to be single select only. Or is it just to safeguard against improper lookup column setup?

I need to edit this every time, as this bothers me a lot.

CC @Krunal_Sheth @BenLee

mainly yes, so when (and if) source column(Users on Tasks table) turns into multi-select the other column (Tasks on Users table) doesnt need to be fixed up.

1 Like

Thanks @Krunal_Sheth! So what about the original question? What is the difference between Filter and Lookup?

Also IIRC Table1.Lookup(X, thisRow.X) would work just as well regardless if it’s a single select or multi select (UPD yes:)

So why did you still decide to use Filter(Contains()) in auto-generated formulas and not Lookup()? Is the latter somehow inferior?

@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

1 Like

Lookup() was just constructed as syntactic sugar for Filter(), esp for cases where users didn’t yet understand thisRow. It’s the same implementation underneath. At one point, we thought about going all the way and adding Vlookup(..., [column to project]) as an easy map for spreadsheet users.

Feedback welcome!

5 Likes