• Learn
  • Blog
  • Pricing
  • About
  • Sign in

Beyond numbers

Numbers can be formatted as plain numbers, as percentages and fractions, using scientific notation and as currency. They can also be formatted as dates and times. Not all values in Calcapp are numbers, though. Values can also be logical values and text strings and there are formatting options for these types too.

Logical values

Switches can be used by users to toggle logical values (from TRUE to FALSE and back again). Here’s an example of switches in use, including the preview sidebar which shows what switches look like in a finished app:


As you can see, this is a simple app with three switches and a label which displays one value when all switches 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")

(The formula Switch1 && Switch2 is equivalent to AND(Switch1, Switch2) in Calcapp.)

To turn a field into a switch field, simply select Switch from the drop-down menu in the inspector. (The menu that says Text in the image above.)

Text fields

To turn a field into a text field, click the abc button in the inspector (as seen in the image above). 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 (again, refer to the image above). For input fields, this allows you to collect long-form answers (which can be e-mailed 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 a number of other functions for handling text, which are documented in the Text category in the reference sidebar. 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. Type two consecutive quotation marks ("") to produce a single quotation mark.

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.")

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.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 e-mail addresses, for instance, you want to alert users to mistakes, such as a missing “@” symbol in an e-mail 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. The formula LEN(TextField1) => 4 requires that the value of the field TextField1 contains four characters or more, for instance.

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}$")

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 three 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}$")

Use this formula to recognize an e-mail address:

REGEXMATCH(TextField1, "^[^@]+@.+\.[^\.$]{2,}$")

Continue reading about using spreadsheet data with drop-down fields »