I don’t want to see Clair in the options in the designer column though (or Alice in the writer column). I was trying to find a way to filter the lookup results so that I only see options where [Designer?]=true.
Unsuccessful solution:
First I created a reference table that filters to just designers, and tried to lookup to that instead. No dice, you can only lookup to master tables, not reference tables it seems.
Successful solution:
Change the column format to be Select List, and set the selectable options to be “People.Filter([Writer?]=true)”.
I initially discounted this solution, assuming that while I’d successfully filter to the right people’s names, I wouldn’t get that sweet sweet hover action (where you hover on the assigned person’s name to see the linked data from the people table in a popup). I was wrong, and you do.
I think it’s because when you filter the people table to a certain set of rows, and then choose one of those rows, you’re still truly selecting a row (with all its associated data) not just text. Cool.
Thanks! I was looking for exactly this, and kept beating my head against the wall.
However, the hover action isn’t there.
It’s weird because the hovering action works just fine in the formula editing box, and the hover action also works great when you’re selecting from the drop-down list itself, but it doesn’t work when you just hover over the field.
It’s been a while since Maria’s comment, but the hover does seem to work for me by now. I’m interested in hearing from Coda themselves whether the Lookup function really works like a shortcut to the Select List function preset to the table you select? If so, this is a perfect solution (while still being a bit hard to stumble upon short of this thread).
EDIT: I didn’t search long enough; in this discussion Coda does explain how this works and some best practices…
Yeah I think the trick is that if you reference a row in the other table, you’ll get the card (e.g. People.Filter(…)). If you reference the row and then also a column in the other table, you won’t get the card (e.g. People.Filter(…).Name)
Reviving this thread as it took me a while to figure out that we have to use a Select List instead of a Lookup. This would be so much more straightforward if we could apply a filter to a Lookup.
In fact, Coda should be able to easily determine that the tables are already related (because there’s already a lookup to the parent table in the child table) and offer to apply this filter by default.
Coda needs to continue to invest in making table relationships easier to use as they are core to its business proposition. We can figure this out with some effort, but this sort of usability challenge makes it difficult to recommend Coda to coworkers.
Aha, thanks, @Ander. That’s what I was looking for.
I still contend that this should be the default behavior when the tables are already connected, but I’m glad to know it’s not quite as obfuscated as I assumed. Coda’s documentation seems to suggest that using a select list with a custom filter is the solution and could perhaps use an update:
I definitely agree that this needs to be more obvious and have better documentation around what syntax should be used to create this filter, what the return value should be, what the “globally scoped” CurrentValue is, etc.
I am using a Lookup to a GSuite Directory table named People. In the “Item Settings” filter, I have tried returning a subset of People, returning the boolean of CurrentValue.in(Managers) where managers is a formulaic subset of People, and a whole slew of other things. Nothing seems to make the list actually filter.
Thanks for the offer, @Federico_Stefanato. I ended up getting it to work by refactoring the sheet such that the lookup is more straightforward - rather than a Lookup into the People table (the entire company directory from GSuite), I’m doing a circular Lookup into the Team table and filtering using more straight-forward logic (although super unintuitive).
How does it make sense that the formula CurrentValue.Employee.In(Managers) filters the entire table, only returning those that are managers? It’s not even obvious that there’s a filter happening and there’s nothing in the Item Options menu that states or alludes to the fact that this should work.
