Photo by DIEGO SANCHEZ on Unsplash

How to Coda thisRow & CurrentValue

Taking a list out of a table and manipulate it elsewhere

Table with people
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.

chaining to relate data
Collecting the rows.
The canvas formula applied in the table

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
Turning the table formula into a canvas formula
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 days per employee with a fixed salary
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

I use Coda mainly for (HR) planning & (budget) calculations. Follow me to learn how to Coda with numbers. I blog at least once per week.