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.

Examples:

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

Ex: TO_NUMBER(SelectQuestion) + TO_NUMBER(CalculatedQuestion) 


Available Functions & Function Descriptions

List of Functions

General

AND

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

Usage: AND(param1, param2, param3, ...)
Returns:
Boolean (true or false)
Example:
AND(Age > 5, Gender = "male")


AVERAGE

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

Usage: AVERAGE(number1, number2, number3, ...)
Returns:
Float (number with decimal places)
Example:
AVERAGE(NumberQuestion1, NumberQuestion2, 5, 15.5) 


CEIL

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

Usage: CEIL(NumberInput)
Returns:
Integer (whole number)
Example:
CEIL(NumberQuestion1) 


CONCATENATE

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

Usage: CONCATENATE(TextQuestion1, TextQuestion2, TextQuestion3, ...)
Returns:
String (some text)
Example:
CONCATENATE(TextQuestion1, " and then ", TextQuestion2) 

Note: Create a new line by adding \n  to the end of the string
Example:
CONCATENATE("Line 1\n","Line 2") 


CONTAINS

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

Usage: CONTAINS(String, Sub String)
Returns:
Boolean (true / false)
Example:
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.

Example: CONTAINS(TextQuestion1, "my option") 


COUNT

COUNT returns the total number of entries for a repeat group.
Usage:
COUNT(your_repeat_group)
Returns:
Integer


DEVICE_IDENTIFIER

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.

Usage: DEVICE_IDENTIFIER()
Returns:
Current Device Identifier as text
Example: DEVICE_IDENTIFIER() 


FLOOR

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

Usage: FLOOR(NumberInput)
Returns:
Current Device Identifier as text
Example: FLOOR(NumberQuestion) 


IF

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.

Usage: IF(Check, True Branch, False Branch)
Returns:
Whatever was in the branch selected based on the Check
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

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

Usage: ISBLANK(InputQuestion)
Returns:
Boolean (true / false)
Example: ISBLANK(Question_1) 


LENGTH

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

Usage:
LENGTH(TextInput)
Returns:
Integer (whole number)
Example: LENGTH(TextQuestion) 


LOOKUP

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.

Usage: LOOKUP(Resource, Table, Key Column, Value Column, Key Value)
Returns:
Text value fetched from the resource


LOWERCASE

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

Usage: LOWERCASE(TextInput)
Returns:
Text
Example: LOWERCASE(TextQuestion) 


MAX

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

Usage: MAX(NumberInput1, NumberInput2, NumberInput3, ...)
Returns:
Number (float or integer based on the inputs)
Example: MAX(Score1, Score2, Score3) 


MIN

MIN 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.

Usage: MIN(NumberInput1, NumberInput2, NumberInput3, ...)
Returns:
Number (float or integer based on the inputs)
Example: MIN(Score1, Score2, Score3) 


MOD

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.

Usage: MOD(Dividend, Divisor)
Returns:
Integer (whole number)
Example: MOD(MyNumberInput, 4) 


NOTBLANK

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

Usage: NOTBLANK(InputQuestion)
Returns:
Boolean (true / false)
Example: NOTBLANK(Question_1) 


OR

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

Usage: OR(param1, param2, param3, ...)
Returns:
Boolean (true or false)
Example: OR(Age < 5, Gender = "female") 


POWER

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.

Usage: POWER(Base, Exponent)
Returns:
Number (integer or float based on input)
Example: POWER(MyNumberInput, 3) 


ROUND

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.

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

Usage: ROUND(NumberInput, DecimalPlaces)
Returns:
Number (integer or float based on input)
Example: ROUND(MyNumberInput, 2)


SUBSTRING

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. So SUBSTRING("this cool text", 6, 4)  would return "cool"

Usage: SUBSTRING(TextInput, Position, Length)
Returns:
Text
Example: SUBSTRING(TextInput, 5, 4) 


SUM

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.

Usage: SUM(NumberInput1, NumberInput2, NumberInput3, ...)
Returns:
Number (integer or float based on input)
Example: 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.

Example: SUM(group.NumberQuestion) 


TO_NUMBER

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

Usage: TO_NUMBER(TextQuestion)
Returns:
Number (integer or float based on input)
Example: TO_NUMBER(MyCalulcatedQuestion) 


UPPERCASE

UPPERCASE returns the input text but with all the lowercase characters converted to their uppercase equivalents. UPPERCASE("i am bob")  returns "I AM BOB"

Usage: UPPERCASE(TextQuestion)
Returns:
Text
Example: UPPERCASE(MyTextQuestion) 


Date Components

YEAR

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, 2019 at the time of writing this article.

Usage: YEAR(DateField)
Returns:
Integer
Example: YEAR(MyDateQuestion) 


MONTH

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.

Usage: MONTH(DateField)
Returns:
Integer
Example: MONTH(MyDateQuestion) 


DAY

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 current day of the month as a basic integer, 1 through 31 .

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

Usage: DAY(DateField)
Returns:
Integer
Example: DAY(MyDateQuestion) 


HOUR

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 through 24.

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

Usage: HOUR(TimeField)
Returns:
Integer
Example: HOUR(MyTimeQuestion) 


MINUTE

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 through 60.

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

Usage: MINUTE(TimeField)
Returns:
Integer
Example: MINUTE(MyTimeQuestion) 


SECOND

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 through 60.

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

Usage: SECOND(TimeField)
Returns:
Integer
Example: SECOND(MyTimeQuestion) 


Date Creation

DATE

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(2019,6,9)  would create a Date Object for 2019/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())) 

Usage: DATE(year, month, day)
Returns:
Date Object
Example: DATE(2019,1,1) 


TIME

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())) 

Usage: TIME(hour, minute, second)
Returns:
Time Object
Example: TIME(20,30,15) 


DATETIME

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(2019, 6, 9, 14,30,0)  would create a DateTime Object for 9 June 2019 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())) 

Usage: DATETIME(year, month, day, hour, minute, second)
Returns:
DateTime Object
Example: DATETIME(2016,10,10,20,30,15) 


Add/Subtract from Date

ADDYEARS

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 an integer number (directly or from a field).

Usage: ADDYEARS(DateObject, amount)
Returns:
Date / DateTime Object
Examples: ADDYEARS(NOW(), 5)
ADDYEARS(DateTimeQuestion, IntegerQuestion)
 


ADDMONTHS

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 an integer number (directly or from a field).

Usage:
ADDMONTHS(DateObject, amount)
Returns:
Date / DateTime Object
Examples: ADDMONTHS(NOW(), 5)
ADDMONTHS(DateTimeQuestion, IntegerQuestion) 


ADDDAYS

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 an integer number (directly or from a field).

Usage: ADDDAYS(DateObject, amount)
Returns:
Date / DateTime Object
Examples: ADDDAYS(NOW(), 5)
ADDDAYS(DateTimeQuestion, IntegerQuestion) 


ADDHOURS

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 an integer number (directly or from a field).

Usage: ADDHOURS(TimeObject, amount)
Returns:
Time / DateTime Object
Examples: ADDHOURS(NOW(), 5)
ADDHOURS(DateTimeQuestion, IntegerQuestion) 


ADDMINUTES

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 an integer number (directly or from a field).

Usage: ADDMINUTES(TimeObject, amount)
Returns:
Time / DateTime Object
Examples: ADDMINUTES(NOW(), 5)
ADDMINUTES(DateTimeQuestion, IntegerQuestion) 


ADDSECONDS

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 an integer number (directly or from a field).

Usage: ADDSECONDS(TimeObject, amount)
Returns:
Time / DateTime Object
Examples: ADDSECONDS(NOW(), 5)
ADDSECONDS(DateTimeQuestion, IntegerQuestion) 


General Date

NOW

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.

Usage: NOW()
Returns:
DateTime Object
Example: NOW()


Answer Attributes

TIMESTAMP

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

Usage: TIMESTAMP(QuestionIdentifier)
Returns:
DateTime Object
Example: TIMESTAMP(MyQuestion) 


GEOSTAMP

GEOSTAMP() returns the Location of 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.

Usage: GEOSTAMP(QuestionIdentifier)
Returns:
Location Object
Example: GEOSTAMP(MyQuestion) 


LATITUDE

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

Usage: LATITUDE(LocationQuestion)
Returns:
Decimal
Examples: LATITUDE(MyQuestion)
LATITUDE(GEOSTAMP(TextQuestion)) 


LONGITUDE

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

Usage: LONGITUDE(LocationQuestion)
Returns:
Decimal
Examples: LONGITUDE(MyQuestion)
LONGITUDE(GEOSTAMP(TextQuestion)) 

Useful Examples

Here are some commonly used functions.

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

AGE
(Calculating Age from a Date of Birth):

YEAR(NOW()) - YEAR(Date_of_Birth_) - IF(NOW() < DATE(YEAR(NOW()),MONTH(Date_of_Birth_),DAY(Date_of_Birth_)),1,0)

Tally Number of Answered Questions:

SUM(IF(NOTBLANK(Question_1),1,0),IF(NOTBLANK(Question_2),1,0),IF(NOTBLANK(Question_3),1,0),IF(NOTBLANK(Question_4),1,0))

Other Useful Articles:

If you have any questions or comments please send us a message at support@devicemagic.com.

Did this answer your question?