How to Coda ‘thisRow’ on the Canvas?
How to replace thisRow in a Filter?
In a previous blog I already wrote on this matter. A question of a client made me realize that something simple requires still some explanation.
The question was about a rather simple calculation. The turn over per month in any given year on the canvas. There is a table with turn over, a table with months and that is it.
The calculation in the table is a basic one. We compare all items in the list ‘month’ per row with the item(s) living each row, thus on ‘thisRow’. In the example above we have only one item per row in the list ‘month’. The second part of this blog shows how to deal with multiple items per row. First the function you can write with your eyes closed:
This works as it does because between the table reference and the ‘month’ reference we have put a Filter to evaluate each item in the list. Writing ‘thisTable.month’ outputs per row all months, because when you reference ‘thisTable’ you ask for the complete list.
The next part is to compare it with the value month living on thisRow and if there is a matching value (the inside of the filter is ‘true’) it permits us to get an outcome per row.
This is the introduction of something many readers have applied often. To translate this into a canvas formula, we have to simulate ‘thisRow’ and make explicit what Coda handles for us in any given table.
Coda is smart enough to know inside the table the position of each item. Once outside the table you have to replicate this index with a virtual index. The preparation for that you see below. We don’t have ‘thisRow’ yet in step01 and we get all items in the list.
see below what happens inside the table
We generate the virtual index in step 02 and it results in as many sub lists as there are rows in the table (16).
In the table we have the same result with the same function:
To get a thisRow, we have to evaluate each item one by one and we can do that referencing the position of each item. We bring the position logic into play applying the Nth() function.
In the table you see even better how the sub lists are brought together with the [ ] like for January & March.
In step 04 we ask for the property turnover and the result is the below.
next we add the Sum() function and this works as expected.
We do the same in the table, but you see it does not make sense. The link with the thisRow ‘Month’ is gone. We get per row all totals per month.
Even on the canvas we need one more step and this is a way to select the month, a tool to imitate the ‘thisRow’.
We use a LookUp controller permitting us to select the month of choice, below for January:
We replace ‘theMonths’ with the controller and we are set. The outcome is a bit off, we need only the first element and thus we remove the virtual index logic. Everything becomes simple again.
The essential element is thus the lookup, that one brings back the ‘thisRow’ on the canvas without any complication. I believe this is one of the essential ingredients of Low Code. Already present tools support support simplicity.
So why the complex preparation? Well because sometimes you may need it. In the lookup I selected two month and the result is clear, but you don’t see how it is composed. The list logic helps.
In the doc below I propose also a ‘hard coded’ solution. I would not have thought about it, but after the feedback ‘I don’t understand the lookup’ I wrote Step08. It is hard coded and it works well when the item you reference is a value living in the display column of another table. You use the @ to get it.
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.
