How to Coda with Filters
When you see ‘if this, then that’ …
Did it also happen to you that you thought you should use an If statement, while you actually needed a filter? To me it happens all the time. Take this example: if the date is in July or August, it is summer holiday, but if the date is in November, it is Autumn holiday. You first might think to create a SwitchIf() to sort this out. Let’s start with this example.
Holidays
A holiday has a start and end date. The dates in between you get via something like: Sequence(start,end) Once you see this, the filter follows:
The pattern we see time after time is this one:
- Select the table
- Add the Filter function
- Define the column that contains the list of information you need — although here we do something differently, see below — and the result is that you have all the information in this list because you write TableName.ColumnName and not ThisRow.ColumnName
- Compare this list with a value on ‘the current row’ in a column in the table your are working in via “thisRow’
- End with IfBlank(“”) to visually polish the outcome if the filter has no results.
Finding the holidays
In the above we did not look for a column that contained the list of holiday dates. There was not such a list in the table. And that is a good thing. The document runs smoother if you have less functions running. Instead of referencing a column, we calculate a value on the go. This is a good practice as you can see in our next part about larger numbers.
One more practical thing.
A filter in a column generates automatically an outcome. Maybe sometimes you want to decide manually what to show. The Select List has a formula option you can use to only bring in values that are true for the filter, see below. This one I wrapped into an If statement. If here is no holiday and thus the outcome is blank, we show ‘sorry no holiday’.
This kind of logic I apply often when setting up tables that deal with goods & services. It allows the product manager to configure the product without the risk of making mistakes since (s)he can only select matching products & services.
Larger Numbers
I was working on quality process related to the inspection of goods. The first step is to find the Lot Size we are dealing with. Left you see the filter I apply to find all the rows that fit my filter, thus are ‘true’ . The right image shows you the Row I am looking for by using the function First(). This works because the rows are already sorted. If that wouldn’t have been the case, the sorting function would have been helpful.
This example shows that a calculation on the go is efficient and even necessary. Deeper down this table we have ranges like:
Imagine that we would have asked for all the numbers in between the ‘from’ and the ‘to’ in a column, the computer would have been busy for a while and I am not even sure if a Coda document would support it, I guess not actually.
When dealing with dates, this issue was not urgent. A cell could contain about 62 days max (the summer holiday). A cell with 150.000 values is simply too much.
Our filter logic takes care of the calculation in an efficient way.
The condition
This one does not look like a filter, but it filters. We return to the holiday logic. The ones that already ended got a grey color. It went like this:
It is filter, but it does not look like. Your inner voice may say something like if the holiday is in the past, then we color it grey. Instead of applying an ‘if statement’ we write a function that outputs True() or False() like the one above. This is filter, but I admit, it is a confusing because the Coda Formula Language does not follow the pattern we described earlier and you see below.
Conclusion
what If(), Filter() and the mentioned condition have in common is that they generate values that are either True() or False(). We also picked up that when you search through a table, you better calculate. You do not always have to reference to visible outcomes like a range of dates or a range of numbers in a column. This is sometimes even impossible.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.
