How to Coda thisRow & CurrentValue
Taking a list out of a table and manipulate it elsewhere
I have two tables. One with employees and one with the hours they work per day. Some employees recieve a fixed salary per day worked, while others are paid for the actual hours and as such enjoy also over time when working longer days or in weekends. What I needed to know is the amount of days per employee worked. Below the two tables we work with.
The second table contains the hours worked per day. For everybody regardless how they are paid out, the hours are noted, while other days are marked as sick leave or paid holiday etc. In short we know per day what went on.
What we need
Our task is to count the days per employee and filter out the days worked by people with a fixed salary. Once we know how many days they worked, we can calculate their monthly compensation.
How the tables relate
In the view below you may notice that the column employee is a LookUp column, it relates to the table DB People, this means we can chain, thus using the famous dot.
The chaining looks as below once I write the formula on the canvas. As you notice, after the dot I get the option FixedSalary, which is a check box.
and if I ask for the employees who match this criteria, you get something like below:
While this is correct, this is not (yet) what we need right now, although one might be tempted to start off like this.
Instead we start with collecting the rows that contain the information we need to filter.
This information printed on the canvas is the same list as if we would use a column in the table, have a look below and you see why I often say to my fellow Coda Makers that in Coda everything is a list. The main differences between the canvas list and the table lists are:
First, instead of having this list printed once on the canvas, in the table you have this list as many times as you have rows and that is only interesting if you really need this information in your table. Second, in your table you can use the ‘thisRow’ logic to set up calculations. Outside a table you need a replacement for the ‘thisRow’ → CurrentValue .
Calculating without thisRow
In the table you check per employee the days someone worked. As said before, the amont of hours does not matter, we only need to know that someone showed up. In technical terms the column that contains the hours cannot be blank, or put differently: IsNotBlank(). Second we filter on the employee by comparing all the employees in this table with the employee living on thisRow. Below how this looks like.
Here you see a proper application of thisRow. We compare all the employees (DB Attendance.Employee) with thisRow.Employee ín the Filter. The outcome is what we want. Jessalyn worked 6 days that month.
How can we reconcile both approaches you see below?
To turn this calculation into a canvas (or button) calculation we have to adapt the formula we just developed in the table. In the table the list is evaluated via the reference thisRow.employee but since we have not such a reference outside the table, we need to find a replacement for it.
First we need a virtual index. This is a list of numbers linked to the unique employee as the thisRow.Employee is a unique reference. My wording hints into the direction we are about the explore.
We go back to where we started and we create a virtual index that mimics the thisRow logic and since in our example we have two employees, we see employee number one and number two. From here we go step by step.
- Step 01: we create the list with values via the filter. Notice that behind the last bracket there is no value, as such we return automatically the value living in the display column. This is important because it enables us to ask for additional info via the famous ‘dot’.
- Step 02: we only need the the unique employees, the ones living in
thisRowin the column Employee, but this time we have nothisRowand we therefor reference to this value via a number. - Step 03 (below) shows how we set up a Filter and not a
FormulaMap()as you might have expected seen the virtual index. This Filter looks very much like the one in the table, with one main difference as I explain below.
How we replaced thisRow
The above shows how we replaced thisRow.Employee by BaseList.Employee.Unique().Nth(). The Baselist is the list with all rows. From all these rows we take the property employee and next only the unique ones (like we have in the main table), but in order to evaluate the unique employees, we need the support of the virtual index (1,2), first employee one and then employee two.
The CurrentValue I renamed as EmployeeNbr, this is not a necessity, but I like clear functions that tell me what is going on. The word number tells me that — indeed — it is a number.
We are almost there, we needed the days and this requires a little extra, the function Count().
So here we are, we replace the function that was rather easy in the table for a bit more complicated variant outside the table.
One might wonder why we need this complicity? Well when you want to avoid buttons in a table or functions — to keep the table as light as possible — and you want to replace them by a canvas button or a canvas formula, you need this kind of approach. I use it in most of my larger documents.
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 and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
