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.

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

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

Expressions are made up of various parts, these parts include

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

(TO_NUMBER(Field_Identifier1) * 5) / 2 

CONCATENATE(Text_Question, " extra string") 

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

Important Notes:

​

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

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 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: Integer (whole number)
​Example: FLOOR(NumberQuestion) 

The GET function works together with the Driver License question to retrieve individual portions of the data encoded in a driver license barcode. It supports "first_name", "last_name", "license_number", "birth_date", "issuing_date", and "expiry_date" as values to retrieve.

Usage: GET(InputQuestion, field_name)
​Returns: The item from the barcode referenced by field_name as a string
​Example: GET(DL_Question, "license_number")

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

*NOTE: When basing an IF statement on a Yes/No field use true/false in your 'Check'

Example: IF(Yes_No_Question = true,"Yes was selected","No was selected")

The INDEX function works together with the NFC question. This function will retrieve individual portions of the data encoded on an NFC tag by index number.

Note: The number specified is zero-based so the first item on the tag will be item 0.

Usage: INDEX(InputQuestion, IndexNumber)
​Returns: The item from the list at index location as a string
​Example: INDEX(NFC_Question, 0)

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 returns the number of characters for the text you input.
​
​Usage: LENGTH(TextInput)
​Returns: Integer (whole number)
​Example: LENGTH(TextQuestion) 

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 returns the input text but with all the capitals changed to their lower case versions.

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

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 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 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 is used to check if the a field has an answer.

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

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

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

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

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) 

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

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) 
​
​Note: The field you would like to timestamp, must have the 'Timestamp Answer' setting checked in order to work.
​

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

COUNT returns the total number of entries for a repeat group.
​

Usage: COUNT(your_repeat_group)
​Returns: Integer
​

INDEX_OF returns the current location within the repeat group. This can be used to generate an identifier for the item in the repeat group and could be used together with CONCATENATE to add text as well.

Usage: INDEX_OF(field_in_repeat_group)
​Returns: Integer (zero-based)
​Note: The best practice for using this field is to add it inside the repeat group that you want to calculate an index for and reference a field within that repeat group (or the calculated question itself if you do not have another field.)

Example: You can use INDEX_OF to auto-number your repeat groups! Using INDEX_OF will update the item's position in the item list as you remove your repeat group items.

INDEX_OF(field_in_repeat_group) + 1

In the example below, items are added and named "Item #" in order. The INDEX_OF function adds a number to each item (shown as Item # | Repeat Group Postion #) based on the position in the item list. As items are removed, the Name of the field stays the same, but the Position updates so the items stay in order.

Instead of using a Calculated Question, use a Text/Decimal Question with an Initial Answer expression.

When the field becomes active the expression will be calculated and populated, and the user will be able to edit the value.
​
Please see this article for more info on how to set up Initial Answer Expressions: Setting Initial Answer Expressions for Questions

Here are some commonly used functions.

Difference Between Two Times In Words

With days:

Round up difference in Time to nearest 15 minutes in Words: 

​Age (Calculating Age from a Date of Birth):

Tally Number of Answered Questions:

Round up to nearest .5 Decimal:

Square Root:
​

Use POWER(your_question, 0.5) to determine the square root.

Pull Answers from inside a Repeat Group:

Use a Calculated Question outside of the Repeat Group to populate a value from a particular Repeat Group item.

Item 1: Group[0]/Question_in_Repeat_Group
Item 2: Group[1]/Question_in_Repeat_Group

Item 3: Group[2]/Question_in_Repeat_Group

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