Coda.io and Previous Rows

6 min readJan 9, 2021

Coda started in 2014 with a simple observation: In a world full of pre-built tools and applications, why do documents and spreadsheets seem to run the world? The founding Coda team crossed paths at Google, YouTube, and Microsoft, where they saw their teams stretch the limits of what spreadsheets and documents can do. Then in 2014, they banded together to build the doc they always wanted. One that erased the boundaries between words and data. And where everyone could work together, in their own way, off the same data.

When using a spreadsheet you easily get used to an attractive advantage: you can reference to any cell you want. Whilst this can lead to rather messy calculations, nobody denies the relevance of the option to refer to a previous row.

In Coda.io this is not so easy. The tables are relational databases and these follow a different logic. Data integrity is protected by linking formulas to columns, not to cells.

Before you continue reading, please have a look at a more recent blog about the previousRow() logic and why you better use buttons.

previousRow() in Coda, a bad idea?

Why a button is your friend to track changes

huizer.medium.com

However in many cases you need such a reference. This article is about some tricks to apply a previous row logic inside a Coda table. These are tricks working well in certain conditions and failing in others. The Coda team is well aware of this issue and I guess we see a programmatically worked out solution coming in the year ahead, a native Coda solution that makes this article obsolete. However for the time being you might need the work around.

Daily Measurements

Already for a while I follow-up on my weight trying to reduce it. I started with notes on a piece of paper and put this in the kitchen, after a while I decided to create a Coda document dedicated to this ambition. I had in mind something simple.

I wanted to see how this day related to the previous day
I needed a column that helped me to define my long term targets. Thus how many kilos in how many days or months to get some realistic numbers.

Previous day

Today I note 100 kg and yesterday I noted 99.7 Kg. We see the delta of 300 grams in the wrong direction. How can we make this simple calculation work in Coda?

Let’s rephrase the question. Can we get the row value of the previous row, thus this row minus one and second can we relate this value to the value in thisRow? The response is : yes we can, using the formula below.

step 01 :thisTable.[Targets in Kg].Nth(Find(thisRow,thisTable )-1)

The next step is to relate the outcome of this function to the value in the same row of the column [Targets in Kg] and deduce the value of the previous row.

step 02: thisRow.[Targets in Kg] - thisTable.[Targets in Kg].Nth(Find(thisRow,thisTable )-1)

We are almost there. But as you can see the first row in the Column [Delta] links to nothing (the row minus one does not exist) and takes the value of [Targets in Kg] in this case ‘100 Kg’ . As of the second row the function runs smoothly. You can also see that the [DeltaSum] is the same as the [Targets in Kg] and this is not how it should be. We fix both issue using an If statement:

appling an If statement to start with an empty cell in case it is the first one

This formula tells that if it concerns the first row, the cell should be empty and only afterwards apply the deduction as shown in step 2. And now it works as long as we start not filtering these results.

This trick works because we make use of a logic that tells us in which row we are via this simple function.

Find(thisRow,thisTable)

The outcome is a number, in our case it is equal to the RowIndex. This number shows the factual position of thisRow in thisTable. When you filter you need the ‘viewed position’ and this position we can (early January 2021) not access via a function. Therefore, as long as you do not Filter, Sort or alter the view in any way, this trick works to deal with the previous row.

You also see in the formula the function Nth() . We put this function behind the complete column in the table. When you ask for all the values in a column, Coda shows them as a list in which the row values are separated by a comma.

Using the Nth() function we ask for the values in a specific Row like Row 4 — (Ntht(4) or in Row 8 — Nth(8). In Row 4 we find 95 Kg and in Row 8 we find 91 Kg.

Since a column can also be read like a list, if followed we can ask for a position in this list. All solutions related to previous rows come back to this logic. You ask for a position in the list and this value you use to show or to work with.

This observation brings us to our second challenge: the DeltaSum

The DeltaSum

This column shows the sum of the previous values. It links to [Delta] in a rather easy way with the previous remarque in the back of our head:

Since a column can also be read like a list, if followed we can ask for a position in this list.

and thus the formula we use is a variation on the previous one:

thisTable.Delta.slice(1,thisRow.RowIndex).Sum()

To keep the formula comprehensible I use the column RowIndex as an alternative for the function behind it: Find(ThisRow,thisTable).

When asking for the sum ‘till so far’ we ask in other words for a subset of the complete list. The subset starts at the first row (and we assume this is an empty one to get the value ‘zero’, now you understand better the fix we introduced earlier on) and goes to the position of thisRow in the list using the RowIndex.

If you ask for the sum of all the values in the list using . thisTable.[Targets in Kg].Sum() you get the sum of all the values. As said, we need only a part of it. Therefore we turn this complete list in a subset by applying Slice(). Put differently, we slice the list into pieces and of that sliced list we ask the Sum(). But if you want the Count() or the Average() it is also possible.

In short

Once you see you can ask for a position in a list, you can use this position as a starting point for your calculations.

I plan to write another article about a bit more complex work based on the logic described over here. Some people assume you need a previous row logic to calculate interests, that is not the case. The function Power() suffices.

My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done.