You can now access all items of a form group, a form screen or the entire app as an array. An item in this context is a button, a field, a text box, a navigator, a named value or a form group.
To illustrate the many uses of this new feature, let’s look at a couple of examples:
Summing all number fields of a form group
Let’s say that the form group Readings contains number fields whose values should be summed. This formula returns the sum:
Calcapp interprets the formula above as this equivalent formula:
If the Readings form group only consists of the number fields Reading1, Reading2, Reading3 Reading4, in that order, these two formulas are also equivalent (and worked in our previous release):
The chief advantage to using the Items property instead of a range is that you no longer need to worry about updating the formula when you add more fields. If you add a fifth number field, Reading5, that should be included in the calculations, the two formulas above need to be updated. The first two formulas, though, will continue working, making for fewer errors and less error-prone maintenance work.
Determining if all switch fields have been toggled
Assume that the form group Switches only consists of four switch fields, Switch1, Switch2, Switch3 and Switch4, in that order. These traditional formulas return whether they have all been toggled to their “on” positions:
Ranges can again provide a space-saving way to express the formulas above:
Again, the Items property provides a better way, which does not require that the formula is updated as switch fields are added:
Determining if all fields are valid
The array of items holds the items themselves, including all their properties, and not just their values.
As such, this formula returns whether all fields of the Switches form group are valid:
Above, the Items property must be spelled out, as the Switches form group does not have a property named Valid, but the items of Switches do.
Making the background color of the app red if any field is invalid
So far, we have only looked at the Items property of form groups. Form screens also support this property, meaning that any example above can work at the level of a form screen and not just a form group.
In fact, we can access all items of the entire app, all at once. This formula returns whether all fields are valid:
(Your app is normally named
App in formulas, but
can be renamed on the first screen that is shown when you open an app
in Calcapp Creator.)
This formula returns whether any field is valid (meaning that a single valid field makes the formula return TRUE):
This formula returns whether any field is invalid, by turning all TRUE elements of the array to FALSE, and vice versa, using NOT:
Putting it all together, this formula returns the color red if any field is invalid, and otherwise returns a blank value:
If this formula is associated with the BackgroundColor property of the first screen, and no other screens have their background colors set, the end result is that the entire app turns red when a single field is invalid.
Enabling a button only if all fields have been filled out
Let’s assume that the DataEntry form screen consists of a number of fields, all of which must be filled out in order for a button at the bottom of the screen to become enabled.
This formula, which should be associated with the Enabled property of the button, does just that:
If all fields need to be filled out and also pass validation, use this formula instead:
Accessing only certain types of fields
Most of the time, accessing the Items property works well. While it includes not just fields, but also buttons, text boxes, navigators, named values and form groups, most formulas involving Items can only be interpreted one way.
Here’s one such formula:
It returns whether all fields of Screen1 are valid, simply because only fields support the Valid property. The meaning of that formula may change in the future, though, if other items gain support for a property named “Valid.”
Sometimes, it’s preferable to be more specific. We also provide the following properties:
(The links above lead to our full documentation, which contains many more examples than this blog post.)
The meaning of this formula won’t change in the future, even if buttons gain a property named “Valid.”
Also, while AND(Screen1)AND(Screen1) does return whether all switch fields of Screen1 have been toggled to their “on” positions, the formula may be hard to understand. This alternative is probably clearer:
Sometimes, being more specific is the only way to write a correct formula. Consider a screen, Screen1, which consists of a number of date and time fields, as well as number fields. If we want to return the sum of all its number fields, we may try this formula:
However, you will find that the returned sum is not correct. The reason is that the formula above also includes the date and time fields and their values.
Remember that number fields are not the only fields whose values are numbers, that also applies to date and time fields. (Details here.) In effect, the formulas above return the sum of all number fields and all date and time fields of Screen1, which is probably not what we want.
The solution is, again, to be more specific. This formula does what we want:
If we combine the Fields property with the FILTER function, we can apply an operation to just a few fields and not to all of them.
For example, this formula returns the sum of all fields of Screen1 which are considered Valid, and ignores the others:
This formula returns the sum of all number fields whose values are greater than zero:
This formula returns the number of text fields whose backgrounds are red:
This formula returns the largest value entered in a number field whose label includes the word “mandatory”:
Finally, this formula returns the average value entered in all number fields whose names start with “F”: