Hi all!
My name’s Nagle. I work on research at Coda.
Do you have experience using Lookups in your docs? If you’d be up for posting here about what you’ve done with them, I’d be grateful. I’m looking for a few users to talk to in depth about some new ideas we’re exploring making it easier to make relationships between tables, and if you’re interested, please let me know at michael.nagle@coda.io
Thank you! I avoided making any jokes about our new lookup app.
Hello @nagle
I have been using Lookups as part of a personal project of running a custom fantasy football league with friends.
For example, I use a Lookup function to show which team is owned by which friend, since “Teams” are stored in one table and “Owners” are stored in another table.
In past years I’ve run this league on Google Sheets, and that requires stretching Google Sheets beyond its ideal use-cases. Hoping Coda can make some aspects of this project easier.
The lookup feature is really important for my day-to-day work and I’ve used it for a few different things:
I really like the lookups feature but it would be gerat if linked items (especially things like subtasks) would show up in a nice list instead of being squeezed into one long line.
Hey @Saskia_Bader, have you tried using the BulletedList() formula to create a list of your subtasks? Or did it not meet your needs?
I just tried figuring out lookups creating a for fun project, tracking the upcoming English Premier League soccer results. I got one lookup working correctly but to be honest the whole process is kind of confusing, and from what I can tell you can only do the lookup based on the display column? So if you wanted to do a 2nd lookup using a different column from the same table you wouldn’t be able to? You’d have to maybe create another table? I don’t know maybe I’m just not getting it but I found it fairly confusing.
Hey @David_Fields, you don’t necessarily have to use the display column in your table. You can define the column you want to compare against in the 2nd argument of the Lookup() formula, see this help article for more. The default behavior is to compare against thisRow (the display column). Hope this helps!
Sorry I was confused and still don’t know the correct terms. I was referring to Lookup Columns. So if I have 2 tables, A and B. In table A I would only be able to make 1 Lookup Column from table B (the display column). If I wanted a 2nd Lookup Column in Table A I would have to do it from a table that is NOT Table B since the Lookup Column has to be from the display column. Do I have that right?
I think I just have to spend more time with it and really think about what data should be in what table and how to properly link the tables together.
Gotcha so you’re talking about the Lookup column format. You can indeed reference other columns from Table B, in your case, where the column is not the display column. After you’ve set the Lookup column, you can reference other columns from that source table by writing a formula like this: `=[Lookup column].[Source table column name]’.
See the gif below of the Teams table (source) and the Teams 2 table where “Team Name” is a Lookup from Table column format. In the “Where is the team located?” column, I’m able to get the city of the team by writing the formula =[Team Name].Location which references the location from the Teams table.
https://cl.ly/0H2c0S2L2305/Screen%20Recording%202018-08-03%20at%2005.56%20PM.gif
thanks @Al_Chen_Coda. I got that part figured out. I was attempting (and think I finally figured out) how to recreate the table for the upcoming english premier league season (for reference - https://www.premierleague.com/tables). Only thing I have yet to crack is to figure out how to display who the next team they play is in the last column. I also can’t figure out how to do conditional formatting based on max points or trying to use .sort or .nth. Not sure if you can do conditional formats based on some of those formulas.
Can you share a screenshot of your tables so I can better understand how you are trying to show the next team the given team is playing and the conditional formatting you are trying to do? My instinct is to create a column as a select list of teams (and I assume you have a list of teams somewhere) or you can self reference the existing table since it sounds like it already contains all the unique teams.
thanks @Al_Chen_Coda for taking the time, I’m a big fan of coda and thought setting up an EPL table would be a fun way to learn coda and keep track of my favorite sports league.
So I have my League Table, which is where my list of teams is and where i’m tracking points, goals for, goals against, etc.
And then everything else is getting pulled from my Master Data cause that’s where the matches, scoring, etc is housed.
I’d love to be able to automatically pull from the Master Data the next opponent for every team on the league table but not quite sure how to get there. For example the last row visible Chelsea plays Arsenal, so ideally there’s a way to show on the League Table for Chelsea that they play Arsenal and vice versa without me having to manually select the next opponent for all 20 teams each week.
Also the conditional formatting on the league table, i’d like to highlight the top 4 teams based on points as those are the teams that make the Champions League, and also the bottom 3 teams as those are the teams who would be relegated to the lower division next season. I’ve tried various methods of max or sort or nth but I can’t seem to get it right. It often will just highlight all the teams that have any points.
I have no idea if there’s a simpler/better way to be doing this project so apologies if this is a complete mess but I’m just messing around with this and trying to learn.
To get the next team played to show up in the last column in your “League Table,” you could write a formula like this and put it in the Next column: =[Master Data].filter([Home Team]=thisRow).[Away Team]. Basically you want to filter the Master League table based on the Home Team, and return the Away Team.
For the conditional formatting, can you show the formulas you currently have in your Master Data?
Bit late to this but have definitely struggled with lookups and references/relationships between tables generally.
I think the main thing that was holding me back was not understanding how the “Lookup from table” column format worked - that an item brought over from another table this way could have any of its attributes from that other table referenced without the need to use a lookup.
Once I worked this out my experience has been much smoother - but I think that for some reason (probably that I’m a bit dense) I’ve found referencing between tables in Coda a bit unintuitive.
It would be great to get some examples of how lookups can be used effectively to get a better understanding of how lookups should/could be used.
Loving Coda generally!
@David_Fields is it possible for you to share you league table? I am currently trying to build something similar, and i have all the master data figured out.
My problem is the league table. I cannot fint out how to look up values(the winner or draw, matches, score etc.)
Can you help?
Sure, what’s the best way to share?
Share a view only link to the doc so folks can make a copy and play with it - you can do this through drive - go into advanced permissions and give “view only” access to anyone with a link and then paste the link here.
This is probably too late to comment for your research but I’ve found the lookup features very confusing and I’m almost giving up on using coda because of it. I wish there were a ton of examples. Very hard to figure out even the simplest lookup.
Update: this post really helped me: https://community.coda.io/t/lookup-from-other-table/1284/6
Are there any plans about creating lookups between different docs?
Did anyone ever find more examples of Lookups? I also can’t quite figure out how it works. The two lines in the help section are not very helpful.
