Inside this article:
Two kinds of lookups
In Coda, there are two primary ways you can connect tables together:
The Lookup column format
The Lookup formula
The Lookup column format
You'll use this if you want to create a dropdown menu that pulls information from another table. For example, a list of projects associated with tasks, or objectives associated with key results. To create a lookup column:
Click the add column button
Type in "lookup"
Select which table you want to pull from
If you don't see the values you expect when you click the dropdown, head to the table you're looking up from and make sure that the display column is set to the column you want. You'll know something is a display column when it has this icon:
To change the display column, click the column dropdown and choose Set as display column:
Starting with the best structure
Sometimes when you add a new column, you know right from the start that it would be best for its values to lookup into a new table.
Just click the + button to add a new column, and choose Create new table from the Lookup menu. You'll get the same options for what to name the table and where to put it, and stay right where you were. And you can add new values directly from the table you're working in.
Once you have a lookup column, it's now easy to jump to its source table when you need to see or edit its values. Just open the format column dialog, and choose Open table.
Lookup columns in Coda are full of row references. So for example, the [Assigned to] column in the Tasks table below is full of references to the Team Member table below it. You can add columns that then get properties from those references - e.g. =[Assigned To].[Phone number].
The Lookup formula
The lookup formula on the other hand, allows you to understand more about items in your tables based on information throughout your doc. For example, I may want to see a list of all the projects associated with a Goal. If you want to slice and dice your data along multiple factors, check out the filter formula.
The lookup formula has three parts:
The table you want to look at
The column you want to match
What you want to match the column to
In the example below, we want to see all of the contacts associated with the Companies:
For the table, we choose to search in the Contacts table
We want to look for matches in the Company column (which is a lookup column)
We choose to match on thisRow (which is the company name we've created a lookup to)
When to use lookup columns versus lookup formulas
Use lookup columns if:
You're getting ready to create a new table that will have one column in common with an existing table
You want a dropdown menu of an existing set of information
Use lookup formulas if:
You want to summarize information in another table
You want to look up information based on one criteria
Formulas and lookup columns
Since lookup columns pull all of the details about a row, you can actually use formulas with them by typing the column name, a dot (or period), and the value want to pull from the other table.
In the example above, we're saying to show the email address of the Contact.
And, to save you time, you don't even have to type a formula. When you create a new column, you'll see the option to create new columns based on what you already have in the table:
A common pattern is to do a reverse lookup - You may want to find out for every Team Member, which tasks are assigned to that member, or what is the total duration of the tasks they have signed up for. The Lookup formula is one key pattern for doing this (note that you can do it with the Filter formula as well).
Now the key thing to watch for is that you're either comparing references to references, or strings to strings. So in the example above, I've added 3 versions of each lookup. For example, for Tasks Assigned, the 3 patterns are:
=Tasks.Lookup([Assigned To], thisRow) → This compares references in the [Assigned To] column to "this row". Since these both refers to rows, this works.
=Tasks.Lookup([Name of Assignee], thisRow.Name) → This compares the [Name of Assignee] column in the Tasks table (which is a string), to the Name field in the current row (which is also a string). This works as well.
=Tasks.Lookup([Name of Assignee], thisRow) → This doesn't work because it's trying to compare a string from [Name of Assignee] to a row (thisRow).
Summarizing table data with lookups and thisRow
One of the most valuable ways to use the lookup formula is to summarize progress or trends in other tables. For example, you may want to know the percent complete on different projects. You'll begin by using a lookup formula to pull the total number of tasks:
Then, you'll use thisRow with filtering to populate the completed tasks. In the case below, we're using the Filter formula rather than the lookup formula because we want to cut out data along multiple lines (the project name and the status).
Notice, in the example above that we need to use thisRow in our formula. Since Coda column formulas populate the entire column, we need to add an extra bit of specificity to customize our results to just the row we're working on. The formulas above is telling Coda that we want to match information from one table to the information in each individual row.
Finally you could calculate the percent complete:
Since lookups pull all of the metadata, you may want to see all of that information in a table view. To do so, open the row by clicking the Expand row icon:
Then click the pencil icon to edit the layout:
Make sure the lookup column is visible and not in the "hidden columns" section.
Finally, toggle on Display as table: