Skip to main content

Troubleshoot your formulas

Best practices for diagnosing formula issues, including how to break down a complex formula or rewrite your formula for clarity.


Whether you're a seasoned Coda user or just getting started, encountering unexpected results or errors in your formulas can often be a common hurdle. In this article, we'll explore a few best practices to assist you in diagnosing and resolving formula-related problems. These tips aim to empower you with the knowledge and confidence necessary to tackle any formula challenges you may encounter đź’Ą

Within this article, you’ll find...


Best practices for writing successful formulas

Let's kick things off with some helpful tips for writing formulas that will set you on the right path!

Give every input a unique name

Since you will use the actual names of tables and columns in your formulas instead of cell coordinates like those in an Excel formula, it will be much easier if everything has a unique name. If you're unsure about what you're referencing, you can click on the chip in your formula to see more information about that reference.

Instead of:

16_10 (56).png
both the table and the column are titled "Tasks" đź™…


Use this:

16_10 (55).png
table and column have distinct names


Know your references and strings

Oftentimes, formulas don't produce expected results because you're comparing strings against references.

  • References: References are links to tables, columns, rows, or even people and, therefore, have all sorts of their own metadata. In the example image below, the “chips” within the Projects column are row references.

  • Strings: "String" is a fancy word for plain text. It's a set of characters, like the status of a task or the cost of an item on your grocery list. In the example below, the “Hire a magician” text in the Task Details column is a string.

reference vs. string demo.gif


Comparing strings to references is like comparing apples and oranges. While they seem similar at first glance, strings and references are entirely different data types and cannot be directly compared.

Typically, when comparing a string to a reference, you intend to compare the string with a specific column in that reference. Try being more specific in your formula and clarify which column to compare the string against.

In the example shown below, we get an Invalid comparison warning because we are trying to compare a reference (row reference from a relation column) with a string. When we change the formula to instead compare with a reference instead of a simple string, we get the correct result.

formular troubleshooting - reference vs string.gif


Similarly, in the example shown in the following gif, we start by comparing row references (in the Project relation column) with a string (”Thumbwrestling tournament”) but get no results - because you can’t compare these different data types. Instead, you can use dot notation to go a level deeper within the row reference, pulling out a specific text column (Project name) to compare against the text string. Then we get the result we expect.

new - formulas - dot notation on relation column.gif

 

Check the chip icons

Issues often arise when comparing one data type, like text, against another data type, like a number. Similarly, you might encounter problems comparing a list against a single value, regardless of the data type.

Each formula input will have an icon letting you know what data type you're working with. You can use these icons to ensure that you're pulling the correct element (e.g., column, table, row, etc.) and the correct type of data (text, number, row, etc.). Notice in the reference 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 find these icons in the headers for each column and within the various components of a formula. You can also click on any chip in a formula to see what that component consists of.​

1_1 (5).png

​

 

Find hints in the formula builder

When you start troubleshooting your formulas, it's a great idea to check out the hints the formula builder provides. They can really guide you along the way!

  • Chips vs. text - Columns, tables, canvas controls, and canvas formulas appear as “chips” (or colored bubbles) in Coda's formula language. This makes it easy to see at a glance if you're referencing a variable or if you should be seeing a string in quotation marks.

  • Chip colors - Coda color-codes chips to show where values are coming from. Chips with the same color mean those variables are from the same table.

  • Chip icons - Icons show the data type and whether the data being returned for that chip is singular or multiple. Refer to the Check the chip icon section above to learn more.

  • Highlighted parentheses - When you place your cursor next to parentheses in the formula builder, its closing or opening pair will be highlighted in yellow. This is particularly helpful in tracking down the cause of a "missing parentheses" error.

  • Cursor placement to prompt help text - Placing your cursor in or next to a formula in the formula builder will prompt help info to pop up just under the builder. You can use your arrow keys to move through the formula. As you do, parameters will be highlighted to let you know what the formula is expecting to see.

  • Check the result - As you write your formula, the formula builder will populate the result so you can check your work before committing.

new - explore the formula builder details.gif


In the example above, the details in the formula builder tell us that...

  • Tasks refers to the green highlighted table. We also see the table’s location, the number of rows, and the number of columns.

  • Status refers to a column, also in the green highlighted table. This column contains rows from the Status table.

  • Done refers to one of the rows within the Status table. This is a separate table, hence the different color of this chip.

  • Due date is also a column in the green highlighted table, and it contains datetime values.

 

 

Rewrite your formula for clarity

If you're working with a long, complex, and/or nested formula, rewriting your formula can be really helpful in tracking down errors and unexpected outputs.

Use dot notation instead of parentheses when possible

Using dot notation, or the chaining method of writing formulas, makes it easier to see what table or list is referenced and the expression being assessed. The more separation between the two, the easier it is to spot discrepancies.

When both the table to be filtered and the filter expression are inside the parentheses, it can be more difficult to visually tell where one stops and the other starts.

16_10 (58).png


Separating the two with dot notation makes it easier to separate and focus on either one individually.

16_10 (59).png

 

Indent nested formulas

For instance, an If() statement might contain formulas for each expression. Moving these to a new line and indenting them helps highlight where the If() starts and finishes.

New lines also help to separate where each expression in the If() statement starts and ends. This is also true for any formula containing nested formulas, which is quite a few!

16_10 (60).png

 

Match pairs

Some formulas have parameters that can be repeated in pairs. For these, keep them on the same line to better spot what column you're targeting and what value you're placing in it.

Not only is it easier to read, it's easier to continue to build out the formula because the pattern stands out.

16_10 (61).png

 

Move your closing parentheses to a new line

Dot notation allows us to continue tacking on more functions. If we move the closing parentheses of a formula to a new line, we can more clearly see the next formula.

16_10 (62).png


In this example, we can clearly see that the objective is to count the items returned. If the intent is to find if that count is <, >, or = to some value, that will stand out clearly, too.

16_10 (63).png

 

Use spacing around comparison operators(<, >, or =)

Adding a little room around comparison operators helps separate the values and formulas you're assessing. When everything is crunched together, it's easy for your eye to skim right past a single character operator. Adding space helps these stand out, and the larger the formula you're troubleshooting, the more this helps!
 

AND & OR logical operators use new lines

Logical operators sit in between expressions. Using new lines for each expression can help you focus on each one individually and the relationship between them.

16_10 (64).png

 

 

Break down large formulas

More often than not, large formulas are just aggregations of smaller formulas. Take advantage of that and split them up when certain parts get overwhelming.
 

Worker columns

A “worker column” is a column added to a table that calculates a smaller formula needed within a larger formula used elsewhere.

Using worker columns can help divide up the complexity by breaking things down into digestible chunks, and it can make your doc more efficient.

Formulas are part of a dependency graph, and if a value changes that the formula references, it recalculates. The more values a single formula references, the more likely it is to have to recalculate when any change is made in the doc.

Splitting up larger formulas into smaller formula columns can break up these dependencies and allow more values to be cached.
 

Check broken formulas one piece at a time

If a big formula isn’t returning the right value, check each part individually to single out the expression that needs a little more work.

If you’re starting with this:

16_10 (65).png


Try checking this first:

16_10 (66).png


Then, move to this:

16_10 (67).png


And finally, check this:

16_10 (68).png


Chances are, you’ll be able to single out the issue and more easily test the fix, as well!
 

Test table filters and disable-if formulas in a column or canvas formula

Both table filter formulas and disable-if formulas need to evaluate to true or false. It’s difficult to see if that’s happening while in the button builder, for instance.

Pulling these formulas out into a column will show the results for each row. If the results return values instead of true or false, that’s one check. The next check is whether or not the rows are returning the expected true or false result.

Not only does this make spotting an issue easier, but it also makes testing and fixing the issue easier.
 

 

FAQs

What are common mistakes when writing formulas in Coda?

Common mistakes include comparing strings to references, not using unique names for formula inputs, and mismatching data types. Checking chip icons and using dot notation can help prevent and spot these errors.

How can I improve the readability of complex formulas?

Use dot notation, indent nested formulas, match parameter pairs, move closing parentheses to new lines, add spacing around comparison operators, and use new lines for logical operators like AND & OR. Check out the Rewrite your formula for clarity section in this article to learn more.

How do I troubleshoot a formula that isn't returning the expected value?

Break down the formula into smaller parts and check each part individually. Use the formula builder hints, such as chip icons and highlighted parentheses, to identify issues. Test table filters and disable-if formulas in a separate column to evaluate true/false conditions.

My formulas are taking a long time to run, and I see a "calculating" message at the top of my doc. How can I troubleshoot this?

If you're seeing a "Calculating..." indicator in your doc, this is likely due to calculation performance issues with your formulas. To troubleshoot and resolve, check out this comprehensive article.

 

 

Related resources