WithName() was introduced late december 2020. Ít intents to make the writing of functions easier and to protect the user against human mistakes. The function enables two key scenarios — reusing logic within a formula, and nested loops.
Reusing logic within a formula
I see the usage of
WithName ()— when we talk about reusing logic within a formula — mainly taking place in an
If() statement or in a
SwitchIf(). The first part is a condition and often this condition is the outcome of a formula. If the outcome is a simple
False(), the rest of the formula simply tells what to do or to show. Below ´left you see a simple
If () and right how a more complex
In the simple example you evaluate a list via a
FormulaMap() and if an item in this list contains “yes’ we note 1 and if not (anything else) we note a 0. This does not require a
The application of
WithName()in the right example on the other hand is useful. In case the outcome of the
FormulaMap() is between 9 & 10, we need a certain result, if not between 9 & 10, something else. Without
Withname() I would have copied — pasted the complex formula trying not to forget any comma or bracket. Ápplying
WithName() results in an easier to understand formula and that is important for the maintenance and improvement of the developed logic. I wrote this function a while ago and still when I look at it, I directly understand what I had in mind. However the
WithName() function is not perfect since the Coda team notes:
Note: If you intend to reference the expression you’re renaming in multiple formulas, we still recommend storing it in a separate column or named formula so it only evaluates once. Any formula with multiple costly expressions may also evaluate more efficiently if you represent each part in a separate column or named formula.
In other words, keep it simple and if that is not possible, put the formula in a separate column (you can hide anyway). I guess the auteur had button logic related to
RunActions() in mind when making this statement. In buttons you often need to evaluate multiple CurrentValues because you apply more than once a
Filter() or an
AddRow() This is our next chapter.
When I saw the launch of
WithName() I actually only thought about nested loops. This is because in the community members wrote quite a bit on CurrentValue variations (like an inner and outer CurrentValue) and how to deal with this. In the summer of 2020 Paul posted a video on how to apply Helper Tables as a way to deal with various CurrentValues.
First the context that gave rise to the issue we address with
Certain loop formulas in Coda like
AddRows()run multiple times across a list, and use the keyword
CurrentValueto reference the current item from the list. […] Things broke down when you needed to nest multiple loop formulas.
While creating a timetable for a bus company, I needed to add various rows to a table and in some rows I needed a result that was not yet available in any column of any table. It concerns the departure times of each trip, per day. Since it is the same for every day and it follows a certain pattern, we calculate it on the flow, while adding rows.
We start with the basics and these are two variables: the period the bus should drive and the name of the line. Below is how it looks and please note that the outcome of the function before the
FormulaMap() is written as
CurrentValue. For each date, we add a row as you can see on the screenshot right.
This is the easy peasy start. Next we need to add the stops per day. Via a simple
lookup() we added first the stops to the row where we have the button, the same counts for the first round the bus drives. The following rounds we calculate on the flow. Íf you wonder why I do not integrate the lookup into the button the answer is ‘simplicity’. It is easier to store these values in the Button Table, it is also good for the readability of the button logic.
An important learning in working with
WithName() is the applied order. In our case it is the following:
- The days the buses drive (5):
- The frequency the bus drives (how many times a day). This is a value we find in the contracts, in our example it is 8 times per day.
On the same level as frequency, we have the bus number logic, each trip has each day a unique number per day, however this bus number is per trip the same for all days.
The formula takes the start value and adds for each row a number. We apply a simple trick by combining two
WithName() functions. First the one that generates after the dates the trips per day, we named it ‘shifts’.
Sequence(1,thisRow.[Shifts per day]).FormulaMap(CurrentValue.WithName(shifts,shifts.FormulaMap(thisRow.[bus start number] + CurrentValue-1).WithName(nextbusnumber,
We use ‘shifts’ (thus the numbers 1–8) to generate the bus number by starting at number minus one, plus one, plus two, plus three and so on. The result is an ascending range of bus numbers, exactly as we need.
The ‘nexbtbusnumber’ is used in the
Once set up (5 days and 8 trips per day, thus 40 rows) we have to add per trip the stops and the times, meaning 6 rows for each row. We should thus end up with 240 rows. This part is crucial in understanding how
WithName() operates to support your nested logic. Once you see this, you cannot unsee it anmore and handle any planning issue.
The main trick is to keep the function ‘open’ to enable nesting to the extent you need it. In the above screenshot you see how after the comma of data range we continue with a new function (the shifts) and how we keep this one open and use it again.
It happened to me that I got along with the Coda suggestion to close with a bracket after CurrentValue, so I did something like this :
And then the nesting stops, because you close with a bracket. Instead you keep the function open and the
FormulaMap() keeps running. Keep this in mind and off you go!
Based on this insight we wrote functions that generated sophisticated timetables. These calculations require quite some testing and experience. Though the basics rules are rather easy to understand, one tends to forget all sorts of details resulting in no or erroneous results.
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 contributions in the Coda Community.
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 Coda, Mailjet, Zapier & Paperform to get the job done.