Learn how to use the Filter formula in Coda, a streamlined alternative to SumIF and CountIF.
If you’re only going to learn about one Coda formula, it should probably be the Filter formula. This formula is extremely powerful and useful, and allows you to work with your structured data in new ways.
The Filter formula allows you to tell Coda exactly what part of your tables you want to work with. You can use this formula in tables, directly in the canvas, in automations, and much more.
Within this article, you’ll find...
What is the Filter formula?
The Filter formula allows you to take a list of values (typically a table or a column) and then filter that down to a subset of values based on your specified criteria.
Here’s how our Formula Library breaks down the Filter formula:
A Filter formula takes two inputs:
A list of values (typically a table or view, column values, or a list from another formula output)
An expression, or a series of arguments, used to evaluate that list
The formula then outputs a list of all values (from the original list) that match the expression.
And the beauty of the Coda formula language is that you can combine the Filter formula with other formulas to do even more. Check out the section below for some examples.
ℹ️ Need a refresher on the Coda formula language? Check out this article.
Filter formula example
Here’s an example of the Filter formula in action. Let’s say you have a table full of groceries. We’ll call this table Groceries.
Get a count of rows
Let’s say we want to count how many of these items are produce items. To do so, we’ll combine the Filter formula with the Count formula. And for simplicity, we’ll do this in a canvas formula:
[Groceries].Filter(Department.Contains("Produce")).Count()
In this example, the list we're processing is the list of rows from the Groceries table and the expression we're evaluating is whether the Department column contains the word "Produce". The Count formula then counts the number of items in the resulting sublist. Therefore the formula would output the value 3, since 3 rows match this criteria.
❓ Wondering why we’re using periods between the formula components? Learn more about dot notation here.
Create a list of items
What if instead of a simple count of the matching rows, we want a list of those items? In this case, we’re wanting to pull out specific column info (the Item column) rather than the entire row. Therefore, we need to add .Item after our filter to specify which data to include in the list. Then we can simply replace the Count() part of our formula with a BulletedList() instead.
[Groceries].Filter(Department.Contains("Produce")).Item.BulletedList()
This will output a list of all three rows that match the stated criteria. It will look something like the image below:
When to use the Filter formula
The filter formula has many different uses and applications. You can use it anywhere that you can write formulas in Coda. This includes in columns, buttons, automations, reactions, canvas formulas, and more! Here are a few example use cases:
Create automations that only run when certain criteria - as specified by the Filter formula - are met. For instance, set up a daily automation that runs for each overdue task in a Task table (but ignores all other tasks).
View and analyze a subset of rows in a table. Maybe you want to count, see a list, or run other calculations on rows that meet certain criteria.
Use the Filter formula within a canvas button, so that the button takes action only on certain rows in a table. For instance, you could create a Modify Rows button that changes certain values only for rows with a particular owner.
Use the Filter formula to limit the options that show up in a relation column based on other data in the row or table. Learn more here.
Create connections between tables with the Filter formula, using a pattern called the linked relation. For example, let’s say you have two tables: a Tasks table and a Projects table. You’ve added a relation column to your Tasks table, which references the Projects table. For each row in the Tasks table, you use that relation column to indicate which Project goes with each task. But now, you want to know which Tasks are associated with each Project. This is where a linked relation would come in handy. Learn all about it here.
FAQs
Where in my doc can I use the Filter formula?
You can use the filter formula anywhere that you find the formula builder. This includes in calculated columns, in column settings, on the canvas, in automations, in buttons, and more.
What’s the difference between a linked relation and Filter formula?
A linked relation is just one way of using the Filter formula. As you can see in this article, linked relations utilize filter(). But there are plenty of other ways to use the Filter formula that aren’t linked relations.
Do I use the Filter formula to filter tables and views?
While table filtering uses the same concept as the filter formula, you won't actually have to write out the Filter() formula in order to do simple table and view filtering. If you're building a table filter in the filter builder, the Filter() portion is already being done for you. You just need to create the criteria for the filter. Learn more about filtering tables and views here.
What’s the difference between the Filter formula and the If formula?
Great question! The Filter formula and the If formula are indeed similar.
The Filter formula takes a list of values, filters the list based on criteria, and outputs a list of values that match the criteria. It’s simply a way to generate a list of values that matches specified criteria.
The If formula, on the other hand, evaluates whether a statement is true or false, and then generates actions or outputs based on the evaluation. In other words, it’s a way to tell Coda to do something (take an action, output some results, etc.) both if a criteria is met and if a criteria is not met.
How can I filter data based on multiple criteria?
Using the Filter formula, you can include as many different filtering criteria as you’d like. Within the parentheses of the Filter formula, write out each criteria, separated with either AND or OR. Use AND if you want only values that match both/all criteria, or OR if you want values that match either/any criteria. You can even use additional levels of parentheses to combine both AND and OR logic. Here’s an example:
[Tasks].Filter( Owner.Contains(”Sandy”) AND ( Priority.Contains(”High”) OR DueDate=Today()) )
This formula will return all rows from the Tasks table where 1) Sandy is the owner, and 2) the priority is high and/or the due date is today.
Why is my filter formula not showing the results that I expect?
This can happen for a number of reasons. Here are a few troubleshooting ideas:
If you’re using = to evaluate text values (ex: Status = Done ), try switching to Contains() instead (ex: Status.Contains(”Done”) ). Contains is a more reliable formula when evaluating text.
If you’re including multiple criteria in your filter, make sure you’re using ANDs, ORs, and parentheses correctly. You can click on any single parenthesis in the formula builder, and the corresponding parenthesis (the other part of the pair) will be highlighted. This can help you understand what each set of parentheses is including.
When in doubt, copy the formula info a canvas formula. Then remove any additional formulas from the end of your Filter() formula. This will show you the list of values that your Filter() is outputting, and you can troubleshoot from there.
If you need more troubleshooting tips, check out this article.