Can someone explain to me functional differences between using “Lookup from table” and a “Select list” where the “Selectable options” is a table?
One noticeable difference is that I can filter the available options in the “Select list”. For example, Projects.filter(Not(isArchived)), which means I don’t have to see all of my archived projects every time I select one.
Is there a way to do the above with “Lookup from table”? I’m a little confused why both exist when they appear to overlap so much. Why would I use “Lookup from table” when “Select list” has the added power of filtering to exactly what I want to see?
I read a different article on the differences and it left me with a totally different understanding - I wish I could find that article again!
I ‘thought’ the difference was that a Lookup would return a reference to the source data, whereas a Select List would fetch a copy of the source data and create a simple list, but once the user selected an option from that list, it was merely a copy of the source value, not reference (i.e. changes to the source would not cascade through other tables)
I don’t know how many others may have thought the same as me, but it might be something that needs clarifying for new users. Arguably the raison d’etre for many folks adopting Coda is the relationship management, so grasping the difference between the two is key.
I personally think that Coda has dug themselves an interesting hole here. Their ability to have formulas inside Selects has probably turned the Selects into the same thing as a Lookup. I bet behind the scenes it is identical. This isn’t really an issue, except that eventually Coda should make this a bit more clear.
Unless someone can produce a doc that shows a clear issue with this idea, I am going to go on assuming this. I have ideas for how to test the differences, but my sample docs would be a pretty shallow test. Without access to the code, it is really hard to know the limitations.
I was really hoping a Codian would reply with some real information to this thread. I understand that the community generates a lot of answers here (myself included!), but sometimes the question really needs an insider perspective.
Codians, is anyone willing to give us some details here?
I thought this for the longest time, too. My default was to prefer Lookup columns for building relational structures; however, as you also realized, Lookup columns are limited in ways that Select lists are not. My brain was locked onto the concept that Lookup columns offer referential objects, while Select lists do not.
Only recently – thanks to some other forum posts – did I realize that we can also reference objects using Select list… which now has me rethinking my preferred default column type. Yesterday in a large project I tried changing a Lookup column to a Select list column (because I wanted to be able to filter & sort the dropdown menu) and ouch!! The doc calculated for over ten minutes making the change, before I killed it and reverted to a previous version. So now, I feel kind of stuck with my existing Lookup columns.
But going forward, I’m strongly considering migrating to Select list as my preferred method of referencing objects.
I agree with everything that @Lloyd_Montgomery said, particularly this:
That’s a good question that I hadn’t thought of. I threw together a test. Fiddle with this doc and advise whether it’s doing what you want or not. (The Detail view is a View of the two source tables below.)
I guess you can do that with any column that has a lookup type filter formula which results a list. So that could be a Lookup, a Text or a Select List…
Hi all,
Nathan from Coda here. Great discussion and questions!
We understand the confusion today and are actively working to simplify the choices to make the best path for each scenario clear.
If you’re just looking up values from a table, we’d recommend using Lookup over a select list that references the table.
If you need to lookup a filtered list, for now the way to do that is with a select list. We’re working on a direct way to do this from a Lookup column.
In general, we’d also advise using a Lookup that returns a reference to the row (with the hoverable card), rather than a select list that returns the value of only a specific column. That allows you to pull additional properties from that row in other columns as needed.
For example - if you have a Tasks table with a Lookup column to Teams, you can then add a column =Teams.Location to see the location of that team in context.
If you’re interested in providing feedback on a few updates we’re working on to select list & lookup UI, I’d love to hear from you - send me a private message. Thanks!
I think this is where confusion starts when you use the term ‘reference’ (Lookup) but ‘value’ for a select list.
It really leads to the assumption that a Lookup is a pointer / reference / link to a row in another table, but that a Select is merely a value (and not a reference). In SOME cases that is true, but not in all.
Maybe it would be helpful to have something that explains which methods will create a ‘cascading’ structure (where a modification to the source data will cascade to all related tables) vs those will not cascade.
I’m a novice, but so far it seems a Lookup will always cascade, but a Select may or may not, depending on the use case.
I agree that a clarification and hopefully a more consistent experience can make this easier for a novice.
One tip is that while writing a formula, the datatype is clearly displayed as you are typing. The coloured variables have symbols next to them that display this information (see this post). Another tip is that after you have written a formula (or just selected a column type), and you are experimenting with your result, you can tell the difference by hovering over the element. The references will show a pop-up with the actual data on a small card. Simple “values” will not.
Finally, I just want to make something a bit more clear you might be confusing: Your use of the words “pointer / reference / link” are all correct conceptualisations of what is happening, but “cascade” is not. In databases, you can have “triggers” that cause actions to “cascade” across datasets to update values; however, in Coda, when you change a value in one place that has references elsewhere, there is no cascading, the values simply are different everywhere all at once. When you reference things, the data is only stored in one place, and it is just viewed in other places. For this reason, there is no cascading or changes propagating, rather, simple the same data with different views.
I think we have got their attention with this post (as well as many others across the forums) that this is something that needs to be addressed. I’m looking forward to the solution they propose and implement. Thanks for the great discussion
I agree about ‘cascade’ but this is where problems lie - ‘pointers’ are understood to a developer, but not to an accountant, or someone trying to make the transition from spreadsheet to database (or whatever Coda is!).
The database guys here probably want to have an idea of the actual implementation (maybe just out of curiousity), whilst other people will be happy with ‘Magic Cell!’ terminology - as long as they understand the behaviour of it.
Maybe ‘Magic Cell’ is not so crazy - if people grasp that it all magic cells will update via a singe change to the source of the magic
