The time has come to update you on the progress we’ve been making with Calcapp 4. Be warned, though — this post is somewhat technical. If you love formulas and what makes them tick, this post is for you. If you’re more interested in our forthcoming support for customizable PDF reports and binding fields to data, feel free to skip past the finer details.
Regardless, rest assured that we’re investing heavily in Calcapp and that we are committed to bringing you major updates with lots of new useful features.
Background
Before we get to what we’ve been up to, we’ll take a moment to shine a light on how Calcapp works. As a Calcapp user, you’re probably familiar with Calcapp Creator, the app designer, and Calcapp Connect, which you use to run your own apps as well as shared apps.
There are other parts of Calcapp, though, which are mostly hidden from view. There’s Calcapp Server, which is what delivers your apps and allows you to make updates. Under the hood, it uses something called Calcapp Compiler.
What’s a compiler? If you’ve ever dabbled in traditional software development, you know that you sometimes need to compile code to make it runnable. (With some languages, such as plain JavaScript, there often isn’t a separate compilation step, at least not one which is visible to you.) Compilers can typically tell you of errors in your code, preventing it from becoming runnable.
Calcapp Compiler does two things. First, it ensures that your app is free of errors, and if it isn’t, it points out the errors to you. (Have you ever tried to subtract a number from a text string in a formula? That’s Calcapp Compiler pointing out the error to you.) Second, it converts (compiles) your Calcapp formulas to JavaScript code which browsers can run directly.
The progress we’ve been making
Calcapp Compiler really is the heart of Calcapp and we have lavished a lot of attention on it lately. We have, essentially, worked full-time on it since November, only taking time off for making your apps qualify as Progressive Web Apps and enabling some miscellaneous features requested by the community.
Calcapp formulas are growing up. We’re enabling them to run in response to events being fired (like a button being pressed). We’re also enabling you to reference elements of your app directly (and not just field values), which will come in handy with certain new properties (like a property enabling a formula to specify which fields to include in a report).
We’re also adding support for around a hundred new formula functions, including SWITCH and IFS. These functions will replace IF for many uses and make formulas significantly easier to read and write (without all the matching parentheses that IF typically requires).
The most important addition, by far, is support for arrays.
Supporting arrays
We’ve long talked about adding support for tabular data, and
arrays will be at the heart of this new support. If you’re
not familiar with arrays, think of an array as a list of items, that
is, a single value referencing multiple values. You can write a
literal array in a formula using brackets, like { 2, 4, 6 },
which denotes a single array with the ordered values 2, 4 and 6.
Spreadsheets support both ranges (B12:C14) and arrays
({ 2, 4, 6 }).
Some spreadsheet functions accept both ranges and arrays, some accept
only arrays and some accept only ranges. We find that confusing, so
Calcapp will only support arrays.
We’re adding support for a very large number of new functions, with the vast majority of these expecting arrays to be passed. Most of these will correspond to functions long supported by spreadsheets in general and by Microsoft Excel in particular. We’ll support functions like MATCH and INDEX, but also functions like AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMIF and SUMIFS as well as lots of new statistical functions.
We’re also leaning heavily on the new array functions introduced by modern versions of Excel (requiring a subscription to Microsoft 365). Those include functions like FILTER, XMATCH and XLOOKUP. (Yes, a true look-up function is coming to Calcapp, although you’ll have to make do with writing the values in the formula itself for the time being.)
The finer points
An extra complication is that we insist on something called
static typing. What that means is that we can flag formula
errors which traditional spreadsheets simply don’t flag. If you try
entering the formula IF(4, "true", "false")
in Excel, “true” will be produced, because 4 is assumed to be
equivalent to TRUE (this process is known as type coercion).
Calcapp will instead present an error and ask you to enter a logical
value, potentially saving you from having to make sense of confusing
output.
Now, capturing all the nuances of Excel formula functions with static typing is a large undertaking. For instance, SWITCH expects parameters to be given in pairs, which is something we haven’t had to handle before.
Another example is SUMIF, which returns the sum of all values in an
array, but only includes values which satisfy a condition. Said
condition can be expressed as a value which an array value must be
equal to, or it can be expressed in the form of a string like “<4”
(an array value must be less than four in order to be considered). In
other words, SUMIF({ 2, 2, 10, 4 },
2) returns 4 (only the first two items are added together,
because they need to equal 2), whereas SUMIF({ 2, 2, 10, 4 },
">2") returns 14 (only the last two items are considered,
because they need to be greater than two).
With SUMIF, if you provide an array of numbers, we must therefore allow the condition parameter to be either a number or a text string (enabling more complex conditions like “<4”). That all adds complexity (requiring us to implement something known as union types).
Finally, we also want to support formula snippets (also
known as lambdas) in place of the condition parameter,
enabling full use of Calcapp formulas to determine whether an array
item should be considered. With a formula snippet, you can write
SUMIF({ 2, 2, 10, 4 },
">2") as SUMIF({ 2, 2, 10, 4 },
Item > 2). Full use of Calcapp formulas as the condition
parameter means that SUMIF({ 2, 2, 10, 4 },
(Item > 2) && (Item < 10)) will also work.
Referencing fields and not their values
To reference the value of a field, you just write the field name in a
formula. In other words, to add the values of Field1 and
Field2 together, you just write Field1 + Field2.
However, to reference whether Field1 is visible, you need to
type the field name, followed by a period, followed by the property
name Visible. In other
words, to add the values of Field1 and Field2
together, but only if both fields are visible, you need to write
something like IF(Field1.Visible &&
Field2.Visible, Field1 + Field2, 0).
We could easily have asked you to reference the value of a field by
writing something like Field1.Value, but that
would have been a mouthful and would have made your formulas hard to
read. In Calcapp 3 (the current version), we simply assume that you
intend to reference the value property if you don’t explicitly write
a period followed by a property name.
This becomes more complicated in Calcapp 4, because we now want to
enable you to reference not only the value of a field, but also the
field directly. The reason for this new requirement is that we are
introducing new functions and properties that need to directly
reference fields, and not their values. For instance, RESET will be
callable from formulas run in response to, say, a button being
pressed, and will reset the field to its initial value. RESET(Field1) will
reset Field1, and it is important that it is passed the
field itself and not its value. (After all, RESET(4) wouldn’t make
any sense.)
We don’t want you to have to type either Field1.Value or
Field1.Reference (where
the latter would get you a reference to the field itself, which would
then work with formula functions like RESET). We want you to be able
to type just Field1 and then have
Calcapp figure out what you’re referring to. In Field1 + Field2, we can
be certain that you’re looking to add the values of the two fields
together, and in RESET(Field1), we can
be certain that you’re looking for a reference to the field itself.
(We’ll actually allow you to write Field1.Value and
Field1.Reference to
make the call yourself, in the off chance that Calcapp 4 can’t figure
out what you mean.)
Our near-term plans
Our Calcapp 4 project encompasses far more than a next-generation formula language, including binding fields to tabular data and a PDF report designer. The new formula capabilities can be considered the base on which the full Calcapp 4 experience will be built.
In the near term, though, we hope to ship many of the formula improvements while keeping the experience identical in all other respects. To that end, we will work to make the new Calcapp Compiler work with the existing Calcapp Creator and Calcapp Connect, creating a sort-of hybrid between Calcapp 4 (the new Calcapp Compiler) and Calcapp 3 (with lightly updated versions of Calcapp Creator and Calcapp Connect).
Then, we’ll commence work on more fully realizing our Calcapp 4 vision.