For the past many months, we have bundled together all the new features we have been working on and released them at the end of the month. (Here are the release notes for August, September, October and November.) There won’t be a monthly update in December, though, because we’re working on a big update requiring more time than a month.
Actually, we started working on the new update in late October, just after the update for that month shipped. (The work that went into the November release was done in parallel.) We have decided to focus all our energy on the new update rather than shipping a few surface-level updates and calling it the December release.
This post details the new features we’re working on. For once, we also spend some time talking about the technical work that’s going into supporting the new features.
Cross-references
The impetus for the new release is the need to reference fields that reside on panels other than the current one. Without support for these cross-references, you are essentially limited to building either small apps, or building large apps with many separate parts that can’t talk to each other. We have seen apps built with the current version of Calcapp with hundreds of fields per panel, just because of this limitation. We can only imagine all the apps that simply were not built when users realized that Calcapp had this limitation.
If you’re used to Microsoft Excel, you can probably guess what cross-references will look like in Calcapp formulas. In Excel, you can reference other worksheets in your workbook by writing the name of the worksheet before the cell reference and separating the two parts with an exclamation point. (In OpenOffice.org and LibreOffice, you use a period instead of an exclamation point.)
Thus, Calcapp Creator will allow you to name not only fields but also
your panels. To reference a field on another panel, you will be able
to simply write CalculationPanel2!Field4
(though we recommend that you name your panels and fields explicitly
to make formulas easier to read).
Under the hood
Cross-references may look simple from a user’s point of view, but they are fairly complex to implement. Feel free to skip this entire section if you’re not interested in how things work under the hood.
Our model for doing calculations used to be simple – when a user changed a field value in a calculation panel, we simply performed all calculations in the panel. Doing this for a single panel actually works quite well, with no slowdowns, even if there are hundreds of fields.
Had we continued with this model and allowed cross-references, though, we would have had to perform all calculations in your app every time a single value was changed. With thousands of calculations, this is no longer tenable.
Spreadsheets solved this problem long ago by only performing the calculations that need to be performed and not all calculations in your workbook. If you change a cell value, a modern spreadsheet only recalculates the cells that depend (indirectly or directly) on this cell. Calculations that are unrelated are not performed.
You can see the relationships between cells yourself by using Excel’s Trace Precedents and Trace Dependents commands or by using the Detective feature of LibreOffice and OpenOffice.org. A computer scientist would say that spreadsheets maintain a dependency graph to keep track of its cells and the other cells they depend on.
Calcapp has since its inception kept track of field dependencies in
this way. We use our dependency graph to detect when you reference a
field in a circular manner. For instance, if Field1 uses the formula
Field2 * 2
and Field2 uses the formula Field1 / 2, there is a
circular relationship between the fields and Calcapp Creator will
show an error message. We also use our dependency graph to ensure
that calculations are performed in the right order when you run your
apps. All this work happens on our server.
Calcapp Connect and the apps you build will now have access to this information to ensure that they only perform calculations that need to be performed. In other words, you will be able to build larger apps with interconnected panels and can also expect calculations to be performed faster.
Calcapp Creator actually maintains an elaborate dependency graph while you build your apps, to ensure that it has a good idea of how all the parts of your app are connected. That in turn enables you to, say, remove a panel and all panels owned by it.
We want you to be able to easily change the name of your fields to ensure that your formulas remain easy to understand. When you change the name of a field, Calcapp Creator looks at all formulas of the current panel and changes their references as well.
Prior to Calcapp gaining support for cross-references, this could be done easily simply by looking at all formulas of the current panel. With support for cross-references, we would need to look at all formulas of the entire application. With thousands of fields, this would be too slow, and Calcapp Creator will thus going forward also keep track of how fields reference one another to keep things speedy when you rename fields.
In the future, we hope to expose this information to you so that you can tell at a glance which fields are referenced by a particular formula. We could, for instance, use different colors for different fields and then use that particular color both for the field name in the formula bar and for the field in the app designer when the formula is edited. That would enable you to determine at a glance what fields are referenced by a particular formula.
Introducing calculated properties
There’s another reason for ensuring that calculations are performed quickly: we expect the apps you build to soon feature many more calculations, but not necessarily more fields.
Currently, the only formulas you can enter are used to calculate values of fields. Going forward, you will be able to use formulas to calculate things like whether a field should be visible, whether a button should be enabled and the text color of a field. You’ll also be able to determine whether the user should be allowed to move on to the next panel and the recipients an email report is sent to using formulas. Collectively, we refer to properties set using formulas as calculated properties.
Many users have asked us to add support for data validation in the
past. This feature can be used in spreadsheets to flag values entered
by a user which may be incorrect, thereby alerting the user. You will
be able to use calculated properties to add something similar to the
apps you create with Calcapp. While we haven’t yet determined if
we’ll add an explicit property that determines validity, you will be
able to use the color property to display the field value in red if
the value added by the user is invalid, for instance. You can also
add a text box with an error message below the field and use a
calculated property to ensure that the text box is only visible if a
formula evaluates to TRUE. (To flag negative numbers, you’d use the
formula Field4
< 0 for the visibility property of the text box, ensuring
that it’s only visible if a negative number is entered.)
That brings us to another point worth mentioning: calculated properties will not only work with fields, they will also work with buttons, text boxes and options in list panels. At least initially, options in list panels will only support one calculated property which determines if the option should be visible. Buttons will support two properties, visibility and whether the button should be enabled.
Enabled buttons can be pressed and disabled buttons are grayed-out.
If your app uses buttons to send reports, we think you’ll want to use
this feature to ensure that reports can only be sent when the user
has filled out all required fields. A formula like ISDEFINED(Field1) &&
ISDEFINED(Field2) && ISDEFINED(Field5) will work nicely
for that.
All elements supporting calculated properties will be collectively
known as variables. You will be able to assign names to all
variables and not just to fields. To refer to a specific property of
a variable in a formula, you will be able to first write the name of
the variable, followed by a period and then the property name. To
make a text field show a particular text string if and only if a
button is visible, you will be able to use the formula IF(Button3.Visible,
"Visible!", BLANK()).
To reference a specific property belonging to a variable residing in
a different panel, you need to also include the name of the panel. If
Button3 is part of a panel named CalculationPanel2 and you want to
reference its visibility property, you’ll need to write CalculationPanel2!Button3.Visible
in formulas to refer to it.
For your existing formulas, nothing will change. While field values
are now considered yet another calculated property, you won’t
reference a value of another field by writing Field3.Value, you will
simply write Field3. That means that
all your formulas will continue working and that referencing field
values will continue to be easy and terse.
Migrating your apps
Whenever we make sweeping changes to the way apps are represented in our database, we need to take steps to ensure that the apps you have already built continue working the way they did before. Specifically, we need to migrate your apps to the new format, a process which requires time, care and lots of testing to get right. The desired outcome is that you’re able to continue using your apps as though nothing happened and simply start making use of the new features.
(This is only the second time since we launched the preview release early this year that we need to migrate your apps to a new internal format. The first time was for the June release, which introduced text fields, logical fields, number formatting and 244 formula functions.)
We hope that our upcoming release will lay the foundation for many exciting features to come (and lots of future calculated properties). We’re looking forward to seeing all the new apps that will be built with our upcoming release.