Today we’re going to talk a little bit about how to filter a table or view to show only certain rows from the main table. In Coda, there are two main ways to do this. We’ll start with the simplest, and then touch on the intermediate way, which can really superpower your information!
What does Filter even mean?
Filtering a table or view is kind of like looking at a restaurant menu and deciding you actually only want dessert so you flip to the back and focus on those items. In Coda we say it a bit differently, of course. “Hey Coda, take this table and only show me the items that match a certain set of criteria.” Let’s dive into a few ways we can filter table information in Coda.
Our example table is going to be information about the age and height of some people.
Anyone can filter using the Filter Bar
Coda has done and continues to do a lot of work behind the scenes to make their product really user friendly, and work like magic! The simplest way to start filtering tables is to click on “Options” > “Filter” when you hover on your table. Often, “Filter” will be one of the choices initially as well. From here, you can look to where it says "Enable filter bar”. Notice that anyone will be able to use this without changing someone else’s view of the table. This option is great for if you have a table that lots of people will use and each one will need to see different information - they can change the table to see the most relevant information. But this is also a great place to start experimenting and better understand what a filter does.
So, toggle “Enable filter bar” on. From here you can choose to show or hide which columns the user can filter by. In this case, we’re going to have the Age and Height options for our People table. See the video below to watch all the settings you can choose from for your team, and how they can use it quickly to find the information they need!
This is how to easily enable the filter barYou choose the filter for someone else
Another way to filter a table is for you to pick what your user sees. This is useful if you want someone (or yourself) to see a filtered view of a table because they only ever need specific pieces of information. Maybe they only ever need to see the people who are between the ages of 30 and 50. So, let’s make a filtered view for them so they don’t need to constantly fiddle around with the filter bar. Watch below to see how to do it!
This is how to filter a table for all the users of the docCoda’s Filter() formula
Now let’s get into some more advanced concepts with Coda: formulas on the canvas! Now even though this might seem a bit scary, the Filter() formula is the first one I recommend beginners work with because of how powerful its function really is. Filter() allows you to connect tables, create dashboards, draw insights and answer questions from your data and so so much more. Wanna see and learn this formula ON a Coda doc and play with it yourself? → All things Filter.
Filter() has three main parts:
- The table you want to filter
- The criteria that defines what rows should be returned
- What column in the table you want (optional)
Let’s start with a formula that can filter our People table. Maybe we want to find anyone whose name is “John”. Here’s how we write that formula on the canvas. First, type the equals key and a neat little formula writer pops up. then start typing until you get the formula below:
Coda formula to filter people named JohnIn plain English, we’re saying, “Hey Coda, can you look into the People table and find anyone with the name John?” You’ll notice that Coda gives you a cool @John as a link to direct us to more information. But maybe we want to show us John’s height instead:
Now let's display the height of the previously filtered peopleYou’ll notice now it’s just his height that Coda returned for us. “Hey Coda, can you look into the People table and find anyone with the name John and then show me his height?”
But the real power happens when you use the filter formula for more in-depth information about the table in general. Let’s say we want to get an average of the heights of all the people in the table. “Hey Coda, can you look at the People table and show only the people whose age is greater than 15, and then return the average of their ages?” In Coda formula language:
Now let's filter by age greater than 15 and get the averageOk, so now let’s try a little more of an intermediate use-case if you think you’re ready. If you just want to practice a bit more, skip down to the Daily practice section.
This is how a filter with multiple conditions would look likeWhat this formula is saying in plain, human language: “Hey Coda, can you look into my Expenses table and find only the rows where the month is the same as this row’s month and the year of the expense which happened in 2022. Then can you take the amount of each expense returned and give me the average of all those?”
I’ll break it down a bit more:
1. The table you want to filter
The table being filtered is a table called “DB Expenses”. This is the first part of a filter expression, and you can see it as the green-chip above. This is a table that is cataloging business expenses where each expense has a date, amount, and description. The Filter() formula is going to look inside that table and return back to us only specific rows that match the criteria
2. The criteria
The criteria by which the table is filtered will be everything inside the parenthesis. Inside the parenthesis you can use as many Boolean operators (the ANDs and ORs) as you need to define what rows should be found. This specific formula was written on a 12-rowed table where each row represented a month.
3. What column in the table you want
This piece is optional. You can find it above as the colored chip that is chained directly after the criteria parenthesis. The formula above chose to pull out the Amount of each expense (which was a column on the expense table) so as to grab the average expenses in a month.
If you leave it out, the Filter() formula will return a list of rows.
Formulas like the one written above are what allow you to make beautiful summary tables like this one below and so so much more!
Summary table displaying totals of expenses by month in CodaMore Resources
If you want to become a Filter() formula master, there’s a lot to understand: Coda data-types, Boolean operators, principles of lists and more. There’s so much more to learn that I created a whole course on mastering Coda’s formulas, including the Filter() formula. The Coda School teaches all these concepts in depth and is taking on new students daily! Join us and get ready to master some Coda!
I also have a few videos that feature the Filter() formula for some pretty great use-cases. Check them out here:
Enjoyed learning more about Coda with this post and want to learn more? From Coda lover to Coda lover, we've selected some other interesting and useful resources we think you'll love. Check them below👇
