TimeTables and previousRows
In my post on the previousRow logic I applied the logic that all columns can be perceived as lists and that we have access to all values via their positions written as Nth() in this list. Additionally we have access to items on row level making use of a RowIndex.
We continue this logic and apply it to TimeTables. We calculate first the duration between stops (which differs from standard calculations). We compare values based on previous rows. Secondly we create a running total over well defined parts.
From a technical point of view it is relevant to understand that a “Coda cell” can contain multiple lists. This in contrast with a spreadsheet. So not only is a cell part of a list (as an item in a list), but this cell can contain other lists. This logic permits a more advanced usage of a previousRow logic.
How do we set up a time table in a spreadsheet?
Below the calculation in Excel. You link the two cells and that is it. You see the time between the two breaks if we relate B11 and B4. It takes you 5 seconds and you see the result. That is very fast compared to the Coda calculation I am about to show.
Since tables in Coda function as relational databases, a table not only looks different, it is different. Below the time table we put together as example.
How to define the time between breaks?
We want to know the driving time between breaks to avoid a period longer than 4h30. We look at the period before the break, in-between breaks and after the last break.
The idea is simple, the moment you add the type of occupation and the time, the system should adapt automatically. As such you cannot ruin it by a manual error or omission of any kind. Something that easily happens in any spreadsheet. That having said, let’s have a look on how we can put Coda at work for us.
Before I show what works, first what did not work out for me. I defined the coordinates of the driving periods. These coordinates show the first and last row of a driving period. Below you see the first driving period is one row, (1,1), the second period contains 4 rows : 3,4,5,6 (3,6) and so on.
The idea was rather simple. The moment we have these coordinates, we can link the RowIndex to the duration and off we go. However calculating with time is complicated. After working out this logic for quite a bit, I decided not to continue with it. Instead I created an easier and more robust solution taking the following steps:
- I defined the positions of the breaks in the list and looked at the minus one position of each break: the previousRow.
- To each of the breaks I linked a period, like first period, second period and so on using a
SwitchIf(). The interesting thing about this formula is that the order of functions inside theSwitchIf()defines the outcome and this makes it a good tool to evaluate our time table from the first until the last row. - Once we had defined the periods, we added a filter to show the rows we look at are OR driving periods OR a break.
- Last but not least we asked for the sum of each period, excluding the breaks. The result is that we have per driving period the total time. Once you change the occupation type AND / OR the time, everything else changes as well
A running total
While creating a trip the planner also may want to know if there is sufficient time to add an extra stop. He is well aware of the fact that the max driving period should not exceed 4.5 hours with one driver and that traffic jams and other delays should be taken into consideration. When planning, it is helpful to see per stop the time built up so far. Below you see how it could looks like
This logic works in a few steps:
- First we create a list of the items linked to each periods.
- Second we bring
Slice()andCurrentValueinto play to turn this list into a list with ascending values. - Finally we distribute these ascending values of the respective rows bij filtering on the position of each element using the RowIndex.
Below how it looks from the perspective of a planner.
Conclusion
The previousRow logic is complicated. Though with a good understanding of how a relational database works and the Coda formula language we managed to create a tool that helps planners to simplify some of their routines. This tool could be easily updated with the distances between the stops. With a bit of extra work such trips could be the starting point of automated business cases providing insight in the Profit & Loss of each line.
I hope you enjoyed this article. If you have additional questions, feel free to reach out. Though this article is for free, my work (including advice) won’t be.
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialised in serving SME in harvesting data and keeping it aligned. I mainly rely on AirTable, Coda, Mailjet, Zapier & Paperform to get the job done.
