ODK-X XLSX Converter Reference

Excel Worksheets

A workbook is composed of one or more worksheets. XLSX Converter expects the worksheets within a workbook to use the following nomenclature.

Worksheet Reference Table

Worksheet

Required?

Description

survey

Required

Contains the content and control flow of the
survey. It contains the full list of questions
and determines the order in which they will be
asked. This worksheet can be broken into
different section worksheets for ease of use.

settings

Required

Includes such details as the form name and id,
as well as the default language.

properties

Optional

Defines the key-value properties that can
specify the detail, list view, and other
properties to use with this table. This sheet
should only be specified in forms whose
form_id matches their table_id.

calculates

Optional

Contains the JavaScript formulas that can be
used in other worksheets

choices

Optional

Contains the sets of choices for multiple
choice questions. Each row represents a
response. Choices with the same
choice_list_name are considered to be
part of the same choice set. Choice sets can
be used multiple times throughout a survey
(such as a yes/no choice set).

model

Optional

Defines the table definition in cases where
multiple forms edit the same data table

queries

Optional

Gets data from an external source that can
be used as the choice set for multiple
choice or linked_table questions much like
the choices worksheet.

user_defined_section

Optional

Worksheets with custom section names can be
used in conjunction with the survey worksheet
to simplify control flow.

prompt_types

Optional

Defines custom prompt types that can be used
within a survey.

column_types

Optional

Defines custom column types that are formulas,
functions, or pathnames.

framework_translations

Required

ONLY framework.xlsx. Translations for
standard prompts.

common_translations

Optional

ONLY framework.xlsx. Application-wide
translations.

table_specific_translations

Optional

Only in form_id matching table_id.
Translations specific to a given table_id.

Note

Each worksheet has a set of required and optional columns. For the XLSX workbook to be valid, all entries must have legal values in the required columns. Optional columns can be left blank at any point, and omitted entirely if not used.

Survey

All XLSX Converter form definitions require a survey sheet. The survey worksheet contains the structure and most of the content of the form. It contains the full list of questions and information about how those questions should be presented. Most rows represent a question; the rest of the rows specify control structures such as screen groups. Blank rows are ignored.

Note

In this document, questions and question types will also be referred to as prompts and prompt types.

There are many prompts available for form development. Some ask the user a question and get a response, but other prompts are simply informational and referring to them as questions is not semantically correct.

Required Columns

A list of the required columns for a survey worksheet follows.

Survey Worksheet Required Columns

Column

Description

type

The prompt type that will be used to display information to the user. Prompt
types can also be used to get data from a user.

name

The name of the prompt type. This name will be used throughout the workbook
to reference the prompt.

display.prompt

A string token identifying the translation entry that can define the text,
audio, image and video to display for this prompt.

Alternatively, this column can be omitted and the prompt text can be
specified directly via the display.prompt.text column.

Optional Columns

A list of the optional columns that can be incorporated into a survey worksheet is below.

Survey Worksheet Optional Columns

Column

Description

branch_label

Used to identify which part of the survey to branch to when
used with a goto clause or user_branch prompt.

calculation

When used with the assign prompt type, assigns a value to a
prompt type.

choice_filter

Used to filter the choices of a multiple choice or
linked_table prompt.

clause

Used in conjunction with the condition column to manage the
control flow of the survey. clause and condition control which
questions get asked in what order, if at all. The clause column
contains control flow options such as if, and the condition
column contains a predicate to determine if action will occur.
if statements always require a condition statement. For other
clause statements, a blank condition column is assumed to
be true. Other commands include begin screen, end screen,
and do section.

comments

Never displayed to the user. Used for development purposes to
leave comments about the form for future reference. It is good
style to comment your work.

condition

Used with the clause column to manage the control flow of the
survey. clause and condition control which questions get
asked in what order, if at all. The clause column contains
control flow options such as if, and the condition column
contains a predicate to determine if the following actions will
occur.

constraint

Takes a JavaScript expression. User cannot navigate forward
until the constraint evaluates to true. If left blank, its
default value is true.

default

Used to set the default value.

display.constraint_message

A string token identifying the translation entry with the
text shown to the user if the constraint is violated.

Alternatively, this column can be omitted and this text
can be specified directly via the
display.constraint_message.text column.

display.constraint_message.text

Message displayed to user if the constraint is violated.
Tells the user what needs to change before they can
continue.

display.hint

A string token identifying the translation entry with the text
to display in italics and a smaller font than
display.prompt.text.

Alternatively, this column can be omitted and this text can be
specified directly via the display.hint.text column.

display.hint.text

Used to display text in italics and a smaller font than
display.prompt.text. Can be used to provide extra instructions
to the user.

display.prompt

A string token identifying the translation entry that can define
the text, audio, image and video to display for this prompt.

Alternatively, this column can be omitted and this information
can be specified directly via the display.prompt.* columns.

display.prompt.audio

Allows the user to play an audio recording. Requires a relative
path to where the recording is saved. If saved in the same
folder as the formDef.json, then only the filename of the
recording needs to be specified.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
display.audio column heading).

display.prompt.image

Used to display an image. Requires a relative path to where
the image is saved. If saved in the same folder as the
formDef.json, then only the image file name and the
extension (for example .jpg, .gif) are needed.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
display.image column heading).

display.prompt.text

The text that the user will see for this prompt type.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
display.text column heading).

display.prompt.video

Allows the user play a video. Requires a relative path to where
the video is saved. If saved in the same folder as the
formDef.json, then only the filename of the video needs to be
specified.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
th:display.video column heading).

display.title

A string token identifying the translation entry that can
define the text to display for this prompt in the contents
screen and as the column name in ODK-X Tables.

Alternatively, this column can be omitted and this information
can be specified directly via the display.title.text column.

display.title.text

The display value the user sees when the prompt is displayed
in the contents screen.

Alternatively, this can be specified on the translations sheet
under the display.title string token (under the
th:display.text column heading).

hideInContents

Takes a JavaScript expression. If true, then the prompt on the same row
will not be displayed on the contents screen.
If left blank, its default value is false.

inputAttributes.<attr>

This column can be used in conjunction with the following
prompt types: string, text, integer, decimal. The <attr> can
specify an HTML attribute to be added to the prompt types.
For example, inputAttributes.min with a value of 5 would add
min=”5” into the HTML element for the prompt type.

model.isSessionVariable

Legal value is true. If true, then the data value for the prompt
will be treated as a session variable and won't be saved.

required

Takes a JavaScript expression. If true, the user will not be able
to navigate to the next screen until the question is answered.
If left blank, its default value is false.

templatePath

Must be specified if using a custom handlebars template.
Requires a relative path to where the template is saved. If
saved in the same folder as the formDef.json, then only the
filename of the template needs to be specified.

value_list

Must be used with the choices worksheet. The value_list
column of the survey worksheet connects to the
choice_list_name column on the choices worksheet.

validation_tags

Space-separated list of validation tags.
If validation tags are present on any prompt, this column does not
have a default value.
If this column is absent, left blank, or otherwise empty, its default value
is finalize.

Prompt Types

The following prompt types are available in ODK-X Survey.

Survey Prompt Types

Prompt Type

Description

acknowledge

Used to display a message to the user and have them click a checkbox
to acknowledge that they have read the message.

assign

Used for internal assignment of a variable.

audio

Used to capture an audio recording.

barcode

Used to capture a barcode.

date

Uses a date picker widget to capture a date. Automatically adjusts for timezone.

datetime

Uses a date time picker widget to capture a date and time. Automatically adjusts for timezone.

date_no_time

Uses a date picker widget to capture a date. Does not adjust for timezone.

birth_date

Uses a date picker widget to capture a birth date. Currently behaves the same as date_no_time.

decimal

Used to display a message to the user and have them enter a decimal.

geopoint

Used to capture a GPS location.

image

Used to capture an image.

integer

Used to display a message to the user and have them enter an integer

linked_table

Used to display the instances of table and allows the user to add
another instance, edit an existing instance, or delete an instance.

note

Used to display a message to the user.

select_multiple

Used to ask the user a multiple choice question and allows the user
to click multiple checkboxes.

select_multiple_grid

Used to ask the user a multiple choice question, displays the
choices to the user in a grid, and allows the user to click
multiple grid items.

select_multiple_inline

Used to ask the user a multiple choice question, displays the
choices to the user inline, and allows the user to click multiple
items.

select_one

Used to ask the user a multiple choice question and allows the user
to click one item.

select_one_dropdown

Used to ask the user a multiple choice question and allows the user
to select one item from a dropdown box.

select_one_grid

Used to ask the user a multiple choice question and allows the user
to select one item from a grid.

select_one_inline

Used to ask the user a multiple choice question, displays the choices
to the users inline, and allows the user to click one item.

select_one_integer

Used to ask the user a multiple choice question and allows the user
to click one item. Each item must be set to return an integer value.

select_one_with_other

Used to ask the user a multiple choice question, displays the choices
to the user, and allows the user to click one item. One of the
choices provided is an other option which if clicked provides a text
box for the user to enter a value.

signature

Used to capture a signature by tracing on the device screen.

string

Used to ask the user a question and allows them to enter a string.

text

Used to ask the user a question and allows them to enter text.

time

Uses a time picker widget to capture a time.

user_branch

Used to allow the user to pick which section of the form they would
like to enter.

video

Used to capture a video.

textarea

Used to enter the information in a big text area or paragraphs.

Note

if users anticipates for writing anything longer than 255 characters then the user needs to change the model sheet and change the elementType column. It is shown in the datatypes XLSX, string variables' length can be adjusted from a default of 255 to other lengths with string(len). For example, if you had a string prompt named long_data that you wanted to be 500 characters, you would add the following to your model worksheet. To know more about model

name
type
elementType
long_data
string
string(500)

Text formatting options in Survey

To format text in Survey you need to make changes to the display.promt.text column in the .xlsx file. Some commonly used options are:

Bold: You can bold text by using the container tag <b>. For example: <b>Bold</b>

Italics: You can italicize text by using the container tag <i>. For example: <i>Italic</i>

HEADING: You can format text as a heading using the heading container tag. For example: <h3>HEADING</h3> Please note that this is similar to HTML so the size of the heading as specified here as 3 can be edited as per your convenience.

Underline: You can underline text using the container tag <u>. For example: <u>Underline</u>

Color: You can add colors to your text by using the container tag span. For example: <span style="color:red;">Color</span>

You can also create a line break using the empty tag <br>

Settings

Settings Worksheet Columns

Column

Description

setting_name

The name of the setting within the form

value

The value for the setting

display.title

A string token identifying the translation entry with the text shown to the user
when the (survey) title is displayed.

Alternatively, this column can be omitted and this text can be specified directly
via the display.title.text column.

display.locale

A string token identifying the translation entry with the text shown to the user
when the translation locale is displayed.

Alternatively, this column can be omitted and this text can be specified directly
via the display.locale.text column.

Available setting_name values that can be used:

setting_name values
Value
Required?
Description

table_id

Required
The unique id of the table that the form data gets
stored in.

survey

Required
Specify the title of the form via content of the
display.title.text column. That value will
appear as the title to the user.

form_id

Optional
A unique identifier for the form. Default value is
the unique id that ODK-X Survey uses to identify the
form.

form_version

Optional
A value used for version control of the form. The
recommended format is yearmonthday (for example:
20131212 to say the 12th of December 2013).

<section_name>

Optional
Used with display.title.text to set how the
section name will appear to the user on the contents
screen.

instance_name

Optional
Used to display the name of saved instances of the form.
This must be the name of a prompt type from the survey
worksheet.

default

Optional
Used with display.prompt.text (no qualifier), or
other fields to set the default translation of a UI
element. Specify label under display.locale.text

<language>

Optional
Used with display.prompt.text.<language>, or
other fields to set other language options in the form.

showHeader

Optional
Used to display the header at the top of every page of
the form. Shows the menu with the form title in a navigation bar
with Back and Next buttons. The form header is visible by default.

showFooter

Optional
Used to display a navigation bar at the bottom of
every page of the form. Similar to the header with Back
and Next buttons. The form footer is hidden by default.

A sample settings worksheet might look like this:

Settings Worksheet Example

setting_name

value

display.title.text

display.locale.text

display.locale.text.hindi

table_id

sample_form

form_version

20130819

survey

Sample Form

default

English

English (as Hindi name)

hindi

Hindi

Hindi (as Hindi name)

showFooter

TRUE

Tip

If the survey has been broken up into multiple worksheets, each worksheet can be assigned its own title by adding a row for it and filling in the display.title.text column.

Tip

In the case of multiple languages, the display.locale.text column determines how the different language options are presented to the user.

Properties

This holds the key-value settings for specifying detail and list views, and other parameters. The columns in this sheet are:

Properties Worksheet Columns

Column

Description

partition

The class of property to set

aspect


key

The name of the property to set

type

Valid options: object, array, rowpath, configpath, string, integer, number, boolean

value

The value of the property to set

For example, the following configuration specifies that the default view for the table is the list view (HTML). It also defines the detail view, list view, and map view HTML files. And, for the map view, it defines the color rule to apply to the pins in the map view and the latitude and longitude columns to use in displaying those pins.

Properties Worksheet Example Table

partition

aspect

key

type

value

Table

default

defaultViewType

string

LIST

Table

default

detailViewFileName

string

config/tables/Tea_houses/html/Tea_houses_detail.html

Table

default

listViewFileName

string

config/tables/Tea_houses/html/Tea_houses_list.html

Table

default

mapListViewFileName

string

config/tables/Tea_houses/html/Tea_houses_list.html

TableMapFragment

default

keyColorRuleType

string

None

TableMapFragment

default

keyMapLatCol

string

Location_latitude

TableMapFragment

default

keyMapLongCol

string

Location_longitude

Calculates

The calculates worksheet is an optional worksheet.

Calculates Worksheet Columns

Column

Description

calculation_name

The name used to reference the calculation in other worksheets.

calculation

The JavaScript formula to be evaluated.

Each row of the calculates page represents a function that can be used elsewhere in the workbook by referencing the individual calculation_name. The calculation column can store any valid JavaScript expression. In general,

Note

Calculations are referenced in the condition column of survey worksheets.

Tip

There are built in functions for ODK-X Survey that can be used anywhere in the workbook. See the Formula Functions section for more details.

If a complex calculation is required, you can access the full power of Javascript and the jquery.js (that is: $.some_func(...) ) and underscore.js (that is: _.some_func(...) ) libraries. Internally, the calculate column is wrapped and evaluated as a Javascript function:

function() {
    return (YOUR_CALCULATE_COLUMN_CONTENT_HERE);
}

You can write your own code to perform a join via defining and invoking an anonymous function in your calculate. Here is an example:

(function() {
    var result = "";
    _.each(data('valueListField'), function(element) {
        result = result + ", " + element;
    });
    return result.substring(2);
}) ()

This defines a function and then invokes it. The available functions within a calculates expression are the following:

Available Calculates Functions

Function

Description

Usage

data(fieldName)

Retrieve the value stored under this fieldName

data('myField')

metadata(instanceMetadataFieldName)

Retrieve value stored under this name

metadata('_group_modify')

selected(promptValue, qValue)

Test whether qValue occurs within a select-multiple

selected(data('mySelectMultipleField'),'myChoiceDataValue')

countSelected(promptValue)

Count the number of selections in a select-multiple

countSelected(data('mySelectMultipleField'))

equivalent(promptValue1, promptValue2, ...)

Test if values are equivalent

equivalent(data('promptA'), data('promptB'))

not(conditional)

Negate a condition ( equivalent to !(conditional) )

not(data('fieldA') === data('fieldB'))

now()

Return the current time

isFinalized()

Return whether or not the current row is finalized

assign(fieldName, value)

Store value in fieldName and return value.

(8 + assign('myField', 5))*10

Additionally, the following functions are also available, but are generally not useful in calculates. They are used within template helper functions (…/system/survey/js/handlebarsHelpers.js).

Template Helper Functions

Function

Description

Usage

getCurrentLocale()

Return the currently-active locale

localize(locale, displayProperty)

Localize the given display.xxx text

localize(getCurrentLocale(), display.hint)

width(string)

Determine the rendered width of a string

expandFormDirRelativeUrlPath(content)

Return url for a file within the form directory.

And, finally, you can also reference the opendatakit object (that is: opendatakit.some_func(...) ) within these functions (system/survey/js/opendatakit.js).

Choices

The choices sheet allows you to specify the set of choices for multiple choice prompts.

Choices Worksheet Columns

Column

Description

choice_list_name

The name used to reference the set of choices. This name must be the same
as the values_list in the survey worksheet.

data_value

The value that gets stored as the user’s response.

display.title

A string token identifying the translation entry with the text shown to
the user for this choice value.

Alternatively, this column can be omitted and this text can be
specified directly via the display.title.text column.

display.title.text

The text that the user sees for this choice.

display.title.image

An image that the user will see associated with a particular choice.

The choices worksheet in the XLSX file whose form_id matches the table_id should have all the choice lists. These choice lists are the ones that get written to the properties.csv

Model

The model sheet is an optional sheet that allows you to specify the data model for the table_id specified in the settings worksheet.

Model Worksheet Columns

Column

Description

name

The name of the data field to be used in table_id

type

The type of data that can be put into this data_field of the table.

isSessionVariable

Whether or not this field is a session variable
(not persisted – defaults to false).

Many more columns can be specified, including a default column or, as shown in the exampleForm, a default[0] column to initialize the first element (index zero) of a select multiple field. Default values cannot be calculates and must be simple literal values (integers, numbers and strings). The elementType column can be used to modify how the database is created. For example, as shown in the datatypes XLSX, string variables' length can be adjusted from a default of 255 to other lengths with string(len).

Queries

The queries worksheet is an optional sheet that allows you to request data from external sources for use in select prompts. These are some of the things you can do with queries:

  • Connect to website APIs.

  • Get data from external Android Applications via file content providers.

  • Get data from a linked table

  • Open CSV files included in the survey's directory.

  • Pass key-value maps to linked_table forms when creating or opening that form.

Queries Worksheet Columns

Column

Description

query_name

The name used to reference the information returned by
the query.

query_type

Legal value are ajax, csv, and linked_table.
Used to specify the provenance of the query data.

uri

Used by ajax and csv queries. The uri to use
for an ajax query or the name of the CSV file to
use relative to the location of the formDef.json
file.

callback

Used by ajax and csv queries. The function
that will be used to map the query results to the set of
choices for a multiple choice prompt.

linked_table_id

Used by linked_table queries. The table_id
used to identify the table that the data will come
from. This should match the table_id provided
in the settings worksheet.

linked_form_id

Used by linked_table queries. The id of the form
that will be used to get the results for the
linked_table. This value should match the
form_id value in the settings worksheet.

selection

Used by linked_table queries to filter results
when used with selectionArgs. Specifies the
conditions that must be true for the results to be
selected but must have selectionArgs to work.

selectionArgs

Used by linked_table queries to filter results
when used with selection. The arguments to be
used in the selection described above.

orderBy

Used by linked_table queries to specify the
order in which results should be returned.

newRowInitialElementKeyToValueMap

Used by linked_table queries. A Javascript
object containing key value pairs used to assign
initial values when creating a new row in the
linked table. The key is the element name in the
linked form. The value is the initial value to
assign to the element.

openRowInitialElementKeyToValueMap

Used by linked_table queries. A JavaScript
object containing key value pairs used to assign
initial values when opening an existing row in the
linked table. The key is the element name in the
linked form. The value is the initial value to
assign to the element.

The two columns newRowInitialElementKeyToValueMap and openRowInitialElementKeyToValueMap allow you to pass information from your originating form into the linked form. The element keys in these maps correspond to the element keys in the linked form (not the current form). These can refer to any of the form's fields; commonly, the values you would pass into the openRowInitialElementKeyToValueMap would refer to session variables. You would typically pass the instanceID of the originating form (that is: opendatakit.getInstanceID() ) into the linked form when creating it so that you can store that id in a field in that linked table, thereby tying the newly-created row in that table back to the originating form's row.

User Defined Section

A custom named section is essentially a subset of the survey worksheet. Thus, all of the columns that were described in the survey section are applicable in a custom section worksheet. However, the following worksheet names are reserved and cannot be used to name a custom section worksheet:

  • settings

  • properties

  • choices

  • queries

  • calculates

  • column_types

  • prompt_types

  • model

  • framework_translations

  • common_translations

  • table_specific_translations

Custom prompt_types

Custom prompts can be created within the survey. The prompt_types worksheet can be used to specify the custom prompts so that they will be recognized by Survey.

prompt_types Worksheet Columns

Column

Description

prompt_type_name

The name that will be used to reference the prompt_type

type

The type of object that will be used to store the data received by the user
for this prompt type.

column_types

Custom columns can be used within a workbook that are used to store functions, formulas, and path names. The column_types worksheet can be used to specify these custom columns.

column_types Worksheet Columns

Column

Description

column_type_name

The name that will be used to reference the column.

type

The type of information that will be stored in the column (for instance, function,
formula, app_path_localized).

framework_translations

The framework_translations sheet is only present in the framework.xlsx file. It defines the translations for all of the standard prompts provided by the ODK-X framework.

framework_translations Worksheet Columns

Column

Description

string_token

The name that will be used string to be translated.

text.<locale>

The value of the translated text string. There can be as many of these
columns as you want translated languages (such as text.default, text.gr,
text.es).

image.<locale>

The value of the image url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as image.default, image.gr, image.es).

audio.<locale>

The value of the audio url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as audio.default, audio.gr, audio.es).

video.<locale>

The value of the videourl fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as video.default, video.gr, video.es).

The locale code should generally be the 2-letter language code, or, if necessary, the language_COUNTRY naming used by Android can be used to identify a specific language variant. For example: en_US, en_UK for US English and UK English, respectively.

common_translations

The common_translations sheet is optional. It should only be present in the framework.xlsx file. It can be used by application designers to define translations used across multiple forms and web pages in an application.

The format for this sheet is the same as that for the framework_translations sheet.

framework_translations Worksheet Columns

Column

Description

string_token

The name that will be used string to be translated.

text.<locale>

The value of the translated text string. There can be as many of these
columns as you want translated languages (such as text.default, text.gr,
text.es).

image.<locale>

The value of the image url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as image.default, image.gr, image.es).

audio.<locale>

The value of the audio url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as audio.default, audio.gr, audio.es).

video.<locale>

The value of the videourl fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as video.default, video.gr, video.es).

The locale code should generally be the 2-letter language code, or, if necessary, the language_COUNTRY naming used by Android can be used to identify a specific language variant. For example: en_US, en_UK for US English and UK English, respectively.

table_specific_translations

The table_specific_translations sheet is optional. It should only be present in the XLSX file whose form_id matches the table_id. It defines translations that are available to all forms and web pages specific to that table id.

framework_translations Worksheet Columns

Column

Description

string_token

The name that will be used string to be translated.

text.<locale>

The value of the translated text string. There can be as many of these
columns as you want translated languages (such as text.default, text.gr,
text.es).

image.<locale>

The value of the image url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as image.default, image.gr, image.es).

audio.<locale>

The value of the audio url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as audio.default, audio.gr, audio.es).

video.<locale>

The value of the videourl fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as video.default, video.gr, video.es).

The locale code should generally be the 2-letter language code, or, if necessary, the language_COUNTRY naming used by Android can be used to identify a specific language variant. For example: en_US, en_UK for US English and UK English, respectively.

Built-in Functionality

The jquery and underscore libraries are available when defining calculates expressions or writing statements for the condition column or the required column.

ODK-X Survey exposes built-in functionality through formula functions to decrease form development time.

Formula Functions

The following formula functions can be used to simplify calculations or expressions.

Built in formula functions

Name

Description

Example

assign

Assignment operator that will assign the value
to the field and return the value

assign('fieldname',value)

countSelected

Returns the number of items selected from a
select_multiple prompt

countSelected(data(‘options’))

data

Returns the value of a field or session variable.

data(‘options’)

equivalent

Check to see if two values are equivalent

equivalent(data(‘option1’), data(‘option2’))

isFinalized

Returns true if this submission is finalized

isFinalized()

localize

Localizes the text passed in.

localize(data('options'))

metadata

Returns a metadata field of this row

metadata(‘_group_read_only’)

not

Negates the argument passed in.

not(selected(data('examples'), 'label_features'))

now

Returns the current date

now().getDay()

selected

Returns true if the value selected from a select
prompt is equal to the second argument passed
into the function.

selected(data('visited_continents'), 'NorthAmerica')

And, additionally, the opendatakit object is also available for use in calculates expressions.

Warning

The opendatakit object contains many useful functions but these should be considered internal methods subject to change. When upgrading, be sure to confirm that the methods you use have not disappeared!

JavaScript Operators

The built-in formula functions can be combined in advanced ways using any valid JavaScript expression. This is particularly useful for creating complex condition statements to implement skip patterns or conditional statements for required variables. JavaScript operators will allow the expressions to involve more than one variable or more than one response from a single variable. Parentheses can be used in creating particularly complex conditions. A few basic JavaScript operators:

Basic JavaScript operators

Name

Description

Example

&&

And

data('person_age')>=18 && selected(data('pizza_type'), 'mushroom')

||

Or

(selected(data('pizza_type'), 'mushroom') || selected(data('pizza_type'), 'onions')

==

Equal

data('person_number') == 1

===

Strict equal of the same type

data('consent')==="yes"

>=

Greater than or equal to

data('age') >=18

<=

Less than or equal to

data('age') <=17

Tip

Make sure that statements using && and || operators for variables that were select_one type are logical and that they work as intended. For example, if the variable pizza_type had been a select_one, the statement (selected(data('pizza_type'), 'mushroom') && selected(data('pizza_type'), 'onions') could never be valid, because the respondent could only have selected one or the other or neither, not both. Therefore, the example instead uses a || statement.