One of our most anticipated features will finally ship with our next release: support for cross-references. A cross-reference enables you to refer to a field on another panel from a formula. Previously, while you could build large apps with Calcapp, every panel was essentially an island unto itself, as formulas could only reference other fields that were part of that same panel.
Users often ask us if they can use Calcapp to convert their spreadsheets to apps. For large spreadsheets with many interconnected parts, our answer is often “yes, but you will need to put hundreds of fields on the same screen, which will lead to a poor user experience.” As you can imagine, we’re tired of giving that disappointing answer and we’re excited that this feature omission will soon be a thing of the past.
Referencing other panels
To reference a field on another panel in a formula, first type the name of the
panel, then an exclamation point and then the name of the field you want to
reference. This is a syntax we have borrowed from Excel. (OpenOffice and
LibreOffice use a period instead of an exclamation point.) A formula referencing
a field found on another panel might look like this:
Here’s the formula bar displaying a formula which makes heavy use of cross-references:
In order to reference panels by name, you need to be able to assign them names. With our next release, you assign a name to a panel by selecting the panel itself and changing the name the way you normally change field names (using the name box to the left of the formula). To select a panel, just click the blue toolbar where you ordinarily enter the panel title and which houses the back and forward button. When you select it, its color darkens:
(In the near future, there will be properties for panels and the inspector will feature those. For now, though, the inspector is blank when selecting a panel.)
Here, we have renamed the calculation panel CalculationPanel1 to Main. When doing so, all formulas are automatically updated to use the new name.
You don’t have to explicitly name panels. Like fields, panels
will automatically use the caption or title you have assigned to it. If you
title your panel “Data entry,” it will automatically be named “DataEntryPanel,”
meaning that you reference fields on it using a formula such as
Most users don’t type field names manually, they click fields while writing formulas to have their names inserted automatically into the formula. Our next release won’t support this ease-of-use feature for cross-references, but we will work to add it as soon as possible. For now, you’ll need to manually type cross-references.
Under the hood
A lot of work has gone into this one feature. Calculations are now done differently in that only the formulas that need to be run are run. (Earlier, all formulas of the panel you were on were run when a single value was changed.) When you change the name of a field or a panel, all formulas referencing said field or panel are automatically updated (and remember that names are automatically changed when you update field captions or panel titles, so it happens a lot). Ensuring that this work happens with no perceptible slow-downs has involved quite a bit of work, now that any part of your app can reference any other part.
You’ll also note a few other changes: text boxes you add to apps, options you add to list panels, buttons you add to calculation panels and the groups they are in are now assigned names automatically. There are two pre-made text boxes at the top and bottom of calculation panels and list panels and you’ll note that they are now also assigned names.
Normally, these elements are automatically assigned unique names. This is what happens if you manually assign duplicate names:
All this may seem quite pointless — why would you want to assign names to text boxes, list panel options, calculation panel groups or buttons? It is, actually, pointless, but won’t be for long. This is done to prepare for the arrival of calculated properties. List panel options, text boxes, calculation panel groups and buttons will all support a new calculated property named Visible, which will enable you to determine whether they are visible or not using a formula. (Buttons will also support associating a formula with the Enabled property, making it possible to determine whether a button is grayed-out using a formula.) That means that you will be able to reference whether a text box, say, is visible from your other formulas and to do that, text boxes and their ilk need to have names.
It used to be that only fields, which are always part of calculation panels, could be referenced from formulas, but now you’ll be able to reference many other parts of your app. We internally refer to fields, buttons, groups, list panel options and text boxes as variables and to the panels that hold them as calculations. We expect to add many more variable and calculation types in the future.
Cross-references and calculated properties will enable you to create apps that are unimaginable with the current release, and we think that it opens up the platform as much as the June, 2016 release did. That release added support for text fields, date and time fields, switch fields, 244 formula functions and also revamped error handling.
We’re looking forward to seeing the apps you build with these new features!