previousRow() in Coda, a bad idea?
Why a button is your friend to track changes
I contributed in a discussion on how to keep track of sales in a table. The idea was to create a set up many makers feel familiar with. You take a value living on a previous row and or add or subtract a value. While this works very well in a spreadsheet (also in terms of performance), it is not the Coda way. Interestingly, not because Coda cannot handle this. Even the function to get the job done is simple as you see below.
We can use a filter based variation to get the same result since it is all about the rowIndex.
We can even complicate the matter a bit and introduce various products. I asked ChatGPT for some inspiration and got a list of wood products you see partly in the table below:
The table I created was filled out via a random function. First I copied the products into a product table and second I used the value for new rows in combination with a function. I did more or less the same for volumes and sales. This little trickery results in irregularities that may resemble reality.
[DB Wood].RandomItem()In the table I filled out we have something remarkable. I underlined the issue. Do you see what I see?
The initial stock is 410, we have 10 items sold and that should result in 400 items left, but it does not. It shows instead that we have 50 items sold (which is the next line). Looking at the final outcome for Beech, we have in total 60 items sold and that is correct. Is goes wrong when we look at the order of things, the table sorting. This is because of the position of the row in the table. It is a relative position.
In Coda most data is stored in tables, and each row in a table represents a unique record or entity. The rows in a table are not necessarily ordered or linked in any particular way, so there may not be a clear concept of a “previous” row for a given record. Previous in the Coda context implies always temporarily. The rowIndex we use handles exactlty this. It is based on the Find() function and outputs the position of a row in the table. When the position of a row changes , this index changes. It needs to be calculated each time, over and over again. That is not efficient.
Instead of relying on a previous row concept, databases typically provide other mechanisms for working with data, such as querying and filtering data based on specific criteria, or using transactional operations (buttons) to ensure data consistency and integrity.
We can obtain a visual coherent display following a few steps. Below the main function and the screenshot for some visual guidance. At the end of this blog I reference a doc containing these tables.
The functions are — again — rather simple. What we need is the rowIndex we defined in the second column. This is a vertical logic and although essential for the proposed solution, it is also the weakest link in our set up. As already mentioned, this logic requires that the complete table will be recalculated every time a row is added, removed or modified. That is what we call an expensive operation.
We start filering, we count the outcome, we slice it two times. First based on the sub lists and second based on the rowIndex (column 2). In step05 we add Sum() . The cherry wood part of the example shows best how it works. Below how you can present it rather well, it has a bit the spreadsheet feeling, right?
Don’t do this
It is possible, it is not even difficult and as long as the table remains below let’s say 5000 rows also relative fast. However, when it comes to stock management, you have to input data. Manually or maybe via scanning codes. This input changes the values in your table and you want to keep track of changes. Maybe you want to archive data related to the user scanning (or manually adding data) and this all goes so much easier and better with buttons.
Buttons work wonderfully. I wrote a few blogs about them, as well a blog about how to set up a simple stock management logic.
The demonstration of the previousRow() logic you find in this document.
My name is Christiaan and I support SMB with calculations (budgets and — Human Resource — planning) and I prefer using Coda to get the job done.
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.
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.
