Photo by DIEGO SANCHEZ on Unsplash

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.

Table with people

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.

Keeping track of attendance

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:

chaining to relate data

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.

Collecting the rows.

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:

The canvas formula applied in the table

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.

setting up the filter in the table

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?

Turning the table formula into a canvas formula

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.

The virtual index
  • 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 thisRow in the column Employee, but this time we have no thisRow and 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.
Replacing the thisRow logic

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().

´The days per employee with a fixed salary

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.

Christiaan writes about ‘How to Coda thisRow & CurrentValue’

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Christiaan Huizer

Christiaan Huizer

I use Coda mainly for planning & calculations of all sorts. Follow me to learn how to Coda with numbers. I blog at least once per week.