Previous Next

In the form designer, you can build constraint, relevance, and calculation expressions using simple wizards that are built right into the designer. You can also edit expressions by hand, in which case the reference materials here will come in handy.

Even when you're editing spreadsheet form definitions directly, you can use the constraint-builder, the relevance-builder, and/or the calculation-builder, all of which are available as tools in the Your forms and datasets section of your server's Design tab.

If you want to create or edit expressions by hand, you can refer to user responses and other fields as follows:

  • For constraints, "." is used to refer to the user's proposed entry or selection for the current field (i.e., for the value you are testing to see if it's valid).
  • Use "${fieldname}" to refer to a prior field's entry, selection, or calculated value. (This gives the value exactly as it will later appear in your data.)

And you can use all of the following operators in any expression:

OperationOperatorExampleExample answer
Addition
+
1 + 12
Subtraction
-
3 - 21
Multiplication
*
3 * 26
Division
div
10 div 25
Modulus
mod
9 mod 21
Equal
=
${fieldname} = 3true or false
Not equal
!=
${fieldname} != 3true or false
Greater-than
>
${fieldname} > 3true or false
>-or-equal
>=
${fieldname} >= 3true or false
Less-than
<
${fieldname} < 3true or false
<-or-equal
<=
${fieldname} <= 3true or false
Or
or
${fieldname} = 3 or ${fieldname} = 4true or false
And
and
${fieldname} > 3 and ${fieldname} < 5true or false
Not
not()
not(${fieldname} > 3 and ${fieldname} < 5)false or true

How to include literal strings
Please note that literal strings within your expressions should always be enclosed in single-quotes, as in 'Male' or ' ' for a single space. If you use double-quotes (like "Male" or " "), your expressions will not work properly.

You can also call any of the following functions:

  1. string-length(field): Returns the length of the string field (as in the constraint "string-length(.) > 3 and string-length(.) < 10").
  2. count-selected(field): Returns the number of items selected in a select_multiple field (as in the constraint "count-selected(.) = 3").
  3. selected(field, value): Returns true or false depending on whether the value indicated in the second parameter was selected in the select_one or select_multiple field indicated in the first parameter (as in the relevance "selected(${fieldname}, 'Male')"). Please note that the second parameter to the selected() function should always specify the internal value associated with the selection, as configured in the choices worksheet's value column. (You cannot specify the label configured in the label column.)
  4. selected-at(field, number): When the passed number is 0, returns the first selected item in a select_multiple field; when the passed number is 1, returns the second selected item; etc. (as in the relevance "selected-at(${fieldname}, 0) = 'Shona'"). Note that the returned value will correspond with the internal value associated with the selection, as configured in the choices worksheet's value column.
  5. jr:choice-name(value, 'field'): Returns the label for a select_one or select_multiple field choice, as defined on the choices worksheet (as in the calculate expression "jr:choice-name(${selectonefield}, '${selectonefield}')" to return the label for the currently-selected choice in the field named "selectonefield"). For select_multiple fields, you can combine with selected-at() to get the label for individual selections (as in "jr:choice-name(selected-at(${selectmultfield}, 0), '${selectmultfield}')" to return the label for the first choice in the field named "selectmultfield"). Note that this function will only retrieve labels from the choices worksheet, so will not work for dynamically-loaded choice options; for options loaded from pre-loaded data, use the pulldata() function in a calculate field to directly pull the label from the appropriate column of the external data.
  6. concat(fieldorstring, fieldorstring, ...): Concatenates fields (and/or strings) together (as in the calculate expression "concat(${firstname}, ' ', ${lastname})").
  7. duration(): Returns the total amount of time spent, in seconds, filling or editing the current form submission. Call this function in a calculate_here field to capture the user's number of seconds into a form when they first reach a particular point (expression for the calculation column: "once(duration())"); you can then subtract one captured duration from another to get the time spent in between. Or, use in a regular calculate field to capture the total duration spent on the form overall (expression for the calculation column: "duration()").
  8. count(repeatgroup): Returns the current number of times that a repeat group has repeated (i.e., the number of "instances" of the group, as in the calculate expression "count(${repeatgroupname})").
  9. sum(repeatedfield): For a field within a repeat group, calculates the sum of all values (as in the calculate expression "sum(${loan_size})").
  10. join(string, repeatedfield): For a field within a repeat group, generates a string-separated list of values (as in the calculate expression "join(', ', ${hh_member_name})" to generate a single comma-separated list from all entered names).
  11. min(repeatedfield): For a field within a repeat group, calculates the minimum of all values (as in the calculate expression "min(${hh_member_age})"). If more than one non-repeating field is passed to min(), then the minimum of the passed fields will be returned (as in "min(${father_age}, ${mother_age})").
  12. max(repeatedfield): For a field within a repeat group, calculates the maximum of all values (as in the calculate expression "max(${hh_member_age})"). If more than one non-repeating field is passed to max(), then the maximum of the passed fields will be returned (as in "max(${son_age}, ${daughter_age})").
  13. index(): Called within a repeat group, returns the index number for the current group or instance (as in the calculate expression "index()", which will return 1 for the first instance of the repeat, 2 for the second, and so on). (We used to recommend the ODK position(..) function instead of index() – but that would fail if you called it from within a non-repeating group that was itself within a repeating group.)
  14. once(expression): For use in expressions in calculate or calculate_here fields only, indicates that the enclosed expression should be calculated only once per form (as in the expression "once(random())" to draw a random number). If a calculated expression is not enclosed in once(), it will recalculate periodically, including each time that the form is edited and saved. In the case of random(), this would draw a new random number every time the form is edited and saved – which is not generally what you want. Be sure to only use once() on the very outside of an expression (so "once(random()*2)" is okay, "2*once(random())" is not); to avoid potential problems, use "once(...)" alone in one calculate field, then you can use that field in more complex expressions elsewhere.
  15. once(random()): Returns a random number between 0 (inclusive) and 1 (exclusive) (as in the calculate expression "once(random())"). To introduce randomness into your forms, use once(random()) in calculated fields, then refer to those calculated fields in your other expressions (don't nest once(random()) directly inside another function or expression). You should always call random() inside the once() function and you should never use random() directly in a relevance expression, because you don't want to generate a new random number every time the relevance is calculated: you want just one stable random number for each filled-out form. For an example, see the sample form in Randomization: Randomizing form elements.
  16. pulldata(source, colname, lookupcolname, lookupval): Pulls data from a dataset or .csv file (as in the calculate expression "pulldata('hhplotdata', 'plot1size', 'hhid_key', ${hhid})", which will pull a value either from an attached .csv file named hhplotdata.csv or from an attached dataset with the unique ID hhplotdata; the value will come from the plot1size column of the pre-loaded data, and the hhid field will be used to identify the matching row in the pre-loaded data's hhid_key column). See Pre-loading data into a form for more information, or Pre-loading: Referencing pre-loaded .csv data for a working example.
  17. indexed-repeat(repeatedfield, repeatgroup, index): References a field or group that is inside a prior repeat group. The first parameter specifies the prior field or group in which you are interested; the second specifies the prior repeat group within which the field or group of interest is located; and the third specifies the instance number, within the prior repeat group, to use. For example, the calculate expression "indexed-repeat(${name}, ${names}, 1)" will return the first name available when the "name" field is inside a prior repeat group named "names". From inside a later repeat group, the calculate expression "indexed-repeat(${name}, ${names}, index())" will pull the xth name from the prior repeat group, where x is the instance number of the current repeat group (e.g., if currently in the fourth instance of a repeat group, it will return the fourth name from the earlier repeat group). If you need to reference a field or group within multiple nested repeat groups, you can supply additional parameters to indicate the instance numbers to use for each level of nesting. For example, the calculate expression "indexed-repeat(${name}, ${families}, ${familynumber}, ${names}, ${membernumber})" will pull a particular family member's name when family member names are inside a repeat group that is itself inside a repeat group of families. For two examples, see Rosters: Two methods for repeated questions. Finally, note that when the passed-in instance number is invalid, an instance number of 1 will be automatically used instead (so the first instance will be returned for such cases).
  18. rank-index(index, repeatedfield): calculates the ordinal rank of the specified instance of a repeated field for use outside the repeat group (as in the calculate expression "rank-index(1, ${random_draw})" to calculate the rank of the first instance, based on the value of its "random_draw" field as compared with other instances' values). The rank of 1 is assigned to the instance with the highest value, the rank of 2 to the instance with the next-highest value, and so on. Instances with the same value are ordered arbitrarily (they are not given the same rank, so every instance will have a unique rank). If you pass an invalid index or an index to an instance with a non-numeric value, a rank of 999 will be returned. See just below for a variation that can be used within the same repeat group.
  19. rank-value(fieldorvalue, fieldorlist): calculates the ordinal rank of a given value relative to a list of values (as in the calculate expression "rank-value(3, '4 2 1 9 3 7')" to calculate the rank of 3 in the given list, or as in "rank-value(${random_draw}, ${list_of_draws})" if the value and the list are both stored in fields). The rank of 1 is assigned to the highest value in the list, the rank of 2 to the next-highest value, and so on. If you pass an empty list or a value that's not in the list, a rank of 999 will be returned. Use this variation to calculate the rank within the same repeat group as the field being ranked. In this use case, add a calculate field outside the repeat group to construct the full list of values using join() (as in "join(' ', ${random_draw})" when the name of the repeated field is "random_draw"); then, within the repeat group, find the current instance's rank with another calculate field (as in "rank-value(${random_draw}, ${list_of_draws})" when the name of the joined list you added outside the repeat is "list_of_draws").
  20. de-duplicate(string, field): For a string-separated list of items, removes duplicates (as in the calculate expression "de-duplicate(' ', ${fieldname})" to remove duplicates from a space-separated list). For example, say you had a repeated multiple-choice field. Outside the repeat group itself, you might want to join all selections together into one calculated list, as in "join(' ', ${repeatedfield})" – but that list might then contain the same selections multiple times. The calculate expression "de-duplicate(' ', join(' ', ${repeatedfield}))" would join together all of the selections, and remove duplicates. You could then call count-selected(), selected-at(), etc. on the combined list in the calculated field.
  21. distance-between(geopointfield1, geopointfield2): Returns the distance, in meters, between two geopoint fields (as in the calculate expression "distance-between(${start_gps}, ${end_gps})"). Two important notes on usage: (1) keep in mind that the accuracy of the distance calculated will depend on the accuracy of the GPS readings, so try to be sure to get accurate GPS readings; and (2) if non-GPS values are passed into this function, the form will give an error that will prevent the submission from being finalized and submitted, so please ensure that any calculate field that calls this function has a relevance condition set such that the field only executes if both parameters are actual GPS coordinates.
  22. area(repeatedgeopointfield): Returns the area enclosed, in square-meters, within a series of repeated geopoint fields (as in the calculate expression "area(${gps_reading})", called outside a repeat group that includes a field named "gps_reading"). (Keep in mind that the accuracy of the area calculated will depend on the accuracy of the GPS readings, so try to be sure to get accurate GPS readings.)
  23. short-geopoint(geopointfield): Returns a string containing the GPS location with only the longitude and latitude, no altitude or accuracy (as in the calculate expression "short-geopoint(${location})"). You might use this if publishing data to outside systems that are confused by altitude or accuracy.
  24. substr(fieldorstring, startindex, endindex): Returns a substring starting at startindex and ending just before endindex (as in "substr(${phone}, 0, 3)" to get the first three digits of a phone number). Indexes start at 0 for the first character in the string.
  25. coalesce(field1, field2): Returns field1 if it isn't empty, otherwise returns field2 (as in the calculate expression "coalesce(${id}, ${id2})").
  26. round(field, digits): Rounds the numeric field value to the specified number of digits after the decimal place (as in the calculate expression "round(${interest_rate}, 2)").
  27. regex(field, expression): Returns true or false depending on whether the field matches the regular expression specified (as in the constraint "regex(., '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}')" which checks for a valid-looking email address). Because regular expressions can be arbitrarily complex, this function allows for construction of advanced relevance and constraint expressions.
  28. if(expression, valueiftrue, valueiffalse): Returns one of two values, depending on whether an expression is true (as in the calculate expression "if(selected(${country}, 'South Africa') or selected(${country}, 'Zimbabwe'), 'SADC', 'Non-SADC')").
  29. format-number(field): Formats an integer or decimal field according to the user's locale settings (as in the calculate expression "format-number(${income})", which might format "120000" as "120,000").
  30. number(field): Converts field to a number (as in "number('34.8') = 34.8").
  31. int(field): Converts field to an integer (as in "int('39') = 39").
  32. string(field): Converts field to a string (as in "string(34.8) = '34.8'").
  33. date(string): Converts string into a date (as in the relevance "${fieldname} > date('2013-01-31')").
  34. date-time(string): Converts string into a date-time (as in the relevance "${fieldname} > date-time('2013-01-31T16:42:00')").
  35. format-date-time(field, format): Converts date and/or time into a string (as in the calculate expression "format-date-time(${fieldname}, '%Y-%b-%e %H:%M:%S')"). In the format string, %Y indicates four-digit year, %y two-digit year, %m two-digit month, %n one-or-two-digit month, %b three-letter month, %d two-digit day, %e one-or-two-digit day, %H two-digit hour, %h one-or-two-digit hour, %M two-digit minute, %S two-digit seconds, %3 three-digit milliseconds, and %a three-letter day of week.
  36. today(): Returns the current date (as in the calculate expression "format-date-time(today(), '%Y-%b-%e')").
  37. now(): Returns the current date and time (as in the calculate_here expression "once(format-date-time(now(), '%Y-%b-%e %H:%M:%S'))" for saving the date and time at which a particular point in your form is first reached).
  38. uuid(): Calculates a unique random ID (as in the calculate expression "once(uuid())").
  39. version(): Returns the version number of the current form (as in the calculate expression "version()").
  40. username(): Returns the currently-configured username of the user filling in the form (as in the calculate expression "username()").
  41. linebreak(): Returns a linebreak character (as in the calculate expression "concat(${field1}, linebreak(), ${field2}, linebreak(), ${field3})" for storing a list of three field values with linebreaks between them).
  42. hash(fieldorvalue, ...): Returns a hash value that represents the one or more parameters passed (as in the calculate expression "hash(${name})" or "hash(${name}, ${birthday})")). See this page for more details on hash values and their potential uses.
  43. pow(base, exponent): Returns the field, number, or expression in the first parameter raised to the power of the field, number, or expression in the second parameter (as in the calculate expression "pow(1+${annual_interest_rate}, ${years_of_interest})").
  44. log10(fieldorvalue): Returns the base-ten logarithm of the field or value passed in.
  45. sin(fieldorvalue): Returns the sine of the field or value passed in, expressed in radians.
  46. cos(fieldorvalue): Returns the cosine of the field or value passed in, expressed in radians.
  47. tan(fieldorvalue): Returns the tangent of the field or value passed in, expressed in radians.
  48. asin(fieldorvalue): Returns the arc sine of the field or value passed in, expressed in radians.
  49. acos(fieldorvalue): Returns the arc cosine of the field or value passed in, expressed in radians.
  50. atan(fieldorvalue): Returns the arc tangent of the field or value passed in, expressed in radians.
  51. atan2(x, y): Returns the angle in radians subtended at the origin by the point on a plane with coordinates (x, y) and the positive x-axis, the result being in the range -pi() to pi().
  52. sqrt(fieldorvalue): Returns the non-negative square root of the field or value passed in.
  53. exp(x): Returns the value of e^x.
  54. pi(): Returns pi.
Previous Next