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.
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.
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.
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,
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!
Based mainly on this post:
I came here looking for the same answer.
I think that Lookup() might be a bit dangerous since it may cause new users to satisfice with lookup rather than learning thisRow semantics which are really much simpler and more powerful.
Maybe in addition to the recommendation to use Filter the tooltip could say:
Categories.Lookup(Name, Sales)
is the same as
Categories.Filter(Name=Sales)
and the filter is easier to read
Or something like that.
I agree with @Connor_McCormick , leaning into the spreadsheet logic hides an important part of the Coda power, the thisRow logic that once undestood is a great great tool.
His suggestion is worthwhile considering à mon avis and it could be part of a follow up article on this one: The Coda Blog: Stories, insights, and the future of work to share the Coda perspective on the formula language and the use case the Coda team has in mind based on what they have (not) seen so far.
There’s one more problem with it.
When people type
Categories.Filter(Name = Sales)
what actually happens is
Categories.Filter(CurrentValue.Name = thisRow.Sales)
(or the other way around, whichever dereferences)
There’s this another problem with Coda that it hides the CurrentValue / thisRow object from end user in such expressions, teaching users to rely on intuition rather than understanding of what’s actually happening. I’ve seen lot of confusion coming out of not understanding thisRow/CurrentValue just because those people were using these without even realizing those were there.
I think that variable colouring and explanation popup are helping a lot.
Although I agree that contextual references (CurrentValue, thisRow/Table/Document) are somehow difficult concepts to catch per se, sometimes.
I don’t have a concrete proposal for this; maybe a bit fatalistic, but somehow I rely on the didactic function of the popup and on the fact that it’s part of the inevitable learning curve.
My 2c - also prefer explicit and simple. IMO it would be good to deprecate Lookup. Keeping the formula list minimal leads to less friction and confusion and understanding thisRow is critical anyway.
If there was a toggle to require the scopes thisRow., etc, would be nice. It would be nice to still be able to type out the shorthand but then the editor could expand it to the fully qualified statement.