Tip: Debug arrays by inspecting their elements

November 12, 2021

Let’s say that you need the sum of an array, expressed as a range. Let’s also assume that a form screen holds five number fields, Field1, Field2, Field3, Field4 and Field5, in that order.

You write the following formula:

SUM(Field1:Field5)SUM(Field1:Field5)

The resulting sum isn’t what you expect. What to do?

If SUM, or any other function, isn’t returning the value you expect, you need to look at the parameters it receives. Normally, this would be simple — just add temporary fields to your app with its formulas set to the function parameters you want to inspect. When you run the app, you get to see their values, which helps you figure out why the function isn’t returning the result you expect.

In this case, this is the formula we need to inspect:

Field1:Field5Field1:Field5

When given as a parameter to SUM, the formula above returns an array of numbers. However, there isn’t (yet?) a field which can visualize arrays.

To solve this, you need to convert the formula to a text string, which enables you to display the array of numbers using a text field. For this purpose, the TEXTJOIN function is perfect.

Here’s how to use it to visualize Field1:Field5Field1:Field5:

TEXTJOIN(", ", FALSE, Field1:Field5)TEXTJOIN(", "; FALSE; Field1:Field5)

As its name implies, TEXTJOIN takes an array and converts it to a text string. To do so, it joins all elements of the array together, separating them with the first parameter, ", "", ". The second parameter, FALSE, instructs TEXTJOIN to include blank values, which is important when we need to visualize an array.

The formula above produces a text string like the following, if Field4.ValueField4,Value is blank:

1, 2, 3, , 5.

Technically, Field1:Field5Field1:Field5 is an array of number fields. TEXTJOIN only knows how to process arrays of numbers, logical values, text strings or colors, so it asks Field1:Field5Field1:Field5 to convert itself to such an array. Number fields have values which are numbers, meaning that the formula above is equivalent to this formula:

TEXTJOIN(", ", FALSE, { Field1:Field5 }.Value)TEXTJOIN(", "; FALSE; { Field1:Field5 },Value)

While inspecting the actual values is what you want most of the time, you can also inspect the labels of the fields. Doing so is easy:

TEXTJOIN(", ", FALSE, { Field1:Field5 }.Label)TEXTJOIN(", "; FALSE; { Field1:Field5 },Label)

The formula above produces a text string like the following:

Field 1, Field 2, Field 3, Field 4, Field 5.

Use this tip whenever you need to inspect what elements an array holds.