Working with formulas
If you’re used to spreadsheets, you will have an easy time working with Calcapp’s formulas. The largest difference is that you must refer to fields by name, not by position and that you don’t need to start formulas with an equals sign. Most of Calcapp’s 271 formula functions — which range from color-, text- and date-manipulation functions to functions in statistics, finance, engineering and trigonometry — are also supported by popular spreadsheets. (Functions which handle tabular data, such as VLOOKUP, HLOOKUP, INDEX and MATCH, are not yet supported.)
The reference sidebar
The inspector is housed in the sidebar on the left side of the window. This sidebar can also be used to display documentation for functions, operators and properties:
Access the reference sidebar by either selecting Reference from the drop-down menu in the inspector, by clicking the fx button (by the formula bar) or by selecting Insert function… from the menu.
The drop-down menu (which initially reads Commonly used functions) groups formula functions into various categories, such as finance, statistics, engineering and text. When you click a function, documentation appears in the lower half of the sidebar.
The reference sidebar also contains documentation for operators. An operator commonly appears between values (such as “+”) and tells Calcapp what to do with the values. Select All operators from the drop-down menu to get an overview of the operators supported by Calcapp.
Finally, all properties which can be set through formulas are also covered in the reference sidebar:
A word on types
In common with spreadsheets, a value in Calcapp is either a number, a logical value (TRUE or FALSE) or a text string. This is the type of the value. A function accepts a number of parameters and each parameter must be of a specific type. (This holds for operators too. The addition operator accepts only numbers, for instance.) The documentation in the reference sidebar specifies if a parameter must be a number, a logical value or a text string. Some functions, such as IF (seen in the first image), are special and have parameters that may be of any type.
Unlike a spreadsheet, you will get an error if the types don’t match. For instance, IF(123, 2, 4) will fail, because the first parameter must be a logical value (evaluating to either TRUE or FALSE). A spreadsheet would try to interpret the number 123 as the logical value TRUE. With Calcapp’s stricter error checking, you catch errors faster:
To convert a value to a number, a logical value or a text string, you can use
the Calcapp-specific formula functions TONUMBER, TOLOGICAL and TOTEXT. Use
FORMATNUMBER to turn a number into a formatted text string which takes the app
language into account. You can also type
NumberField1.FormattedValue to return
a number field’s value as a formatted text string.
Optional parameters and variadic functions
Some function parameters are optional, meaning that you can leave them out. Unlike some spreadsheets, optional parameters in Calcapp are always the last parameters. In the documentation in the reference sidebar, they are surrounded by brackets: [Number optionalParameter]
Some functions are said to be variadic, meaning that they accept an arbitrary
number of parameters. MAX is such a function.
MAX(x, 0) and
MAX(x, y, z, 4)
are both equally valid. Such parameters are followed by an ellipsis in the
documentation: Number numbers…
Inserting field names in formulas
When you’re editing formulas, you often reference other items, such as fields. Instead of typing their names manually, you can simply click on them while editing formulas. When you’ve reached a spot in your formula where it makes sense to enter a reference (such as after you’ve typed “+” or the name of a function plus an opening parenthesis), the items in the app designer will turn purple to indicate that you can click them to insert their names at the caret position:
If you find yourself including the same formula fragments in many different formulas, you can store the part you often repeat in a hidden field and reference it from other fields. A hidden field is just like a normal field, but is never shown to your users. Make a field hidden by toggling its Visible property in the inspector:
In the image above, the Celsius and Fahrenheit hidden fields are used to convert the input from degrees Celsius to degrees Fahrenheit and vice versa. The Result field uses an IF formula function to determine what conversion was selected by the user.
Sometimes evaluating a formula leads to an error when you run your app. Examples include when you divide a number by zero or when a parameter to a function is unexpected. For instance, the documentation may specify that a value must be a positive number and providing a negative number or zero then leads to an error. (Passing a number when a text string is expected, for instance — a type error — prevents you from launching your app and does not lead to an error visible when running the app.)
If you’re used to spreadsheets, you’ll find Calcapp’s error handling familiar. Here’s what happens if you divide a number by zero:
A short error code is displayed in red instead of the value if there is an error. If you hover over the field with your mouse, a more descriptive error message is displayed in a tooltip (pinpointing the offending operator or function).
Calcapp supports a number of functions enabling you to work with errors. You can use ERROR.TYPE to get more information on an error, ISERROR to determine whether a value is an error and IFERROR to return a certain value if a value is an error and another value otherwise.
Sometimes, it is perfectly acceptable for a formula to yield an error when
evaluated. You can use IFERROR to display nothing if a value evaluates to an
error. The formula in the image above could be changed to
Divisor, BLANK()) to ensure that a potential error is not seen by the user.
Error codes are visible in apps to help you diagnose formula errors. They are not shown in apps you share, though, ensuring that your users never see them.
Functions specific to Calcapp
Of the 37 formula functions only supported by Calcapp, all but 13 relate to
manipulating colors. There are the aforementioned TONUMBER, TOLOGICAL,
TOTEXT and BLANK functions as well as ISDEFINED.
NOT(ISBLANK(Field1)) and is provided for convenience. BLANK
returns a blank value, that is, the same value that is assigned to an input
field before a user has entered anything into it. Due to Calcapp’s stricter type
checking, the usual spreadsheet trick of assigning the empty string (“”) to an
output field to indicate that there is no value only works for text fields and
not for number fields or logical fields. In Calcapp, write
BLANK() instead to
indicate that there is no value.
There are eight functions which turn numbers into formatted text strings and back again, taking the app language into account. FORMATNUMBER is the most prominent example. Explore them all in the reference sidebar.
Now that you have learned how to work with formulas, learn more about adding navigation to your app »