Advanced formulas

Basic knowledge of Calcapp formulas enables you to build powerful apps. However, advanced techniques can make you more productive and unlock capabilities that would otherwise be impossible. This guide explores these techniques.

Arrays and ranges

Suppose you want to calculate the sum of five fields. You add these fields to a form screen named MyFormScreen in order: Field1, Field2, Field3, Field4 and Field5.

Both formulas work:

These formulas are verbose. Calculating the sum of hundreds of fields this way would be impractical.

The SUM function also accepts an array—a value containing multiple other values (elements). Arrays use curly braces.

This array contains the values 1, 2 and 3:

You may recognize this as an array constant from spreadsheets. Calcapp arrays are more powerful since you can use calculations for array elements and reference any app values.

This array is also valid:

Here’s how to calculate the same sum using an array:

This formula is longer, though. Can we make it shorter?

Yes, using ranges:

A range is simply a shorthand for writing arrays. The range above includes Field1, Field5 and all fields between them in your form screen.

To show that ranges are arrays, this formula also works:

This formula calculates the sum of Field1, Field3, Field4 and Field5:

Use IF to conditionally include elements in arrays:

In this formula, Field1 is only included if its value exceeds 5.

Use | to join arrays with other arrays or values and SIZE to count array elements.

Accessing fields and other items as arrays

Fields belonging to a form group, form screen or entire app can be accessed as a single array. The five fields of MyFormScreen, for instance, are available through the Items property.

If MyFormScreen contains only those five fields, these formulas are equivalent:

The main advantage of using Items over ranges is that you don’t need to update formulas when adding more fields.

You can often access the Items property implicitly. These formulas both return the sum of all MyFormScreen fields:

This formula checks whether all MyFormScreen fields are valid:

This formula, when attached to a screen’s BackgroundColor property, turns the screen red if any app field is invalid:

This formula, when associated with a button’s Enabled property, enables the button only when all MyFormScreen fields are filled out:

Here we must specify MyFormScreen.ItemsMyFormScreen,Items rather than just MyFormScreenMyFormScreen. The reason is that ISDEFINED(MyFormScreen)ISDEFINED(MyFormScreen) applies to the form screen itself, not its items. Since the form screen is always defined, that formula would always return TRUE.

Calcapp-specific functions and extensions

Calcapp supports many specialized functions, including color functions, FORMATNUMBER, PARSENUMBER and REGEXMATCH. We also provide extensions to existing functions, making them more powerful and easier to use.

SUMIF calculates the sum of values that pass a test you specify. This formula returns the sum of all field values greater than 5:

This formula sums all field values greater than Field1’s value (using the & operator to join text strings):

For multiple conditions that all must pass, use SUMIFS.

What if you need more flexibility? Suppose you want to sum all field values that are either greater than 5 or odd numbers. Neither SUMIF nor SUMIFS can handle this.

A Calcapp extension lets you use formula fragments to determine which numbers qualify. A formula fragment is a regular formula part with access to special values.

SUMIF makes the current array element available as Element. These formulas are equivalent:

Formula fragments unlock the full formula language. Expressing “greater than 5 or odd” becomes simple:

We also provide extensions that make formulas easier to read and write. Consider the SORT function, which returns a sorted array.

This formula returns the sorted array { 1, 2, 3 }{ 1; 2; 3 }:

For descending order (greater values first), pass -1 as the second parameter.

This formula returns { 3, 2, 1 }{ 3; 2; 1 }:

As a Calcapp extension, you can specify sort order using text:

While longer, text is clearer than hard-to-remember numbers. XLOOKUP, XMATCH and FORMATDATE use similar systems.

The LET function

The LET function helps you write shorter, more descriptive formulas that run faster.

LET lets you assign names to values for use in later calculations within the same formula.

Here’s an example:

This formula first multiplies Width and Height, assigning the result to Area. It then multiplies Area by 1500, calling it Cost. Finally, it adds 1000 to Cost and returns the sum.

You could also write (Width * Height * 1500) + 1000(Width * Height * 1500) + 1000, which is shorter but less descriptive.

LET is most useful when you repeat formula fragments. You can assign a name to the repeated part and reference it multiple times. This makes formulas shorter and apps faster by reducing Calcapp’s workload.

To learn more about Calcapp, including in-depth tips and detailed looks at our features, read our blog »