A customer recently got in touch and asked if it was possible to summarize the switch field selections made by a user in a single text field. The answer is yes — and what’s interesting is that there are two ways to do it, one using classic Calcapp formulas, and one fully taking advantage of our new formula engine.
First, let’s consider a sample app. Here, the user is asked to select which weekdays they worked. The results are summarized in a text field:
What’s interesting here are the formulas associated with the “Days worked” text fields. Here’s the old-style formula:
And here’s the formula that takes full advantage of the new formula engine:
We’ll go over the formulas in detail soon, but suffice to say that the new formula is not only shorter, but also a lot less repetitive. For employees potentially working seven days a week, we could easily add two new switch fields, Saturday and Sunday, type Monday:SundayMonday:Sunday instead of Monday:FridayMonday:Friday in the formula and be done. With the old-style formula, achieving the same end result would be a lot more work.
Dissecting the old-style formula
First, let’s look at the old-style formula to see why it works. Here it is again:
First, let’s strip away the second parameter to the first IF function:
What this formula does is that it uses the second parameter (here
ellipsized as ...) only if the user has selected one or
several days. Otherwise, the third parameter is selected and “None.”
is used. || is Calcapp’s “logical or”
operator, meaning that Monday || Tuesday ||
Wednesday || Thursday ||
FridayMonday
|| Tuesday ||
Wednesday || Thursday ||
Friday simply means Monday or Tuesday or Wednesday or
Thursday or Friday.
Now, let’s look at that second parameter to IF:
IF(Monday, "Monday")IF(Monday; "Monday") is pretty self-explanatory — if the switch field Monday has been toggled to its “on” position, the text string “Monday” is used. Otherwise, a blank value is used.
& is Calcapp’s concatenation operator, which joins text strings together. That means that "a" & "b""a" & "b" is a convoluted way of writing "ab""ab".
IF(Tuesday, IF(Monday, ", ") & "Tuesday")IF(Tuesday; IF(Monday; ", ") & "Tuesday") requires more of an explanation. What’s IF(Monday, ", ")IF(Monday; ", ") doing there? Simply put, it’s there to make sure that a comma separates “Monday” and “Tuesday”, but only if the user has toggled the Monday switch field to its “on” position.
IF(Wednesday, IF(Monday || Tuesday, ", ") & "Wednesday")IF(Wednesday; IF(Monday || Tuesday; ", ") & "Wednesday") is similar. IF(Monday || Tuesday, ", ")IF(Monday || Tuesday; ", ") adds a leading comma only if the user has selected Monday or Tuesday, that is, only if there is text preceding “Wednesday”. Again, note the use of the “logical or” operator ||.
The remaining parts of the formula follow the same pattern.
Dissecting the new-style formula
The approach illustrated above may work acceptably if we only have five or seven switch fields, but what if we have a hundred?
The new formula engine provides a concise solution that performs identically to the old-style approach. Here it is, again:
First, let’s temporarily remove the second parameter to the first IF function:
Like the old-style approach, this formula uses the text string “None.” if the user has toggled none of the switch fields to an “on” position. Otherwise, it uses the ellipsized second parameter.
Essentially, this approach replaces Monday || Tuesday || Wednesday || Thursday || FridayMonday || Tuesday || Wednesday || Thursday || Friday with OR(Monday:Friday)OR(Monday:Friday), which perform identically. The new approach has the benefit of being shorter, and of scaling to an arbitrary number of switch fields (we would just need to replace FridayFriday with the new last field).
How does it work? You may recall from our November introduction that Monday:FridayMonday:Friday is a range, which in this case is a short-hand for expressing the array { Monday, Tuesday, Wednesday, Thursday, Friday }{ Monday; Tuesday; Wednesday; Thursday; Friday }.
An array is a list of values, known as elements. The OR function returns TRUE only if at least a single element is TRUE. Here, it returns TRUE only if the user has toggled one or several switch fields to their “on” positions.
Now, let’s look at the second parameter to the IF function:
FILTER takes the array to filter as its first parameter and a logical array of the same size as its second parameter, which determines which elements of the first array are returned. If an element of the second array is TRUE, the corresponding element of the first array is included in the returned array, otherwise, the element is dropped.
Here, the array to filter is Monday:FridayMonday:Friday — an array of switch fields — and the logical array is Monday:Friday = TRUEMonday:Friday = TRUE. As you might expect, the logical array consists of five elements — one for every weekday — and it is TRUE only where the user has toggled the corresponding switch field to its “on” position. As a result, FILTER returns an array of switch fields that the user has toggled.
Before we move on to TEXTJOIN, there’s one final bit of business to discuss. After FILTER(…)FILTER(…), the eagle-eyed reader may have noticed that .Label,Label has been added:
Recall that FILTER returns an array of switch fields. A switch field supports various properties, including Label, which returns the label of the field. As a result, by appending .Label,Label, we effectively transform an array of switch fields to an array of text strings, representing their labels.
TEXTJOIN joins array elements together in a single text string, separating the elements with the delimiter given as the first parameter. For instance, TEXTJOIN("x", FALSE, { "a", "b", "c" })TEXTJOIN("x"; FALSE; { "a"; "b"; "c" }) returns “axbxc”.
Putting it all together, TEXTJOIN takes an array of text strings representing weekdays and produces a single text string, where every weekday is separated by a comma and a space.
Again, the new approach is cleaner, shorter and scales to an arbitrary number of switch fields. The one downside is that you need to have a better understanding of how formulas work — but once you do, you will find that you can supercharge a lot of older formulas, making them more concise, more powerful and easier to maintain.