Getting started with Coda
items, lists, sorting and filtering
In Coda tables are essential. Yes you can write on the canvas, you can create formulas on the canvas, in buttons, controllers and automations, but in the end it all comes down to tables. Tables permit to structure your data and structured data is the backbone of your data driven system. In and outside tables you deal with items, lists, sorting and filtering. I this blog I explain how these core concepts work, mainly inside tables since they are essential.
Even when you feel you are already familiar with how to Coda, it might be interesting to scan this blog. Maybe you pick up some insights.
In Coda everything is a list
I have mentioned this many times, certainly when solving specific problems. It pleased me that the Shishir (CEO Coda) applied the same logic in his speech. The video starts where he explains his view on data. Indeed the moment you rephrase to: “Lists of Data”, it becomes closer to our daily experience, less distant and it is easier to get started with Coda.
What some people do not understand is that when you have one item like a phone number or a city or a date, it is an item in a list. It is a list with only one item, but still a list. At the end we show that it is even possible to have an empty list.
Columns as lists
In the above screenshots you see what I mean when I say that a column is a list. Because when you ask for all the rows in the column Birthday Date, you get something like below. Here I did it on the canvas, but you can do the same in the table.
You literally see the list: items (the dates) separated by commas.
Row content as lists:
Since in this list all items are taken, we have per row one item. What if a row contains multiple items like in the below screenshot?
When we apply this logic on the canvas and we ask for unique values we get:
Do you see the [] ? These brackets indicate we are talking about lists. The @ on the other hand refers to the value living in the display column. The values in a display column are also referenced as objects. When you hoover over these objects, you see per object all related data living on the same row. When we talk about thisRow() we always talk about the value living in the display column and the dot between ‘thisRow’ and an other column (chaining) connects the data. thisRow.Country gives us the country living on this row.
thisTable.[First Name] results in a list with all the first names, while thisRow.[First Name] shows the first name living on this row.
Both are lists.
By the way, the AI I used decided to make the first name the display column. That is not a very smart choice, but I kept it like this.
The initial sorting
In the first screenshot you seen how I bring in the rowId(). This number corresponds with the order you see in light grey in front of the table. My main point is that data is always sorted, even when you feel like you did nothing. I call this the initial sorting. When you paste data from a spreadsheet or when you start typing on row one, no matter how you start, there is a sorting. Always. The initial sorting can be found by looking at the row Ids, they should be consecutive. Maybe a row was deleted and then you miss one, but the ‘next’ one is always a higher value in an initial sorting than the ‘previous one’.
previousRow()
Once you understand that every table is a always a sorted table, you start understanding that there not something like a previousRow() other then the row preceding a row in the defined sorting. This observation does not imply you cannot calculate with the values of rows that are sorted before and or after the row you work with. It only tells you that there is always a sorting active in your table (the initial sorting or something else).
The often used rowIndex to attribute a number to each row helping us to compare items by using logical operators (< = >) looks like the initial sorting when no rows are deleted. It then provides the same number logic as the rowId.
thisTable.Find(thisRow)However when you ask for the sorting below the display column (which are the first names) is sorted in alphabetical order and the numbers follow accordingly. You no longer have a corresponde between the rowIndex and the numbers in light grey before the table.
thisTable.Sort(True()).Find(thisRow)Table view sorting
The moment we sort the example table by department the rowIndex is no longer ‘in line’ with the numbers we see in light grey in front of the table.
To get a rowIndex that follows the table view sorting, we have to adjust manually the formula.
As always, you start with ‘thisTable’ and since this one assumes the display column as leading, you don’t have to write anything else. Next you add Sort(). This means you sort on the display column value. When you enter nothing, you get the initial sorting that corresponds with True().
Next, make use of the option to sort on a column of choice, in our case department.
thisTable.Sort(True(),sortBy:thisTable.Department).Find(thisRow)The result is a number that corresponds with the light grey number in front of the table.
The table view can thus be filtered and we can get a rowIndex aligned with this view, but only if we enter manually the function that simulates the view logic.
It would be nice would Coda create an sorted index function that follows the table view sorting. This is not too difficult, but not important to them I guess.
Filtering — the main Coda function
In my work I estimate that in 80% of the formulas I write, I need a filter. Actual we have two types of filters. First on on the view of the table and second one inside the table we use to calculate with values. When a filter ‘works’ you have filtered out something and you are left with the result.
For example we can filter the view of the table on birthday dates before the year 1999. Below how that goes.
The function applied is not a complete filter function as we are used to inside a table. What you see is the ‘inside’ of the filter already relating to the table (since it goes via the options of a table). For most beginners this is hard to understand. Simply try it and you will get used to it!
When you write the same function inside a table, you have an evaluation of the birthday date with two outcomes true or false. Sometimes this is named a filtering of thisRow, but I believe this is confusing. Instead we evaluate a value (birthday date in our example).
A filter in a table brings rows together fitting criteria inside the filter. It relates data based on the thisTable you start with. Likewise in sorting, you sorted the complete table, while in this case you filter the complete table.
The code is simple and shows a pattern:
thisTable.Filter(thisRow.[Birthday Date].Year() < 1999)You start with thisTable and from there you go. Next you mention the items of the column you want to evaluate. Written like this you may not see that the filter actually evaluates each item in the list. On the other hand when you write as below, the logic is self explanatory.
thisTable.Filter(currentvalue.[Birthday Date].Year() < 1999)It happens that the row does not fit the filter criteria, in that case we have a blank or better an empty list marked by []. As you remember from the start of this blog these [] indicate the start & end of a list and when no content is found, it shows an empty list.
Everything is a list in Coda, even when empty.
In this blog I showed a few Coda basics we will apply in what follows:
- thisRow
- currentvalue
- step 01, step 02
My name is Christiaan and blog about Coda. Since the summer of 2023 mainly about how to Coda with AI to support organisations dealing with texts and templates. My blogs are for beginners and experienced users. The central theme is that in Coda everything is a list.
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. You find my (for free) contributions to the Coda Community and on Twitter.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
