Additional field types

So far, we have covered number fields and date and time fields. Calcapp offers many more field types, including switch fields (which can be toggled to an “on” position or an “off” position), text drop-down fields, number drop-down fields as well as regular text fields. They are covered by this guide.

Switch fields

To turn a field into a switch field, select Switch from the drop-down menu in the inspector (which reads Text in the image below). Switch fields can be used by users to toggle logical values (from TRUE to FALSE and back again).

Here’s an example of switch fields in use, including the preview sidebar which shows what switch fields look like in a finished app:

Switch fields

This simple app has a single form screen, Switches, holding three switch fields and a text field. The text field displays one value when all fields have been toggled to the “on” position and another when at least one is in the “off” position. To do so, this formula is used:

IF(Switch1 && Switch2 && Switch3, "All are switched on!", "At least one is missing")IF(Switch1 && Switch2 && Switch3; "All are switched on!"; "At least one is missing")

Switch1 && Switch2 && Switch3Switch1 && Switch2 && Switch3 may also be written as follows with the AND function:

AND(Switch1, Switch2, Switch3)AND(Switch1; Switch2; Switch3)
AND({ Switch1:Switch3 })AND({ Switch1:Switch3 })

The last two formulas use techniques explored here.

To turn a field into a drop-down field, select Number drop-down or Text drop-down from the menu in the inspector. Drop-down fields enable users to select from a number of predetermined values (numbers or text strings).

Drop-down fields

The List values section in the inspector enables you to add and remove values. Press the Add value button to add values and the button to remove them. Drag the symbol to reorder values.

To make a value pre-selected when the field is first shown, select the corresponding value from the drop-down in the app designer (which reads °C to °F in the image above).

If you don’t want a value to be pre-selected, select the first blank value from the drop-down. To enable your users to select a blank value, include an value under List values in the inspector with no content.

To use different formulas depending on the value selected by the user, use the IF function. In the image above, the Result field might use this formula to determine if degrees Celsius should be converted to degrees Fahrenheit or the other way around:

IF(Conversion = "°C to °F", (9/5) * Input + 32, (5/9) * (Input - 32))IF(Conversion = "°C to °F"; (9/5) * Input + 32; (5/9) * (Input - 32))

Text fields

To turn a field into a text field, click the abc button in the inspector. Text input fields enable users to enter text and text output fields enable you to display text using formulas.

Text fields can display multiple lines by toggling the Multiple lines property in the inspector. For input fields, this allows you to collect long-form answers (which can be emailed to you):

Input text fields with multiple lines

Using formulas to calculate text

Output text fields display text you “calculate” using formulas. This is particularly useful in conjunction with output text fields which display multiple lines. The most useful formula function to use with an output text field is IF, as you can then display certain text only if a condition holds.

There are many functions that handle text. Use the LEN function to determine the length of a text string and the LEFT, RIGHT and MID functions to return a new text string containing only part of another text string.

The LOWER and UPPER functions return a text string converted to only lower case or only upper case characters, respectively. Use the SUBSTITUTE function to substitute new text for old text in a text string. The FIND function searches for text strings in another text string and returns the position of the found text string. (If no text string is found, an error is returned, which you can determine using the ISERROR function.)

To join two values together and produce a text string, use either the & operator or the CONCATENATE function.

Text must be enclosed in quotation marks in formulas. If a literal quotation mark should be part of a text string in a formula, type two consecutive quotation marks ("").

Here is a sample app which gives an assessment of a given temperature:

Output text fields with multiple lines evaluating a temperature

The following formula is used in the image above:

"The temperature (" & Temperature.FormattedValue & ") is " & IF(Temperature > 85, "untolerable! It's really time to get an air conditioner!", "quite pleasant! It's a nice time of the season.")"The temperature (" & Temperature,FormattedValue & ") is " & IF(Temperature > 85; "untolerable! It's really time to get an air conditioner!"; "quite pleasant! It's a nice time of the season.")

This formula joins text strings together using the & operator and uses the IF formula function to select one of two text strings based on the temperature entered by the user.

You can type NumberField1.FormattedValueNumberField1,FormattedValue to access a text string version of the number of the field, which has been formatted according to your instructions.

Validating text fields

Sometimes, you need to alert your users if they enter incorrect text in a text field. If you collect phone numbers or email addresses, for instance, you want to alert users to mistakes, such as a missing “@” symbol in an email address.

You may recall that validation can be used to ensure that numbers are within an interval you specify. For text fields, you need to associate a formula with the Valid property to do validation. Press the fx button next to the Valid property to edit the formula, which must evaluate to either TRUE or FALSE.

You can use a simple text-processing function like LEN or FIND (see above) to do the validation. With this formula, the value of the field TextField1 must contain four characters or more:

LEN(TextField1) >= 4LEN(TextField1) >= 4

The function REGEXMATCH may be used for more advanced needs. To ensure that a text field contains a valid US phone number (like “(541) 754-3010”, “541.754.3010” or “541-754-3010”), use this formula:

REGEXMATCH(TextField1, "^(?\d{3})?[\s.-]\d{3}[.-]\d{4}$")REGEXMATCH(TextField1; "^(?\d{3})?[\s.-]\d{3}[.-]\d{4}$")

The second parameter to REGEXMATCH is a regular expression, which can not only be used to validate text fields but also to extract and transform text. Our blog has a guide to regular expressions and Calcapp’s regular expression functions.

Here are two more useful regular expressions. To recognize a US Social Security number, use this formula:

REGEXMATCH(TextField1, "^\d{3}-\d{2}-\d{4}$")REGEXMATCH(TextField1; "^\d{3}-\d{2}-\d{4}$")

To recognize an email address, use the ISEMAIL function (which uses a complex regular expression internally):


Continue reading about handling tabular data »