Inside this article
The structure of a formula
Basic Math
Basic Boolean Logic
First and Last
Filter and thisRow
Best practices and guidelines
Troubleshooting your formulas
Coda's formula language has a lot to offer, and hundreds of inputs you can use as you build your doc. But, as you're getting started, or going about your daily tasks, you won't need the whole suite. If you master these top inputs, you'll be able to accomplish most daily tasks.
The structure of a formula
Before we get into the most commonly used formula inputs, there are a few important things to know about how to write formulas in Coda.
Concept 1 - Ingredients
When you write a Coda formula, you're not just limited to cells or rows. You can use any of the following objects as "ingredients":
- Tables - so you can tell Coda which part of the document you want to query to learn more about it
- Columns - so you can learn about particular attributes of your data
- Rows - so you can learn how one item connects to the other information in your doc
- References - so you can learn more about people or items in your doc
All of these ingredients are essentially lists of information. More on this later in the article!
Concept 2 - Named Objects
Everything in Coda has a name, and rather than using a coordinate (like A2:A33), you'll just use its name in the Formula:
Concept 3 - Chaining
With complex formulas in Excel, you’re required to wrap methods around “inner formulas” ー leaving you with this long block of )))))’s at the end of your formulas which can be really hard to understand. If you like that approach, Coda will support you, but you can also use chaining to help with readability.
Chaining separates steps in your formula by using a dot operator which is a fancy word for a "." - at its most basic level, chaining looks like this:
Concept 4 - Operators
When you're working with formulas, there are a few operators to be aware of:
- Not Equal To - !=
- And Then - .
Basic Math
Count ( )
Count will be your go-to formula to understand your progress on tasks, RSVPs for events, and more.
Sum ( )
You can use Sum() in a formula just like Count. To help you out, you can scroll to the bottom of any table and select n from the summarize menu.
CountUnique( )
CountUnique helps you count the number of non-duplicative items in your table.
Filter and thisRow
Filter ( )
Filter is probably the most useful formula for day-to-day work. It lets you determine which part of the table you are interested in. For example, I might want to know how many tasks in the All Tasks table are Not Started:
thisRow
In Excel, the smallest unit is a cell. Since Coda is a database at heart, the smallest unit is a row. What this buys you is the ability to take all of the contextual details of an item with you, and you can drag and drop rows and columns around as you see fit. For example, dragging and dropping tasks as their status changes.
This also means that you can use thisRow to focus your formulas. For example, I may want to see all of the Ingredients associated with a recipe:
In the formula above I am mapping ingredients from one table to recipes in another:
- Ingredients is the table with all of my ingredients
- Recipe Name is the row that holds the name of each recipe that use the ingredient
- thisRow is the name of the recipe in my recipes table
Basic Boolean Logic
Boolean Logic is a fancy term for determining whether or not the answer to your question is true or false. Your question can be simple like "is the status of this task equal to "Done?" (true), or more complex "is the status of my task equal to "Done" and assigned to me or my colleague? (false). You'll normally use AND or OR logic when filtering a table/view or when using the FILTER() formula.
AND
This function allows you to see the results for items that meet multiple requirements. For example, I might want to know any task that is Not Started AND assigned to me so I know what I need to focus on.
OR
This function lets you decide to cast a wider net if you're interested in seeing the results of a few different queries. For example, as a project manager, I want to know tasks assigned to me OR tasks that aren't started yet so I can manage the team.
Parentheses
Just like in math problems, you'll use parentheses to separate multiple AND and OR functions so that all of your calculations happen in the right order. Coda will calculate anything inside of a parentheses first.
(2*2)/(4+4) = .5
In the example above, we will multiply 2 times 2 and 4 plus 4 before doing the division.
In the same way, use parentheses to specify how multiple AND or OR functions should be grouped. By default, the formula applies the logic from left to right in the order that it appears. If you want to specify a specific order, just use parentheses.
This concept is best learned by playing with a real-life example. The first formula filters a Tasks table to show all tasks that are both assigned to Adam AND are either not started or in progress:
However, if you add in parentheses around the first two phrases (shown in red below), it will instead show all tasks that are either [both assigned to Adam and not started], OR in-progress tasks:
Did you follow that? It's a subtle change, but can make a big change in your filter. If you have questions, feel free to reach out to us - we're happy to help 😄 .
Oh, and If you're ever missing a parenthesis, Coda will let you know:
First and Last
First ( )
When you're working with a set of data, many times you're looking to pull out a specific subset of it - like the first item of a list, or the first task assigned to myself. When you're dealing with data a list doesn't necessarily need to be a literal list of text, it could be in a table, or even the details of a row.
Last ( )
Last also allows me to pull out a subset of information. Maybe I want to know that last task that was added:
Best practices and guidelines
Guideline 1 - Give every ingredient a unique name
Since you'll use the actual names of items in your formulas, it will be a lot easier if everything has a unique name. Coda helps you out by color coding items that come from the same table, but this will eliminate any confusion
Instead of:
Use this:
Guideline 2 - Know your references and strings
- References are links to tables, columns, rows , or even people and therefore have all sorts of their own metadata
- Strings are a set of characters, like the status of a task, or the cost of an item on your grocery list. It's a fancy word for plain text.
Troubleshooting your formulas
If your formula ever gives you an unexpected result or error, there are a few best practices for troubleshooting.
Tip 1 - Check the result
As you write your formula, Coda will populate the result so you can check your work before committing:
Tip 2 - Check the icons
Each input will also have an icon letting you know what type of item you're working with. Make sure that you're pulling the right element (e.g. column, table, row, etc.). Notice in the table below that if there's only one of those objects, the icon will look different than if there are multiples of that item.
You can also click on any chip to learn more about it:
Tip 3 - Check the colors
When you're working in Coda, elements that come from the same table have the same color so if you're anticipating working from two places make sure that the colors are different:
Tip 4 - Test each step
When all else fails, start creating your formula piece by piece. You'll then be able to ensure that each step is function as expected.