# Formulas

You can use formulas to create smarter forms with ArcGIS Survey123.

Previous questions must always be referred to in formulas with the format \${field_name}.

## Operators

The following operators are supported in Survey123:

OperatorDescriptionExample

.

.=1

+

\${question_one} + 4

-

Subtraction

\${question_one} - 4

*

Multiplication

\${question_one} * 4

div

Division

\${question_one} div 4

=

Equal

\${price}=9.80

!=

Not equal

\${price}!=9.80

<

Less than

\${price}<9.80

<=

Less than or equal to

\${price}<=9.80

>

Greater than

\${price}>9.80

>=

Greater than or equal to

\${price}>=9.80

or

Or

\${price}=9.80 or \${price}=9.70

and

And

\${price}>9.00 and \${price}<9.90

mod

Modulus (division remainder)

\${question_one} mod \${question_two}

The following functions are supported in Survey123:

FunctionDescriptionExample

selected(question, value)

Checks whether answer is selected. Used for select_one and select_multiple questions.

selected(\${question_one}, 'a')

count-selected(question)

Returns the number of selected answers for select_one and select_multiple questions. Also returns the number of attached files for image, audio, and file questions using the multiline appearance.

count-selected(\${question_one})

string-length(question, expression, or value)

Returns the length of a nonempty string.

string-length(\${question_one})

substr(question, start, end)

Returns the substring beginning at the specified start and extends to the character at index end -1, where start and end begin at 0.

substr(\${question_one}, 1, 2)

not(expression)

Returns a false value if the expression would return true, and a true value if the expression would return false.

not(selected(., 'yes'))

if(condition, a, b)

If the condition evaluates to true, returns a; otherwise, returns b.

if(selected(\${question_one}, 'yes') and selected(\${question_two}, 'yes'), 'yes', 'no')

true()

True

true()

false()

False

false()

uuid()

Returns a random UUID string.

uuid()

random()

Returns a random value between 0 (inclusive) and 1 (exclusive).

random()

today()

Returns today's date, internally stored as local midday. Used in date questions.

today()

now()

Returns a time stamp for this instant. Used in time and dateTime questions. Behaves the same as today() in date questions.

now()

once()

If a question already has a value, returns the existing value. Useful when using random() or uuid() in a repeated question to ensure the value doesn't change when you browse through the repeat records in the form.

once(uuid())

boolean(question, expression, or value)

Returns true if the value provided is not null.

It's recommended that you use boolean-from-string() instead.

##### Caution:

This function will always return true in the Survey123 web app. For alternatives, see Empty values.

boolean(\${question_one})

number(question, expression, or value)

Converts to number. Conversion varies depending on data type.

##### Note:

If this function is empty, it will return NaN and the question will remain empty.

number(\${question_one})

int(question, expression, or value)

Converts to integer. Conversion varies depending on data type.

##### Note:

If this function is empty, it will return NaN and the question will remain empty.

int(\${question_one})

string(question, expression, or value)

Converts to string. Conversion varies depending on data type.

string(\${question_one})

date(question, expression, or value)

Converts a number or string to a date object, without preserving time.

date('2017-05-28T04:39:02+10:00')

date-time(question, expression, or string)

Converts a number or string to a date object.

date-time('2017-05-28T04:39:02+10:00')

decimal-date-time(question, expression, or string)

Converts a date object into a decimal date-time number.

decimal-date-time(\${date_question})

decimal-time(question, expression, or string)

Converts a time object into a number representing a fractional day in the device's time zone.

decimal-time(\${time_question})

format-date()

Fits an existing date or time value to a defined format.

format-date(\${previous_time}, '%H:%M')

coalesce(value1, value2)

Returns the first nonempty value. Supports only two values.

coalesce(\${question_one}, \${question_two})

concat(value1, value2, …)

Returns the concatenation of the string values.

concat(\${question_one}, ' and ', \${question_two})

max(value1, value2, ...)

Returns the maximum value in a given range, or to a single question across repeats.

max(\${question_one}, \${question_two})

min(value1, value2, ...)

Returns the minimum value in a given range, or to a single question across repeats.

min(\${question_one}, \${question_two})

sum(repeat)

Returns the sum of all responses to a given question across repeats.

sum(\${question})

count(repeat)

Returns the amount of responses to a given question across repeats.

count(\${question})

pulldata()

Returns information saved in an external CSV file, or in the properties of an answer. For more information, see Work with external content.

pulldata('info', 'email', 'name', \${previous_question})

version()

Returns the version of the survey defined in the settings worksheet.

version()

boolean-from-string()

Returns true if the string provided is 'true' or '1'. Otherwise, returns false.

boolean-from-string(\${question_one})

contains(string, substring)

Returns true if the given string contains the substring.

contains(\${question_one}, 'red')

starts-with(string, substring)

Returns true if the given string starts with the substring.

starts-with(\${question_one}, 'The')

ends-with(string, substring)

Returns true if the given string ends with the substring.

ends-with(\${question_one}, 'hand.')

selected-at(question, number)

Used for select_multiple questions. Returns the name of the choice selected for the given number, counted starting from zero; for example, '2' will return the third selected choice.

selected-at(\${question_one}, 2)

jr:choice-name(choice_name, 'question')

Used for select_one questions. Returns the label associated with the name of the choice in the given question. Be aware that the question must be defined inside quotes.

jr:choice-name(\${select_one}, '\${select_one}')

Used for select_multiple questions. Returns the label associated with the name of the choice in the given question. The selected-at() function must be used to extract the label for individual answers using the choice index. Be aware that the question must be defined inside quotes.

jr:choice-name(selected-at(\${select_multiple}, choice_index), '\${select_multiple}')

join(separator, question)

Concatenates all answers to a given question in a repeat, separated by the given separator.

join(',', \${question_in_repeat})

regex()

Applies a regular expression to the question's input. For more information, see Regular expressions.

regex(., '^\d{5}\$')

position(..)

Returns the index of the current record in a repeat. For more information, see Repeats.

position(..)

indexed-repeat(question, repeat, index number)

Returns the value from a specific question in a repeat record. For more information, see Repeats.

indexed-repeat(\${room_no}, \${floor}, 3)

## Constraints

Adding a constraint to a survey question restricts the accepted inputs for a response. This can include a specific range of numbers, combinations of letters and numbers, or general pattern matching. In your spreadsheet, the constraint expression is entered into the constraint field and informational text is entered into the constraint_message column of the survey worksheet. In the constraint expression, the input for the question is always represented by a period.

For example, you can use the following formula to restrict the input of an integer field to positive numbers only:

.>= 0

This formula, when applied to a date field, prevents the user from entering a value earlier than today:

.>= today()

You can also use calculations in constraints. This formula performs a calculation to only allow the user to select dates between today and 14 days from today:

(.>= today()) and (.<=(today() + (1000 * 60 * 60 * 24 * 14)))

##### Tip:

To avoid unexpected errors, if your formula includes decimal values between -1 and 1, include a leading zero in your values. Without the leading zero, the decimal can be mistaken for the operation character . (or -.). For example, the following expression will fail:

.> .25 and .< 24.25

The following expression will execute as expected:

.> 0.25 and .< 24.25

### Regular expressions

You can use regular expressions in a variety of ways for pattern matching to constrain valid responses to a given format or to ensure it contains specific content. This example requires that the response to the question include the word road somewhere in it.

The period used at the start of these examples applies the expression to the current field. Adding the name of another field in its place instead applies the regular expression to that field, which is ideal for relevant expressions. The comma acts as a separator between this field definition and the expression.

This regular expression ensures that the response exactly matches the word road, with nothing before or after it.

Regular expressions are ideal for confining the input of a question to a standard format. This example accepts only the input of a United States five-digit ZIP Code.

regex(., '^\d{5}\$')

This example matches the response to the current format of Indonesian number plates as in the following photo:

regex(., '^[A-Z]{1,2}\d{4}[A-Z]{2,3}\$')

More loosely standardized formats can require complex regular expressions. This regular expression used by the Survey123 web app constrains the input of a string field to match the format of an email address:

regex(., '^(([^<>()\[\]\\.,;:\s@"]+(\.[^<>()\[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))\$')

This modification of the above expression constrains input to match the format of an email address, while accepting non-English characters:

regex(., '^(([^<>()\[\]\\.,;:\s@"]+(\.[^<>()\[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}])|(([a-zA-Z\u0400-\uffff\-0-9]+\.)+[a-zA-Z\u0400-\uffff]{2,}))\$')

This example, also using a regular expression from the Survey123 web app, limits a field to only accept responses that match the format of a web address:

regex(., '^((https?|ftps?)\://|)(((?:(?:[\da-zA-Z](?:[-\da-zA-Z]{0,61}[\da-zA-Z])?)\.)+(?:[a-zA-Z](?:[-\da-zA-Z]{0,61}[\da-zA-Z])?)\.?)|localhost)(\:\d+)?(/(?:[^?#\s/]+/)*(?:[^?#\s/]+(?:\?[^?#\s/]*)?(?:#[A-Za-z][\w.:-]*)?)?)?')

For more information about regular expressions, refer to the Mozilla Developer Network documentation. For a list of characters and their functions in regular expressions, see the Reference worksheet in the Survey123 templates or refer to the Quick reference.

## Calculations

Calculations are performed in the calculation column for a question. Calculations are often associated with the calculate type of question, but they can also be applied to integer, decimal, text, and select_one questions. The outcome of the calculation can be used to populate relevant or constraint expressions by referring to the field name of the calculate question.

The calculate question type is hidden and doesn't display on a form. This means it can also be used to hold values that don't need to be displayed on the form but are included in the feature layer.

For example, you can create a question of type calculate, name it calc, and insert the following expression in its calculation column:

\${question_1} + \${question_2} + \${question_3}

Use the result to set the relevance for the next question:

\${calc} <= 100

Calculations can be used with the responses in date fields. This calculation estimates the number of years between a date entered and today, ideal for calculating somebody's age:

int((today() - \${birth_date}) div (1000 * 24 * 60 * 60 * 365.25))

Sometimes you only need part of a value, or a truncated version of a complete answer. The substr operator returns only part of a string, defined by the numbers after it. The first character determines the starting point of the selection, while the second value determines the length (if no second value is present, it continues until the end of the string). In this example, the substr operator removes all but the string from characters 10 to 15:

substr(\${previous_question}, 10, 15)

When the first number is negative, substr starts counting from the end of the string instead of the beginning. This example returns only the last five characters of the answer:

substr(\${previous_question}, -5)

You can also use the calculation column for aggregate functions in repeats. For more information, see Repeats.

### Complex mathematical functions

The calculation column can also handle more complex mathematical operations. This example determines the area of a plot given its radius, using the pi and square root functions:

Other mathematical functions you can use in Survey123 are listed in the following table:

FunctionDescriptionExample

pi()

Returns pi.

pi()

acos(value)

Returns the arccosine of the value.

acos(\${question_one})

asin(value)

Returns the arcsine of the value.

asin(\${question_one})

atan(value)

Returns the arctangent of the value.

atan(\${question_one})

cos(value)

Returns the cosine of the value.

cos(\${question_one})

sin(value)

Returns the sine of the value.

sin(\${question_one})

tan(value)

Returns the tangent of the value as degrees of an angle.

tan(\${question_one})

exp(value)

Returns the natural exponent of the value.

exp(\${question_one})

exp10(value)

Returns 10 to the power of the value.

exp10(\${question_one})

log(value)

Returns the natural logarithm of the value.

log(\${question_one})

log10(value)

Returns the base-ten logarithm of the value.

log10(\${question_one})

sqrt(value)

Returns the square root of the value.

sqrt(\${question_one})

atan2(value1, value2)

Returns the arctangent of the quotient of the values.

atan2(\${question_one}, \${question_two})

round(value, power)

Returns the rounded value.

round(\${question_one}, 5)

pow(value, power)

Returns the value to the power specified.

pow(\${question_one}, 3)

Adhere to the following best practices when using calculations:

• When using random(), consider adding a constant to avoid getting zero (0) as a result, for example, random()+0.5. A value of 0 may result in a blank answer.
• As with constraints, ensure that all decimal values between -1 and 1 in your formula have a leading 0, as leading with a decimal point causes errors.
• The data type of a calculation result is dependent on the data type of each element of the calculation. If a calculation is performed on two integers, the calculation result is an integer. If a calculation includes a string data type, you may find that a + operator concatenates values instead of adding them. To avoid unexpected results, use the number() function to ensure string values in a calculation are treated as numbers. For example, the calculation to add question1 (of type integer) to question2 (of type text) is \${question1} + number(\${question2}).
##### Tip:

The XLSForm default binding type for a calculate question is string. To overwrite this default, enter the required type (for example, int or decimal) in the bind::type column for your question. Alternatively, you can use the desired question type (for example, integer or decimal) and set the appearance for this question to hidden.

##### Caution:

Using mathematical functions or operators with text questions returns a result of NaN in the Survey123 web app. To concatenate text questions, use the concat() function instead of the + operator.

### Date formatting

You can use the format-date function in the calculation field to format date and time values. This can be useful when you want to display portions of dates to users or persist them as strings.

In this example, the value contained in a previous time or dateTime question is returned in 24-hour time:

format-date(\${previous_time},'%H:%M')

The qualifiers you can use in the format-date function are as follows:

QualifierDescription

%Y

4-digit year

%y

2-digit year

%m

%n

Numeric month

%b

Month name abbreviated

%d

%e

Day of month

%H

%h

Hour (24-hour time)

%M

%S

%3

%a

Three letter short text day

%W

Week number

##### Note:

The %W qualifier can't be used in a format-date function in which the date question uses a calculation. This qualifier works with a date question that has a default.

## Empty values

When using constraints and calculations that refer to other questions, consider what occurs when that question is empty (that is, it has no response). Empty values are represented as follows:

• NaN (not a number) for integer and decimal questions. This is a special value that represents the absence of a valid value.
• '' (an empty string) for text questions. The default data type for select_one, select_multiple, rank, and hidden questions is also text. When select_one, select_multiple, and hidden question types are empty, or when a rank question hasn't been altered by the user, they contain an empty string.

Depending on whether the value is a number or text, the behaviors in calculations differ.

In integer or decimal questions, the following behaviors occur:

• Any mathematical expression with a value of NaN fails to complete, and the question remains empty.
• The min() and max() functions complete and ignore any NaN values.
• The NaN value compared to any other value is only true in a calculation of an is not equal value comparison. All other expressions result in false.

In text questions, the following behaviors occur:

• The concatenation of text questions completes if empty values are present. For example, "Hello" + \${firstName} + ", how are you?" results in "Hello , how are you?" when the question firstName is empty.
• The min() and max() functions complete and ignore any empty strings.
• An empty text answer is equal to another empty text answer and is always less than any nonempty text.

You can determine if a question is empty using the string-length function. The string-length function can be used with all question types. For example, string-length(\${Question1}) returns 0 if Question1 is empty.

You can also determine if a select_one or select_multiple question is empty using the count-selected function. For example, count-selected(\${question2}) returns 0 if no selection has been made for question2.

## Select_multiple and rank questions

The responses to select_multiple and rank question types are stored differently than all other question types. Each checked answer in a select_multiple question is entered in the order it was selected, separated by commas. For example, selecting answers 'A' and 'B' in that order presents the response as 'A,B'. A rank question also stores its answers in a comma-separated list in order from highest to lowest rank at the time of submission.

Some XLSForm features do not work with select_multiple and rank questions. For example, if you enter the answer 'A' in the relevant column of a question referring to your select_multiple question, and the survey response is 'A,B', the response is not considered relevant. In this case, the solution is to use the selected() function, which checks whether any one of the values appears in the list.

The following expression, when used in the relevant column of a question, displays the question if the user has selected 'A' as one of the responses in the referenced select_multiple question. Additional responses in the select_multiple question do not change this behavior.

selected(\${previous_question}, 'A')

## Work with external content

Using the pulldata() function in the calculation column of a question, you can preload data from a CSV file. There are two ways to include a CSV file: manually place the file in the survey's media folder or link to a CSV file hosted in ArcGIS.

The pulldata() function requires the following four parameters to be specified in order:

1. The name of the CSV file that contains the list of values. The name does not include the .csv file name suffix.
2. The name of the column in the CSV file that contains the value you want to return.
3. The name of the key field in the CSV file that you will use to look up the value.
4. The key value to look up in the key field.

These values can be defined directly or through variables defined elsewhere in the survey. In the following example, the calculation returns the email address of somebody named in a previous question from a CSV file called info:

pulldata('info','email','name', \${previous_question})

The same pulldata() function also works in the constraints column, preventing the user from submitting answers that are not in the CSV file. In the constraints column, this same formula prevents the form from accepting any values that are not in the CSV file's name column.

There are some limitations to the pulldata() function. The name of the key field has the same limitation as the name column in the choices worksheet, meaning that these values can't have spaces or non-ASCII characters. Additionally, because these are CSV files, using a comma in any of these fields causes the pulldata() function to produce incorrect results.

##### Note:

If your .csv file lacks a column header, or has a trailing comma at the end of the file's rows, the file fails to import into the survey.

If you manually place the .csv file in the survey's media folder, and at a later time upload a .csv file of the same name to ArcGIS and link it to the survey, the .csv file in the survey is updated from the linked content (if newer).

To link a .csv file to a survey, do the following:

• Ensure the .csv file is hosted in ArcGIS Online or ArcGIS Enterprise and has the same sharing permissions as the survey.
• Ensure the survey is published to ArcGIS before attempting to link content.
• In Survey123 Connect, browse to the Linked Content tab of the survey and click the Link content button. Choose CSV and browse to and select the .csv file to link.

To use the CSV in Survey123 Connect, on the Linked Content tab, you can choose to download the file with the download button . This places a copy of the CSV file in the survey's media folder. Linked files are automatically downloaded with the survey in the Survey123 field app.

##### Note:

The pulldata() function can't be used to populate the values of select_multiple questions.

If values in your CSV file exceed 255 characters, you must enter a higher value in the bind::esri:fieldLength column for both the question you're populating with the CSV content and any questions being used as input to the pulldata() function. If your CSV file includes values that are larger than the maximum length of one of these fields, a survey response fails to submit and displays a Code 1000 error.

When using the pulldata() function, your CSV column names cannot contain spaces, hyphens, or other special characters.

### Other uses of pulldata

You can use the pulldata() function to extract properties provided by the device using the "@property" parameter and the name of the property you intend to extract. The following example returns the UTC offset of the device, allowing users to record the time zone in which the survey was taken:

pulldata("@property", 'utcoffset')

For information about which properties can be extracted, see Device and user properties.

You can also use the pulldata() function to extract and deconstruct geopoint question values. For more information, see Geopoints.

EXIF and JSON data can be extracted from image questions using the pulldata() function. For more information, see Media.

The pulldata("@property") function can be used to detect the way the user is currently interacting with their survey response using the following:

pulldata("@property", 'mode')

This has three possible values, returning new if the user is producing a new survey response (including one copied from an existing survey), returning edit if the user is editing an existing response, and returning view if the user is viewing an existing response in read-only mode. The following example, when placed in the readonly column, detects whether the user is editing an existing response and prevents them from editing the chosen question:

pulldata("@property", 'mode') = 'edit'

The pulldata("@property") function can also be used to check where a survey was opened. The following example checks if the survey was opened from the inbox:

pulldata("@property", 'status') = 'inbox'

##### Caution:

It is recommended that you do not store the results of the mode or status properties in a hidden or calculate field and do not enable or disable questions using these properties in the relevant column. Doing so may cause deletion of the contents of these fields or values to not update as expected.