On the road to Calcapp 4

Calcapp 4 is our next-generation version of Calcapp, and it's coming along nicely. Read on to learn more about the progress we've made.

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.

« Feature: Auto-height for AMP Tip: Generate better PDF documents through Zapier »