Inside this article:
Reverse lookups are a very powerful, useful tool in Coda. They allow you to connect multiple tables, summarize table data, and more. Once you get the hang of them, you might find yourself using reverse lookups all over your Coda docs!
What is a reverse lookup?
Reverse lookup is a way to formulaically pull data from one table into another using unique identifiers. Most commonly, reverse lookups exist in column formulas. The typical structure of a reverse lookup looks something like:
TableName.Filter(ColumnName.Contains(thisRow ..... )
So for instance, let’s say you have one table that has a list of tasks, their owners, and their associated projects. Then, you have a separate table for the projects themselves. And you want to know: for each project, what are the associated tasks? Enter the reverse lookup.
Reverse lookups made easy
While it’s helpful to know the logic behind reverse lookups, sometimes you just don’t have the time to create your own. Good news - we’ve built a feature that tries to guess the connection you're trying to make and guides you through the process. Here’s a brief example to explain this feature.
Side note: if you're brand new to this tool, we recommend reading through the How to set up reverse lookups section below.
We’ll use the same example data as above. We have a Tasks table, and each row has a Task Name, Owner, and Project. Project is a lookup to the Projects table.
We want to add a column to the Projects table that tells us which Tasks are associated with each Project. We’ll call this new column Associated Projects.
Start by creating a new column. Then change the column type to a lookup, and just follow the blue dots. You’ll see a blue dot next to Lookup. When you click on that column type, you can choose which table to connect to. And notice another blue dot next to Tasks > Project. This is Coda doing it’s best to guess which connection you’re wanting to make.
Once the new column is added, you’ll see that a reverse lookup column formula has automatically been created. It should look something like:
How to set up reverse lookups: an example
In the above example, you’ll note that the Project column is a lookup to a separate table, called Projects. Reverse lookups often involve lookup columns - but not always. Below, we provide some info on how to set up a reverse lookup when there isn’t a lookup column involved (see Using reverse lookups without a lookup column).
So here’s your Projects table. Note how each row in the Tasks table (above) references a row in this Projects table.
This table is admittedly pretty boring. We have the project name listed, but not much else. So what if we want to know: which tasks are associated with each of the of these projects?
1. Create a new column and add a formula
First, we’ll create a new column called Associated Tasks. Then, we’ll add a formula to this column.
2. To start the formula, call the name of the table you’ll pulling data from + the filter() formula
To write this formula, we’ll start by calling the table from which we want to pull data. In this case, that’s the Tasks table. Then, we’ll use the filter() formula.
3. Determine the unique identifier between the two tables
Next, we need to ask ourselves: what’s the unique identifier in this scenario? In other words, what is the piece of data that connects a row from one table to a row in the other table? What information will we need to use to connect this Projects table to the Tasks table? In this case, it’s the Project itself that connects the two tables. The Tasks table has a Project column, which references this Projects table.
So to continue our formula, we’ll then type the name of that column from the Tasks table that contains the unique identifier:
Notice that if we click on the Project tile in this formula, we can see more info about what this portion of the formula is referencing. In this case, Project is a column in the Tasks table. And since that’s a lookup column, this outputs a row from the Projects table. Nifty.
4. Add a formula that helps you compare data
Next, we need to tell Coda what criteria or logic to use to match up this information with the current table (Projects). Since the Project column contains rows, we’ll use the Contains() formula. Contains() is by far the most common formula to use here, but there may be times when you want to use something else. See the Troubleshooting section below for more info on this.
5. Ask yourself: what does the column contain?
Now comes an important part. Ask yourself: what does this column contain? Does it contain text? Dates? People? Row references? In this case, the Project column contains row references (remember - it’s a lookup column). That means we’ll call the handy-dandy thisRow as the next step in our formula. thisRow is used in column formulas to reference the given row.
In this example, the Project column contains entire rows from the Projects table. Therefore, we don’t need to use dot notation to specify which column within the Projects table; we’re matching against the entire row.
Now check it out - we’re almost done!
6. If needed, change the column type to match the column contents
Notice how the Associated Tasks column now contains a list of row references from the Tasks table. Pretty cool. The only thing left to do is to make sure that the column type matches the data that’s contained in the column. So since Associated Tasks contains row references, we’ll want to make sure it’s set to a lookup column. In this case, it should look up to the Tasks table. While this step isn’t always critical, it’s good hygiene and should help prevent unexpected issues down the line.
If you haven’t yet, read through the above section (How to set up reverse lookups: an example). You’ll need the context there to fully understand the below examples.
Pulling data from a specific column
So in the extended example above, we ended up pulling entire rows from the Tasks table into the Projects table. But what if we just wanted to pull data from a specific column in the Tasks table? No problem! You can use dot notation to specify which column you want.
For instance, what if we just want to see the Owner column info, so we can see all the people involved in each project? We’ll call this column Associated Participants. And we’ll use almost the exact same formula as the above example, but we’ll add the column name (Owner) at the very end.
In this example, the same person owns multiple tasks, so we’ll add a Unique() formula on to the end so that we don’t get duplicates in our results. Also be sure to change the column to a People column for best results.
Using reverse lookups without a lookup column
We almost always recommend using lookup columns in scenarios where you’d want to use a reverse lookup. But perhaps there’s some reason you aren’t using any lookup columns. In that case, your reverse lookup will be similar to the above example, but you’ll need to be a little more specific with your formula.
So let’s use the same example tables as above, but in this case the Project column of the Tasks table is not a lookup column. It’s a plain text column. So there isn’t any connection between the Projects table and the Tasks table at this point.
We’ll follow Steps 1-4 from the above walkthrough. For Step 5, we need to be more specific. Remember, the Project column is not a lookup and does not contain row references. Therefore, we can’t just use thisRow. We need to specify which column of thisRow to look for. So in this example, the formula would look like:
One important thing to note: reverse lookups are more fragile when lookup columns aren’t involved. For instance, if we were to even slightly change the text value in the Project column of the Tasks table, it would no longer match the value in the Projects table, and the reverse lookup would no longer work. That’s what makes lookups so valuable.
Adding on to your reverse lookup formula
Once you’ve got the basic reverse lookup formula set up (see “How to set up reverse lookups: an example”), you can then get fancy with it. Using dot notation, you can add other formulas from Coda’s formula language on to the end of your column formula to get exactly what you want.
Summarize your results
One very useful application of reverse lookups is the ability to summarize data. You can do this by adding on additional formulas to your basic reverse lookup.
In our example, what if we just want to know how many tasks are associated with each project? Easy! We can just add the Count() formula.
As a reminder, don’t forget to then change this column type to a Number column to match the data it contains.
Stylize your results
Another option is to use formulas to add formatting and style to the results of your reverse lookup.
For instance, you can use the BulletedList() formula to change the display of your results. In our example, maybe we want to see a bulleted list of all the associated tasks. Here’s how that formula would look:
Note that we also called the Task Name column in this example, because we want to see a bulleted list of text rather than a bulleted list of row references.
Use contains() instead of =
One common mistake is trying to use the = operator instead of the Contains() formula within the filter() portion of your reverse lookup. Here’s an example of how this would look:
While this formula may return the expected results, it can cause unexpected issues downstream. Therefore, we recommend you only use = when comparing single numeric values against single numeric values. When lists, texts, rows, and/or other data types are involved, Contains() is a much safer bet.
Data type mismatch
Another common issue that occurs in the filter() portion of the formula is comparing dissimilar data types. This can happen in many different ways.
For instance, perhaps you’re trying to compare a text value with a row reference. In the below example, the filter() formula is comparing a row value (Project) against a text value (thisRow.ProjectName). These are entirely different types of data, and therefore cannot be directly compared. This formula will not yield the expected results.
A good way to troubleshoot this is to use the information provided to you in the formula builder. If we click on the Project portion of the formula, we see that this portion outputs a row.
Then, if we click on the thisRow.Project portion, we see that this outputs a text value.
This indicates that I’m comparing two very different data types. To solve this, we can change thisRow.Project to just thisRow. We’ll then be comparing row values again row values, which will yield the expected results.
Check out this article on general formula troubleshooting for more helpful info.