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 💥

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

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:

Use this:

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

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.

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.

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.​

​

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!

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

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.

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.

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

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!

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.

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.

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.

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!

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.

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.

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.

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:

Try checking this first:

Then, move to this:
​

And finally, check this:

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

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.