Working with formulas

If you’re used to spreadsheets, you will have an easy time working with Calcapp’s formulas. We support around 400 formula functions, including modern Excel marvels like XLOOKUP and FILTER, spreadsheet classics like SUM, COUNTIFS, INDEX and MATCH as well as the power user favorites MAP and REDUCE.

The largest difference between spreadsheets and Calcapp 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.

Our formula and property documentation come with several thousand examples, making it easy to get started.

When typing formulas, Calcapp Creator’s autocomplete feature helpfully provides suggestions in a menu that pops up automatically. To force it to appear, press Ctrl+Space on PC, or ⌘+Space on Mac.

When you’re ready to learn more about formulas, consult our advanced formula guide.

Decimal points vs. decimal commas

Formulas can be written using either decimal points or decimal commas. When you first open Calcapp Creator, it asks your preference. (If you need to change it, select Account settings… from the menu.)

By default, the formula samples that appear on this website use decimal points, not decimal commas. Formula samples on a black background allow you to change this setting by pressing the button with a decimal point and a decimal comma.

IF and SWITCH

Calcapp’s version of IF can be used with multiple conditions without having to include additional parentheses. Here’s a formula which returns 10 if the value of Field1 is 1, 20 if it is 2, 30 if it is 3 and 99 otherwise:

Use SWITCH when you need to test the same value for equality against other values. This formula is equivalent to the one above:

New logical operators

An operator typically appears between values, such as + and & (which joins text strings together). Some operators appear after the value, such as % (which divides the value by 100).

&& does the same thing as the standard AND function (which returns TRUE only if all values are TRUE), but appears between the values instead of before them.

These formulas are equivalent:

In the same vein, || does the same thing as the standard OR function (which returns TRUE if at least a single value given to it is TRUE), but appears between the values instead of before them.

These formulas are equivalent:

Finally, the ! operator does the same thing as the standard NOT function (which returns FALSE if the given value is TRUE and TRUE if the given value is FALSE).

These formulas are equivalent:

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:

You can also use the autocomplete feature to insert field references. Simply start typing the name or label of any field in the app, and autocomplete will suggest the proper way to reference the field.

Hidden fields and named values

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:

A hidden field can only reference numbers, text strings, TRUE and FALSE. A named value, however, can represent anything a formula can return, including screens and arrays.

Create a named value by creating a new field and selecting Named value from the field drop-down in the inspector:

Handling errors

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.

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 return an error when evaluated. You can use IFERROR to display nothing (a blank value) if a value evaluates to an error:

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.

Now that you have learned how to work with formulas, learn more about adding navigation to your app »