Advanced formulas

Basic knowledge of Calcapp formulas goes a long way in enabling you to build featureful apps. However, there are some advanced techniques that make you more productive, or enable you to realize things that would otherwise not have been possible. This guide explores these techniques.

Arrays and ranges

Let’s say that you want to calculate the sum of five fields. You add the following fields to a form screen named MyFormScreen, in this order: Field1, Field2, Field3, Field4 and Field5.

These formulas both get the job done:

The formulas above are fairly verbose. Calculating the sum of several hundred fields in this way wouldn’t be workable.

The SUM function can also be invoked with an array, which is a value that contains many other values (elements). They are written between curly braces.

This array holds the values 1, 2 and 3:

You may recognize this as an array constant from spreadsheets. Arrays in Calcapp are more powerful, though, as you can use any calculations to derive the array elements and reference any values in your app.

This array is also valid:

Returning to SUM, here’s how we can calculate the same sum by passing an array:

This formula is even longer, though. Is there a way to make it shorter?

Yes, using ranges:

In Calcapp, a range is simply an easier way to write an array. The range above includes Field1, Field5 and all fields that appear between them in your form screen.

To illustrate that ranges are just arrays, this formula also works:

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

Use IF to determine if an element should be part of an array:

In the formula above, Field1 is only part of the array if its value is greater than 5.

Use | to join arrays together with other arrays or values and SIZE to return the number of elements in an array.

Accessing fields and other items as arrays

All the fields that belong to a form group, a form screen or the entire app can be accessed as a single array. The five fields of MyFormScreen, for instance, can be accessed through the Items property.

If only those five fields are part of MyFormScreen, the following formulas are equivalent:

The chief advantage to using Items instead of a range is that you no longer need to worry about updating the formula when you add more fields.

The Items property can often be accessed without typing it explicitly. These equivalent formulas return the sum of all fields of MyFormScreen:

This formula returns whether all fields of MyFormScreen are valid:

If this formula is attached to the BackgroundColor property of a screen, it — and possibly the screens that follow it — turns red if a single field of the entire app is invalid:

If this formula is associated with the Enabled property of a button, it only becomes enabled when all fields of MyFormScreen have been filled out:

Above, we need to spell out MyFormScreen.ItemsMyFormScreen,Items, instead of just referencing MyFormScreenMyFormScreen. The reason is that ISDEFINED(MyFormScreen)ISDEFINED(MyFormScreen) applies to the form screen itself, and not its items. As the form screen is always defined, that formula would always return TRUE.

Calcapp-specific functions and extensions

We support a large number of functions specific to Calcapp, including color functions, FORMATNUMBER, PARSENUMBER and REGEXMATCH. We also support extensions to existing functions, making them more powerful or making formulas using them easier to read and write.

SUMIF calculates the sum of the given values, but only considers values which pass a test you supply. This formula returns the sum of all field values which are greater than 5:

This formula returns the sum of all field values which are greater than the value of Field1 (using the & operator, which joins text strings together):

If you need multiple conditions, which all must pass, the SUMIFS function is available.

However, what if you need more flexibility? Let’s say that you want to return the sum of all field values which are either greater than 5 or are odd numbers. This can’t be handled by either SUMIF or SUMIFS.

A Calcapp extension allows you to use a formula fragment instead to determine which numbers should be considered. A formula fragment is a regular part of a formula, but with access to special values.

SUMIF makes the array element under consideration available under the Element name. These two formulas are equivalent:

By using a formula fragment, you have access to the full power of the formula language. Expressing “greater than 5 or is an odd number” is easy with a formula fragment:

We also offer extensions that make formulas easier to read and write. To illustrate this, consider the SORT function, which returns a sorted version of an array.

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

To sort in descending order instead (where greater values appear first), -1 can be given as the second parameter.

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

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

While longer, using text instead of a hard-to-remember number can help make formulas clearer. XLOOKUP, XMATCH and FORMATDATE are other functions which use a similar system.

The LET function

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

At its core, LET makes it possible to assign names to values, which can be used in calculations that appear to the right in the same formula.

The best way to demonstrate this is with an example:

The formula above first multiplies the values of the Width and Height fields together and assigns the name Area to the resulting value. It then multiplies this value by 1500, assigning it the name Cost. Finally, it adds 1000 to the Cost value and returns the sum.

The formula above can also be written (Width * Height * 1500) + 1000(Width * Height * 1500) + 1000, which is significantly shorter, but isn’t as descriptive.

LET is primarily useful when you need to repeat formula fragments in a formula, allowing you to assign a single name to the repeating part and then refer back to it. That can make formulas shorter and make apps perform better, as it gives Calcapp less work to do.

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