We think that the time for incremental change is over. It’s time for revolution.
This isn’t the first time we’ve been down this road — the service you use today is internally called Calcapp 3. Calcapp 1 was created back in 2003 as an easy means of building single-screen calculator applications for Windows using a text file containing formulas. In 2008, mobile was booming and we created Calcapp 2, which was capable of building multi-screen apps for Windows and Android. Calcapp 3, begun in 2014, brought Calcapp into the web era with an app builder available to anyone, producing slick web apps for iPhone, iPad, Android and desktop computers.
It’s 2020, and the time has come to build Calcapp 4. Chiefly, we aim to provide far better support for tabular data and formula-driven automation, which we discuss at greater length below.
You don’t need to worry about your investment in Calcapp, though — all your existing apps will continue to work and the user experience will still feel familiar.
Also, change won’t happen overnight. The new features will be rolled out incrementally, without affecting stability or performance negatively.
The primary innovation Calcapp 4 will bring is much better support for tabular data. While we support importing tabular data for use with drop-downs and exporting data to CSV files or even spreadsheets and databases (through a third-party service), this is a far cry from being able to write apps that can read the data they themselves have written.
Calcapp has excellent support for calculations today. However, calculations often need to be based on not just raw formulas, but on data, which we don’t handle well today.
Also, we think there is a far larger market for apps that can read and write data than there is for pure calculator apps. We’d like to not only serve our existing user base better, but also break into this much larger market.
No-code app builders are exploding in popularity, and what the vast majority of these app builders have in common is that they have robust support for handling data — and poor support for calculations and for using formulas for modeling dynamic behavior in apps. We think that combining Calcapp’s formula prowess with first-class support for data will produce a truly stellar product.
Generally, these data-savvy app builders fall into one of two categories. Either they offer great ease-of-use, but little power (like the slick Glide) or they offer lots of flexibility, but at the expense of a steep learning curve and requiring users to learn lots of new concepts (like the well-engineered Microsoft PowerApps).
We think there is room for an app builder that is both easy to get started with and offers a lot of power through formulas. Further, we think it’s crucial that this product respects the investment users have made in spreadsheets in general and in Microsoft Excel in particular, by offering a similar mental model.
(Others have tried to go down that road in the past, by creating app builders fully integrated with the Microsoft Excel desktop application. We think that’s a mistake, as Excel was never meant to be an app builder. For starters, designing the user interface of an app using Excel’s grid will never yield high-quality user experiences. We think that any product integrating that deeply with Excel is needlessly limiting itself.)
No product on the market meets these criteria today. Our aim is to create it in the form of Calcapp 4.
Apps often need to take complex actions, based on evaluating information entered in an app. For instance, a button, when pressed, should perhaps send an order to a warehouse, make a note of the order in a database and then send a confirmation e-mail to a user. If any of the entered data is not valid, a warning popup may need to be displayed.
Calcapp makes no attempt to realize such workflows today. Buttons can only send or open reports, reset fields or send off data to third-party services. We recognize that this is limiting.
We aim to realize automation through formulas, triggered by events such as a button being pressed. These formulas will be different from regular formulas, in that they will be able to not just calculate values, but take actions through special formula functions.
The current crop of enterprise app builders usually realize automation by enabling workflows to be described graphically. Users essentially have access to a palette of actions, where individual actions can be dragged onto a canvas, allowing multiple steps to be described. There is often special support for describing conditions, enabling different actions to be taken based on whether a formula evaluates to TRUE or FALSE.
Our initial plan calls for using plain Calcapp formulas to realize automation. There is no reason we couldn’t allow these formulas to be represented graphically in the future, enabling users a choice between typing formulas and the graphical approach favored by other app builders. The graphical approach has the advantage of clearly communicating what actions are available and allowing their behavior to be easily customized, without requiring users to remember or look up function names or the order of parameters.
To support the features listed above, we’ve been hard at work since late last year adding features to Calcapp formulas.
Here’s a small sample of the work we have completed so far:
IF without all the parentheses
Expressing multiple conditions with the IF formula function leads to verbose,
hard-to-read formulas, as multiple IF functions need to be nested.
IF(X = 1,
10, IF(X = 2, 20, IF(X = 3, 30))) isn’t particularly easy to read, especially
if you need a large number of conditions and values.
We’ll fix this problem by allowing the extra conditions and values to be
expressed as parameters to the first IF invocation:
IF(X = 1, 10, X = 2, 20, X
= 3, 30). Much better!
(We’ll also support the new Excel formula function IFS, which does much the same thing.)
Support for the SWITCH formula function
Excel recently gained support for the SWITCH formula function, which Calcapp 4 will also support. Think of SWITCH as a simplified version of IF, provided that you only need to test a single value and you only need to test equality.
The first parameter to SWITCH is the value to test. If this value is equal to the second parameter, the third parameter is returned. Otherwise, SWITCH compares the first parameter to the fourth parameter, and if those values are equal to one another, the fifth parameter is returned, and so on.
Here’s the IF formula above, rewritten to use SWITCH:
SWITCH(X, 1, 10, 2, 20, 3, 30).
Every value reachable through a Calcapp formula has traditionally been a number, a logical value or a text string (known as the type of the value). That is unfortunately quite limiting.
We have received many requests for enabling buttons to navigate to various parts of an app, using a formula to determine the target panel. As formulas haven’t been able to reference panels, we haven’t been able to implement that feature.
Also, countless customers have asked us to make which fields are included in reports customizable through formulas. As referencing fields through formulas hasn’t been possible, we’ve had to postpone adding that feature.
Calcapp 4 changes all that, by adding lots of new types, including the aforementioned panels and fields. We look forward to adding new properties and formula functions taking advantage of the new types.
We refer to formulas which can execute actions as action formulas. These formulas will have access to special formula functions, known as action functions, which can execute actions.
Action formulas will also be special in that they will be able to execute
multiple actions and thus invoke multiple action functions. You will probably
; between such invocations.
Here’s an example which sends a report and displays a message if and only if the
Result field is valid, and otherwise displays an error message:
IF(Result.Valid, SENDREPORT(...); ALERT("Done!"), ALERT("Invalid result!"))
Finally, action formulas will be able to set values, probably by writing
something akin to
Field1 := Field2 * 2. Note the
:= symbol — that’s an
assignment operator, which assigns the value on the right-hand side to the
property on the left-hand side. This will enable buttons to do things like
incrementing values, without having to use a stepper, or perform calculations.
(VBA users are used to using
= both for assignment and equality. We think
that’s confusing, so we’ll probably use
:= for assignment instead.)
Some action functions will ask that you supply formula snippets that are executed at a later point in time, snippets which may have access to additional values. (In programming lingo, these snippets are known as lambdas, or anonymous functions.)
For instance, an action function asking the user to enter a value in a
popup may make the response available as the
QUERY("What is your name?", ALERT("Your name is " & Response & "!"))
Above, the (fictitious) QUERY formula function takes two parameters: the question to pose to the user and a formula snippet which is provided the response and is executed when the user presses a button.
Naming the values which are provided to these formula snippets may result in
easier-to-read formulas. Naming values will be supported using optional
so-called arrows (note the
QUERY("What is your name?", Name -> ALERT("Your name is " & Name & "!"))
We have promised a PDF report designer for the longest time now. We aim to deliver one, but we will deliver better support for tabular data and formula-driven automation first.
We are also aware that many customers want to create apps and upload them to app stores. Some enterprising Calcapp customers have found ways to do so using third-party software, and we expect this process to become easier now that apps produced through Calcapp are true Progressive Web Apps (PWAs). However, supporting app store submissions out-of-the-box would be a complex undertaking and isn’t something we plan to prioritize in the near term.
Finally, we expect Calcapp 4 to deliver myriad smaller improvements. Calcapp 3 was started in early 2014 and the technology landscape has improved markedly since then. We aim to adopt new technology to make maintaining Calcapp easier and to improve performance. We plan to use that opportunity to address long-standing usability problems, including adding support for undo and redo in Calcapp Creator and more robust formula editing.
We hope to deliver the first fruits of our labor in a couple of months. The first features we plan to deliver are the formula improvements discussed above and support for formula-driven automation. Then, we look forward to sinking our teeth into adding support for tabular data.