Hi everyone,
I feel like no matter how many times I do this I can’t ever quite get my head around this issue. And I think it’s key to understanding how Coda works.
It’s about cross-table referencing. I get how to reference another table via a lookup column. However, I can never quite get how to reference another column within that table, using the display column as a link in the chain. So for instance, if I have a Group table and a Person table, and I want to lookup all the names of people in a Group I can easily do that via a lookup column.
Could be a formula like this: ([People].Filter([Group].Contains(thisRow)))
However, let’s say I want to have a column in the Group table that lists all the emails of all the people in that group - and these emails are listed in the Group table. How do I do that? And is it best done via a single formula, a set of additional columns, or a whole new table?
Don’t get this one. You want to get a list of Users.Email in a Group table, but they’re already in a Group table?!
Anyway. Let’s say a User row has an Email and a Group, like it should.
Then in your Group table you can:
- Pull in all users with this group as you said,
Users.Filter(Group = thisRow), or Users.Filter(Groups.Contains(thisRow)) if a user can have multiple groups.
- Pull in emails from that column:
thisRow.Users.Email will pull in emails of all previously pulled in users into a list. That is how dereferencing a column on a “virtual table” (the filtered subset of users) works in Coda.
Or do it through a single formula: Users.Filter(Groups.Contains(thisRow)).Email. This will also return emails only of users in this group. But it’s better to split this into two steps — easier to follow.
Now, if you were pulling in a multi-select column or otherwise a list of values (e.g. if a user could have multiple groups, and you wanted to pull in all other groups that users from this group also belong to), and you wrote it like:
Users.Filter(Groups.Contains(thisRow)).Groups
you’d end up with a list of lists of Group rows. Then you’d have to .ListCombine() to flatten it, and possibly .Unique() and .Filter(CurrentValue != thisRow) to only get other groups.
Short answer: Table.Filter(...) returns a list of rows (or rather a “virtual table” that behaves a bit differently). Then [List of rows].Column returns a list of values in those rows in that column.
If the values themselves are lists, it’ll return a list of lists — something to watch out for, since it often may look just like a flat list, but you’ll be running into confusing issues with your formulas later.
P.S.
Don’t use the display column for anything else than, well, display. It’s only there so that when you’re looking at the blue lookup bubble, the text within is something meaningful, helping you identify the row. Don’t match on the display column. Don’t use .ToText() on the lookup reference. It’s a bad practice because lookup column is not meant to be stable, and if you choose to change it to something more meaningful for display eventually (e.g. not just a name, but a name and email), all your formulas depending on it will break.
I often go as far as construct a dedicated display column where I concatenate some pieces of data for convenient identification, e.g.
1 Like
Sorry, I miswrote! The emails aren’t in the group table, they belong in the user table. This is perfect, thank you.
I guess my main issue is that I don’t get why your solution works, and so I will likely not be able to apply this logic to solve other issues. I’ve read the basics of coda formulas and how dot operators work, but I still dont get why it’s as simple as thisrow.users.email and doesn’t need a more complicated filter lookup. I learned that dot operators work as [table].[part of table].[action], but here there are three parts and yet all three are about finding location and not action. You mentioned a virtual table - is that what “thisrow.users” is referencing? Is a virtual table “created” outside of view whenever I create a lookup column?
Thanks for your help!
Whenever you pull in a subset directly from another table, filtered or unfiltered, what you get is a “virtual table”. And just like you can Users.Email on the whole table, you can Users.Filter(...).Email on the part of the table, and you can store Users.Filter(...) in a column of another table, then .Email on that column. You can go as deep as you want with this.
You can reference just one row, e.g. Users.First().Email. Or do the same as Users.Email.First(). And in both cases you can first pull and store Users.First() in a cell anywhere (will be a row from Users table), or Users.Email in a cell anywhere (will be a list of text values).
It’s not always [Table].[Part of table].[Action]. That’s just a simplified piece from the tutorials. Besides, when you’re looking up a row or multiple rows into a cell of some other table, you’re already storing [Table].[Part of table] part. That’s why simply .[Action] (so to say) works.
Of course you can also do it like Users.Filter(thisRow.Users.Contains(CurrentValue)).Email, but you’ll just be calculating the subset of users for each group again. Why do that if you have already done that.
1 Like
I think I get it, thank you for the explanation. The virtual table has been “created” already by the other column formula, and that formula has done the heavy lifting so I can now do simpler queries into that virtual table…
Appreciate your help making sense of this
1 Like
