Overview

In this article we will go over writing expressions for calculated questions as well as providing information and examples of the available functions.

Here is an overview of the expression builder:

You build expressions by entering it into the main text area in the middle. The expressions are very similar to those you would write in Microsoft Excel.

To make writing expressions easier you can use the "Add Field" and "Add Function" menus on the side.

They will insert any Field or Function you select at the current cursor position in the main text area.

Add Field

You simply click on a field name on the right to add the field identifier to the main area.

Add Function

Add function works in a similar way except that you may need to fill in some values on the right first - You can click "choose field" to bring up the "Add field" box that to help set values.

Once you have configured your selected function you click "Add this Function" to insert it into the main text area.

Writing Expressions

Expressions are made up of various parts, these parts include

- Field identifiers: The identifiers of the fields you want to use in the expression. When an identifier is used, the device will evaluate the current value of that field and use it in the expression. The value of the calculated question will update whenever a field used in the expression is updated.

- Functions: The functions that you can use to perform various checks or calculations in the expression. We will give descriptions of the available functions later in this article.

- Constants: Constant values - either string or numbers in your expression. e.g. "A test string", 2, 5.34

- Mathematical operators: + (plus), - (minus), * (multiply), / (divide), () (brackets)

Putting these together you can map calculations or checks you want to perform into calculated questions.

e.g.

(TO_NUMBER(Field_Identifier1) * 5) / 2

CONCATENATE(Text_Question, " extra string")

IF(Field_Identifier > 5, "Looks correct", "Needs investigation")

Important Note: Mathematical operators and functions will break when used on "Text" answers.

Calculated and Select Questions are treated as Text answers - so to use them in mathematical functions you need to use the TO_NUMBER().

e.g. TO_NUMBER(SelectQuestion) + TO_NUMBER(CalculatedQuestion)

Available Functions

- List of functions -

- General -
AND
AVERAGE
CEIL
CONCATENATE
CONTAINS
DEVICE_IDENTIFIER
FLOOR
IF
ISBLANK
LENGTH
LOOKUP
LOWERCASE
MAX
MIN
MOD
NOTBLANK
OR
POWER
ROUND
SUBSTRING
SUM
TO_NUMBER
UPPERCASE

- Date Creation -
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
DATE
TIME
DATETIME

- Add/Subtract from Date -
ADDYEARS
ADDMONTHS
ADDDAYS
ADDHOURS
ADDMINUTES
ADDSECONDS

- General Date -
NOW

- Answer Attributes -
TIMESTAMP
GEOSTAMP
LATITUDE
LONGITUDE

- Function Descriptions -

- General -

AND

Usage: AND(param1, param2, param3, ...)

Returns: Boolean (true or false)

AND is used when you need to check more than one thing at a time and they all need to be true for the result to be true.

AND will return true if all the parameters passed in evaluate to true, or false if any of them are not true

e.g. AND(Age > 5, Gender = "male")

AVERAGE

Usage: AVERAGE(number1, number2, number3, ...)

Returns: Float (number with decimal places)

AVERAGE is used to get the average of a collection of numbers passed in.

e.g. AVERAGE(NumberQuestion1, NumberQuestion2, 5, 15.5)

CEIL

Usage: CEIL(NumberInput)

Returns: Integer (whole number)

CEIL rounds the passed in number up to the closest whole number. So CEIL(1.1) would return 2.

e.g. CEIL(NumberQuestion1)

CONCATENATE

Usage: CONCATENATE(TextQuestion1, TextQuestion2, TextQuestion3, ...)

Returns: String (some text)

CONCATENATE is used to combine text. You can pass in text questions as well as constant pieces of text.

e.g. CONCATENATE(TextQuestion1, " and then ", TextQuestion2)

CONTAINS

Usage: CONTAINS(String, Sub String)

Returns: Boolean (true / false)

CONTAINS is used to determine whether a piece of text (a string) contains another piece of text.

e.g. CONTAINS("the quick brown fox", "brown") would return true because "the quick brown fox" includes "brown"

CONTAINS("This is my text", "giraffe") would result in false.

You can use questions as inputs as well to check what their answers contain. This is especially useful for multiple select questions.

e.g. CONTAINS(TextQuestion1, "my option")

DEVICE_IDENTIFIER

Usage: DEVICE_IDENTIFIER()

Returns: Current Device Identifier as text

DEVICE_IDENTIFIER is used when you need to pass the identifier of the current device back as an answer. It can also be handy when passed to other functions such as LOOKUP when you want specific devices to get specific answers.

e.g. DEVICE_IDENTIFIER()

FLOOR

Usage: FLOOR(NumberInput)

Returns: Current Device Identifier as text

FLOOR is used to round a decimal point number down to the closest whole number. So FLOOR(1.9) would return 1.

e.g. FLOOR(NumberQuestion)

IF

Usage: IF(Check, True Branch, False Branch)

Returns: Whatever was in the branch selected based on the Check

The IF statement is used when you need to return different answers based on some check. It works in the same way that an Excel IF statement does.

You construct the IF statement with 3 parts. The first parameter is the check, this is some expression that evaluates to true or false (e.g. NumberInput > 5).

The device will evaluate the check and then continue on with evaluating either the second or third parameter passed in based on the result of the check.

If the check is true then it will evaluate and return the second parameter - the True Branch. If the check returns false it will evaluate and return the False Branch.

It doesn't matter what is in the True or False branch as long as they are valid expressions - so you can nest IF statements to get more than 2 outcomes.

Example:

IF(Age > 65, "Pensioner", IF(Age > 20, "Adult", "Youngun"))

In this example the check is "Age > 65" - if this evaluates to true (the age is bigger than 65) then it will have a look at the True branch - which is just "Pensioner" and that will be the result.

If the check evaluates to false (Age is less than 65) then it will go down the False Branch. In this case it would then evaluate the next IF as it is what we passed in.

It would check Age again, this time against 20 and then either return the True branch "Adult" or the false one "Youngun".

ISBLANK

Usage: ISBLANK(InputQuestion)

Returns: Boolean (true / false)

ISBLANK is used to check if the answer to a field is blank or not yet filled in.

e.g. ISBLANK(Question_1)

LENGTH

Usage: LENGTH(TextInput)

Returns: Integer (whole number)

LENGTH returns the number of characters for the text you input.

e.g. LENGTH(TextQuestion)

LOOKUP

Usage: LOOKUP(Resource, Table, Key Column, Value Column, Key Value)

Returns: Text value fetched from the resource

LOOKUP is used to fetch a value from one of your resources based on a "Key Value" from the form.

LOOKUP functions need to be setup from the "Add Function" panel - it will walk you through selecting the various parameters for the function.

You first select the Resource which should be an excel spreadsheet, then the Table (or worksheet) from that resource.

Then you identify the Key and Value columns - the key column is the one you will search in and the value column is the one you want LOOKUP to pull the answer from.

Finally you set the Key Value which is what you want to search for in the Key Column.

Add the function - it will look like LOOKUP() with a bunch of identifiers for the parameters.

LOWERCASE

Usage: LOWERCASE(TextInput)

Returns: Text

LOWERCASE returns the input text but with all the capitals changed to their lower case versions.

e.g. LOWERCASE(TextQuestion)

MAX

Usage: MAX(NumberInput1, NumberInput2, NumberInput3, ...)

Returns: Number (float or integer based on the inputs)

MAX returns the largest input. So you use it when you have a collection of numbers and want to find the biggest one.

e.g. MAX(Score1, Score2, Score3)

MIN

Usage: MIN(NumberInput1, NumberInput2, NumberInput3, ...)

Returns: Number (float or integer based on the inputs)

MAX returns the lowest value input. So you use it when you have a collection of numbers and want to find the one with the lowest value.

e.g. MIN(Score1, Score2, Score3)

MOD

Usage: MOD(Dividend, Divisor)

Returns: Integer (whole number)

MOD is used to find the remainder after you divide two numbers. So MOD(21,4) would return 1, because 21/4 is 5 but with 1 left over.

e.g. MOD(MyNumberInput, 4)

NOTBLANK

Usage: NOTBLANK(InputQuestion)

Returns: Boolean (true / false)

NOTBLANK is used to check if the a field has an answer.

e.g. NOTBLANK(Question_1)

OR

Usage: OR(param1, param2, param3, ...)

Returns: Boolean (true or false)

OR is used when you need to check more than one thing at a time and any of them need to be true for the result to be true.

OR will return true if any the parameters passed in evaluate to true, or false if all of them are not true

e.g. OR(Age < 5, Gender = "female")

POWER

Usage: POWER(Base, Exponent)

Returns: Number (integer or float based on input)

POWER is used to work out mathematical exponents. So POWER(2,2) would return 4. It takes the Base and puts it to the power of the exponent.

e.g. POWER(MyNumberInput, 3)

ROUND

Usage: ROUND(NumberInput, DecimalPlaces)

Returns: Number (integer or float based on input)

ROUND is used to round a number off to a certain number of decimal places. ROUND(3.4, 0) would return 3 as there are 0 decimal places specified.

ROUND(3.5324, 2) would return 3.53.

e.g. ROUND(MyNumberInput, 2)

Note: This will still drop trailing 0s - i.e. ROUND(5.2000, 2) will result in 5.2

SUBSTRING

Usage: SUBSTRING(TextInput, Position, Length)

Returns: Text

SUBSTRING is used to pull parts of a text answer out. You give the input, the position to start the result from and the length of the result and it will return that.

SUBSTRING("my cool text", 3, 4) would return "cool"

e.g. SUBSTRING(TextInput, 5, 4)

SUM

Usage: SUM(NumberInput1, NumberInput2, NumberInput3, ...)

Returns: Number (integer or float based on input)

SUM is used to add a set of numbers together. You can pass in a bunch of numbers and it will return the sum of the numbers.

SUM(1,2,3,4,5) will return 15.

e.g. SUM(Number1, NumberQuestion2, NumberQuestion3 * 4)

The other use of SUM is to add up numbers from a repeat group. To SUM over a repeat group you need the calculation question to be outside the group and then use the identifier of the question in the group you want to SUM.

e.g. SUM(group.NumberQuestion)

TO_NUMBER

Usage: TO_NUMBER(TextQuestion)

Returns: Number (integer or float based on input)

TO_NUMBER is used when you need to do some mathematical functions on Text answers that are actually numbers.

This is very useful for answers coming from other calculated questions (the device does not know if these are really numbers) and for select questions (all answers are treated as text).

e.g. TO_NUMBER(MyCalulcatedQuestion)

UPPERCASE

Usage: UPPERCASE(TextQuestion)

Returns: Text

UPPERCASE returns the input text but with all the lowercase characters converted to their uppercase equivalents.

UPPERCASE("i am bob") returns "I AM BOB"

e.g. UPPERCASE(MyTextQuestion)

- Date Componenets -

YEAR

Usage: YEAR(DateField)

Returns: Integer

YEAR is used to get the year component from a date. You can input any Date/DateTime field, a Calculated Question that returns a Date or a function that returns a date like NOW(), DATE() or DATETIME().

It returns the year as a basic integer - YEAR(NOW()) would return the current year, 2016 at the time of writing this article.

e.g. YEAR(MyDateQuestion)

MONTH

Usage: MONTH(DateField)

Returns: Integer

MONTH is used to get the month component from a date. You can input any Date/DateTime field, a Calculated Question that returns a Date or a function that returns a date like NOW(), DATE() or DATETIME().

It returns the month as a basic integer - 1 for January through to 12 for December.

MONTH(NOW()) would return the current month, 6 at the time of writing this article.

e.g. MONTH(MyDateQuestion)

DAY

Usage: DAY(DateField)

Returns: Integer

DAY is used to get the day component from a date. You can input any Date/DateTime field, a Calculated Question that returns a Date or a function that returns a date like NOW(), DATE() or DATETIME().

It returns the day as a basic integer - as the current day of the month 1->31 .

DAY(NOW()) would return the current day, 9 at the time of writing this article.

e.g. DAY(MyDateQuestion)

HOUR

Usage: HOUR(TimeField)

Returns: Integer

HOUR is used to get the hour component from a Time or DateTime. You can input any Time/DateTime field, a Calculated Question that returns a Time/DateTime or a function that returns a Time/DateTime like NOW() or DATETIME().

It returns the hour as a basic integer on the 24 hour clock 1 -> 24.

HOUR(NOW()) would return the current hour, 14 at the time of writing this article.

e.g. HOUR(MyTimeQuestion)

MINUTE

Usage: MINUTE(TimeField)

Returns: Integer

MINUTE is used to get the minute component from a Time or DateTime. You can input any Time/DateTime field, a Calculated Question that returns a Time/DateTime or a function that returns a Time/DateTime like NOW() or DATETIME().

It returns the minute as a basic integer in the hour 0 -> 60.

MINUTE(NOW()) would return the current minute, 37 at the time of writing this article.

e.g. MINUTE(MyTimeQuestion)

SECOND

Usage: SECOND(TimeField)

Returns: Integer

SECOND is used to get the second component from a Time or DateTime. You can input any Time/DateTime field, a Calculated Question that returns a Time/DateTime or a function that returns a Time/DateTime like NOW() or DATETIME().

It returns the second as a basic integer on in the minute 0 -> 60.

SECOND(NOW()) would return the current second, 25 at the time of writing this article.

e.g. SECOND(MyTimeQuestion)

- Date Creation -

DATE

Usage: DATE(year, month, day)

Returns: Date Object

DATE is used to create a Date Object from the different date numbers. To use the function you pass in the year, month and day as numbers in that order.

So DATE(2016,6,9) would create a Date Object for 2016/06/09.

Date Objects are useful for comparing to Date Questions and for use with the Date Functions like ADDYEARS, ADDMONTHS, etc.

You can use the above Date Component functions to get inputs from Date Questions/functions to use in this function. e.g. DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW()))

e.g. DATE(2016,1,1)

TIME

Usage: TIME(hour, minute, second)

Returns: Time Object

TIME is used to create a Time Object from the different time numbers. To use the function you pass in the hour, minute and second as numbers in that order.

So TIME(14,30,0) would create a Time Object for 2:30pm.

Time Objects are useful for comparing to Time Questions and for use with the Time Functions like ADDHOURS, ADDMINUTES, etc.

You can use the above Time Component functions to get inputs from Time Questions/functions to use in this function. e.g. TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

e.g. TIME(20,30,15)

DATETIME

Usage: DATETIME(year, month, day, hour, minute, second)

Returns: DateTime Object

DATETIME is used to create a DateTime Object from the different date+time numbers. To use the function you pass in the year, month, day, hour, minute and second as numbers in that order.

So DATETIME(2016, 6, 9, 14,30,0) would create a DateTime Object for 9 June 2016 at 2:30pm.

DateTime Objects are useful for comparing to DateTime Questions and for use with the Date and Time Functions like ADDYEARS, ADDMONTHS, ADDHOURS, ADDMINUTES, etc.

You can use the above DateTime Component functions to get inputs from DateTime Questions/functions to use in this function. e.g. DATETIME(YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))

e.g. DATETIME(2016,10,10,20,30,15)

- Add/Subtract from Date -

ADDYEARS

Usage: ADDYEARS(DateObject, amount)

Returns: Date / DateTime Object

ADDYEARS is used to add a number of years to an existing Date / DateTime object. To use it you need to pass in a Date/DateTime Question or function and a integer number (directly or from a field).

e.g. ADDYEARS(NOW(), 5)

ADDYEARS(DateTimeQuestion, IntegerQuestion)

ADDMONTHS

Usage: ADDMONTHS(DateObject, amount)

Returns: Date / DateTime Object

ADDMONTHS is used to add a number of months to an existing Date / DateTime object. To use it you need to pass in a Date/DateTime Question or function and a integer number (directly or from a field).

e.g. ADDMONTHS(NOW(), 5)

ADDMONTHS(DateTimeQuestion, IntegerQuestion)

ADDDAYS

Usage: ADDDAYS(DateObject, amount)

Returns: Date / DateTime Object

ADDDAYS is used to add a number of days to an existing Date / DateTime object. To use it you need to pass in a Date/DateTime Question or function and a integer number (directly or from a field).

e.g. ADDDAYS(NOW(), 5)

ADDDAYS(DateTimeQuestion, IntegerQuestion)

ADDHOURS

Usage: ADDHOURS(TimeObject, amount)

Returns: Time / DateTime Object

ADDHOURS is used to add a number of hours to an existing Time / DateTime object. To use it you need to pass in a Time/DateTime Question or function and a integer number (directly or from a field).

e.g. ADDHOURS(NOW(), 5)

ADDHOURS(DateTimeQuestion, IntegerQuestion)

ADDMINUTES

Usage: ADDMINUTES(TimeObject, amount)

Returns: Time / DateTime Object

ADDMINUTES is used to add a number of minutes to an existing Time / DateTime object. To use it you need to pass in a Time/DateTime Question or function and a integer number (directly or from a field).

e.g. ADDMINUTES(NOW(), 5)

ADDMINUTES(DateTimeQuestion, IntegerQuestion)

ADDSECONDS

Usage: ADDSECONDS(TimeObject, amount)

Returns: Time / DateTime Object

ADDHOURS is used to add a number of seconds to an existing Time / DateTime object. To use it you need to pass in a Time/DateTime Question or function and a integer number (directly or from a field).

e.g. ADDSECONDS(NOW(), 5)

ADDSECONDS(DateTimeQuestion, IntegerQuestion)

- General Date -

NOW

Usage: NOW()

Returns: DateTime Object

NOW() returns the current date and time in a DateTime Object. You can use the result with the other DateTime or Component functions.

YEAR(NOW()) would return the current year.

e.g. NOW()

- Answer Attributes -

TIMESTAMP

Usage: TIMESTAMP(QuestionIdentifier)

Returns: DateTime Object

TIMESTAMP() returns the timestamp off a question that has the timestamp enabled as a DateTime Object.

e.g. TIMESTAMP(MyQuestion)

GEOSTAMP

Usage: GEOSTAMP(QuestionIdentifier)

Returns: Location Object

GEOSTAMP() returns the Location off a question that has the geostamp enabled as a Location Object.

If a calculated question returns a Location Object it looks like a Location Question. You can use the LATITUDE and LONGITUDE methods on a Location Object returned by GEOSTAMP.

e.g. GEOSTAMP(MyQuestion)

LATITUDE

Usage: LATITUDE(LocationQuestion)

Returns: Decimal

LATITUDE returns the latitude value from a Location Question or GEOSTAMP function. The latitude is returned as a decimal number e.g. -33.124545435

e.g. LATITUDE(MyQuestion)

LATITUDE(GEOSTAMP(TextQuestion))

LONGITUDE

Usage: LONGITUDE(LocationQuestion)

Returns: Decimal

LONGITUDE returns the longitude value from a Location Question or GEOSTAMP function. The longitude is returned as a decimal number e.g. 16.2312414

e.g. LONGITUDE(MyQuestion)

LONGITUDE(GEOSTAMP(TextQuestion))

Useful Examples

Here are some commonly used functions that can be a bit tricky.

Difference Between Two Times In Words

IF(FLOOR((End_Time_Question - Start_Time_Question) / 60 / 60) > 0,
CONCATENATE(FLOOR((End_Time_Question - Start_Time_Question) / 60 / 60), " hours ", MOD(FLOOR((End_Time_Question - Start_Time_Question) / 60), 60), " minutes"),
CONCATENATE(FLOOR((End_Time_Question - Start_Time_Question) / 60), " minutes"))

With days:

CONCATENATE(
 IF(FLOOR((End_Time_Question - Start_Time_Question) / 60 / 60 / 24) > 0,
CONCATENATE(FLOOR((End_Time_Question - Start_Time_Question) / 60 / 60 / 24), " days "),
""),
 IF(FLOOR((End_Time_Question - Start_Time_Question) / 60 / 60) > 0,
CONCATENATE(MOD(FLOOR((End_Time_Question - Start_Time_Question) / 60 / 60),24), " hours "),
""),
 MOD(FLOOR((End_Time_Question - Start_Time_Question) / 60), 60), " minutes"
)

Did this answer your question?