When we released our big update to our formula engine in November last year, we promised that more features related to the formula engine were coming. Today, we’d like to announce that we’re nearing the end of our current development cycle, and that these features will be released in the coming months.
Here’s a sneak preview of the new features:
Action formulas
We have teased action formulas since 2020, and they are finally almost ready to ship. At its core, an action formula has access to special functions which perform actions. Our next release will allow you to create special buttons that run these formulas when the button is pressed.
These are some of the things you’ll be able to do with an action formula:
- Send a report, but only if the entered data is valid. Otherwise, show the user an alert box.
- Ask the user a series of questions, before sending a report incorporating the entered answers.
- Create a button that calculates values only when pressed.
- Send multiple reports, incorporating different information, to different email addresses.
- Open a report on the user’s device, while also sending a message to the team’s internal Slack channel. (Integration with Slack, and other third-party services like Google Sheets, Excel and Salesforce will require a third-party service like Zapier, just like today.)
- Reset all fields, but only if the user first consents by pressing a button to that effect in a message box. Then, move the user back to the first screen, and ensure that previously-hidden text boxes are once more visible.
This action formula asks the user for their name and greets them once the name has been entered:
AWAIT(PROMPT("What's your
name?"), BANNER("Hi " & Result & "!"))
This action formula sends a report, containing all fields of the app, and shows a banner if the report could not be sent successfully:
AWAIT(EMAILREPORT({ App },
"test@example.com"), OnFailure: BANNER("Could not send
report"))
Finally, this action formula asks the user to enter a number, and once the user has done so, the entered number and the entered number divided by two are shown in a banner:
AWAIT(PROMPT.NUMBER("Enter
number:"), BANNER("Half of " & Result & " is " & (Result / 2) &
"."))
Many more actions
Today, buttons can only email and open reports, reset fields and send data to third-party services like Zapier. Soon, many more actions will be available:
- Copy text to the system clipboard.
- Show an alert box, which users need to dismiss by pressing a button.
- Open an email compose window on the user’s device, with pre-filled fields.
- Open a text message (SMS) compose window, again with pre-filled fields.
- Dial a phone number.
- Open a web page in the user’s web browser.
- Move forward to a screen you determine.
- Move backward to a previously-visited screen you determine.
- Open a map using the user’s installed map app, or on the web.
- Print the current screen or save it as a PDF document.
All of the properties of these new buttons are calculated properties, meaning that their values can be determined using formulas.
Also, the existing buttons have also been revamped, so that many more of their properties can be determined using formulas. For instance, you’ll be able to determine if a report is sent as a CSV file or as a PDF document using a formula.
All the new features are also available to action formulas, which support even more features:
- Show a banner message, which disappears automatically.
- Ask the user for confirmation.
- Ask the user to enter a text string or a number, which can then be used to take further action.
Action formulas will also be able to assign any value to any property
using the new := operator.
Field1.Visible :=
FALSE will hide Field1.
Named parameters
Some functions accept a large number of parameters, which can make
formulas hard to read. Our new release includes support for named
parameters, allowing you to precede a parameter value with its
name, followed by :.
For instance, these two formulas will be equivalent:
FORMATNUMBER(23,
2, 2, FALSE, 1, FALSE, "$")
FORMATNUMBER(Number: 23,
MinimumNumberOfDecimalPlaces: 2, MaximumNumberOfDecimalPlaces: 2,
OmitThousandsSeparators: FALSE, MinimumNumberOfIntegerDigits: 1,
UseAccountingStyle: FALSE, LeadingUnit: "$")
All parameters don’t need to be named, but once a parameter has been named, the remaining parameters must also be named.
Named parameters make it possible to provide parameters out-of-order and to omit optional parameters that normally would have been expected to precede a parameter, had it not been named. Omitted optional parameters use default values.
The formula above uses default values for all parameters other than
Number and
LeadingUnit. As such,
those optional parameters can be removed from the formula:
FORMATNUMBER(23,
LeadingUnit: "$")
We suggest that you use named parameters when they help make a formula easier to read.
Named parameters were added to Calcapp to make existing functions like FORMATNUMBER easier to use, but also in consideration of new functions like EMAILREPORT and OPENREPORT. Those functions support a very large number of optional parameters, and named parameters will enable you to only provide the parameters you care about, omitting the rest.
The LET function
The new LET function, which is also part of Excel 2021, allows you to name values you use in formulas. That means that you no longer need to repeat calculations that otherwise would have appeared multiple times, making formulas run faster and making them shorter and easier to read.
This formula should be read as “give X the value 2, Y the value 4 plus X, and return Y divided by two”:
LET(X := 2, Y := 4
+ X, Y / 2)
By assigning names to values, you can make formulas easier to read. If a formula consists of many moving parts, being able to name values serves as valuable documentation.
This is especially important for apps maintained by multiple people, allowing other members of your organization to more easily understand how your formulas work. Being able to name values has been available to traditional software developers for decades.
LET adds a lot of value to regular formulas, but works especially well with action formulas. If you need to, say, send a report with certain values and reset them afterwards to blank values, doing so without LET invariably leads to formula duplication.
Here’s an attempt without LET:
EMAILREPORT({
Field1, Field3:Field10 }, "test@example.com"); RESET.BLANK({ Field1,
Field3:Field10 })
With LET, there is no need to duplicate what fields to send and reset:
LET(Fields := {
Field1, Field3:Field10 }, EMAILREPORT(Fields, "test@example.com");
RESET.BLANK(Fields))
Being able to type := to assign values to
fields is a Calcapp extension. Excel 2021 requires that you use
, or
; instead
(depending on your language settings). We think that using
:= makes
formulas easier to read, but you’re also welcome to use , or ; if you’re used to
that syntax from Excel.
Named values
Calcapp gained support for hidden fields way back in 2016. They allow you to assign names to calculations and use them from other formulas. This is somewhat similar to LET, but LET allows you to name values that are only accessible from a single formula, whereas hidden fields are accessible to the entire app.
Hidden fields have one major drawback, though: they can only represent numbers, logical values and text strings.
That wasn’t a problem before our new formula engine was introduced last year, because before that time, Calcapp actually only handled numbers, logical values and text strings. Today, formulas can reference many more things, including fields, buttons and — most importantly – arrays.
Simply put, there is currently no way to name an array and make it available to other parts of your app. That means that arrays have to be duplicated left and right in your app, which is especially worrisome for arrays that contain a lot of data (possibly converted from a spreadsheet using our app).
With named values, those issues go away, as a named value can represent anything a formula can return in Calcapp, including arrays. They are, by default, hidden, but you can make them visible to help spot problems in your app (optionally using a formula).
As such, visualizing arrays, as suggested by this tip, will no longer require text fields and the TEXTJOIN function.
An editor for your tabular data
Historically, we have never done a very good job of helping you work with data maintained as tables in a spreadsheet. In 2018, we introduced the CHOOSE function and the Index property of drop-down fields, allowing values to be selected based on what value the user selected from a drop-down field.
That release also introduced a new app, written in Calcapp using advanced new text processing functions introduced just to make that app a reality. The app allows Calcapp authors to convert spreadsheet tables to CHOOSE formulas, one column at a time.
CHOOSE is a poor substitute for true table lookups, though. This issue was finally rectified last year, when our new formula engine introduced support for the new XLOOKUP function (which can do everything that HLOOKUP and VLOOKUP can do). There was also a new app for generating XLOOKUP formulas from spreadsheet tables, requiring Calcapp authors to copy and paste data, one column at a time.
While having support for true table lookups is great, the status quo leaves much to be desired. First, converting tabular data to formulas is time-consuming and dull. Having to repeat the process every time the data needs to be updated makes matters worse.
Second, the tabular data can potentially be large, making apps slow to download and use. If multiple formulas need to look up data, they need to repeat the same table data. That means that when that data needs to be updated, it’s easy to miss a formula, making for inconsistent, buggy apps.
Thankfully, those concerns are soon a thing of the past. Our new release will introduce a data editor, allowing you to copy and paste spreadsheet tables in full to Calcapp Creator. When you need to update said data, you’ll be able to bring up the same data editor and make edits either manually, or by pasting data from your spreadsheet.
The new data editor is powered by named values (see above). Every column causes a new named value to be created (or altered), containing arrays which can be passed directly to functions like XLOOKUP and SUMIFS.
If you like, you can create screens that are not reachable by your users which consist entirely of named values. Every such screen represents a table.
Imagine a screen named Employees, with the following named values (created automatically from data by the data editor): Name and Salary. You’ll be able to find the salary of an employee using this XLOOKUP formula:
XLOOKUP(Name,
Employees!Name, Employees!Salary)
Back in 2020, we promised much better support for tabular data in a future Calcapp 4 product. While the new data editor goes some way towards realizing those ambitions, it does not fully do so.
Specifically, tables remain read-only, unable to incorporate changes made in the app. If you want data entered in the app to be reflected in a spreadsheet table, you need to use a third-party product, such as Zapier, with your app, and even then, the app cannot reflect the entered data the next time it is started.
We will soon begin to explore ways of making apps produced with Calcapp truly data-aware.
Minor feature additions
Today, all links opened from an app open in a new window or tab. This is appropriate for stand-alone apps, but not always for apps embedded in an existing website (where you may want links to open in the existing window or tab).
Our new release will make this behavior fully customizable. The selected behavior will, for the time being, apply to all text box links, with no ability to determine the behavior on a per-link basis. However, the new buttons that open web pages will support selecting the behavior on a per-button basis, and the corresponding action function BROWSE will also allow this behavior to be determined at a more granular level.
Finally, our new release will make the new formula documentation we introduced last year far more accessible. Specifically, when you edit the formula of a property in Calcapp Creator, a button will allow you to visit the documentation for that property directly. Also, a link in the inspector will allow you to browse the documentation for the element you’re editing.
We think that this release, with its support for action formulas and an editor for spreadsheet tables, has the potential to be even more important than our release from last year, which was our most ambitious release to date. This release may not be bigger, strictly speaking, but it arguably introduces features that are even more desirable than those we introduced last year.
Look for the new release in the coming months. Be sure to subscribe to our newsletter (bring up the menu with your email address on it in Calcapp Creator and select Account settings…) to be notified when that happens.