Using ODK-X XLSX Converter

ODK-X Survey offers a rich set of features that can be seamlessly integrated into a custom form. A lot of the functionality can be implemented solely within an Excel workbook. This guide is designed to help you take advantage of this via a guided tour of example tasks.

Tip

For a full reference to all the functionality available, see the ODK-X XLSX Converter Reference.

Creating and Loading a Form into ODK-X Survey

Below are the steps to create a new form from the exampleForm:

  1. Within the Application Designer's folder, create the following directory structure app/config/tables/your_table_id/forms/your_table_id/

  2. Copy the exampleForm.xlsx from app/config/tables/exampleForm/forms/exampleForm/ into this new directory.

  3. Rename the XLSX file to your_table_id.xlsx

  4. Edit the XLSX file and on the settings worksheet, change the value for table_id to your_table_id. Then update the display title for the survey and the form version. Save the changes.

  5. If you have not already, run grunt to launch the Chrome browser and open the Application Designer home page.

  6. Navigate to the XLSX Converter tab, choose this file to convert it. Once converted, choose Save to File System and click OK on the 3 pop-ups that alert you to the saving of 3 files to the file system. The three files that are saved are:

  • app/config/tables/your_table_id/definition.csv – defines the user-defined columns in your table

  • app/config/tables/your_table_id/properties.csv – defines the appearance and available detail and list view HTML files for the table

  • app/config/tables/your_table_id/forms/your_table_id/formDef.json – defines the ODK-X Survey form defined by the XLSX file

  1. The first two files are written only if the form id matches the table id. That form and the XLSX file define the data table.

  2. Repeat the edit, conversion, and save steps to update the columns in your table and your survey form.

  3. Connect your device to your computer with a USB cable.

  4. In a separate command window, navigate to the Application Designer directory and type:

$ grunt adbpush

to push the contents of the app/config directory to your device.

  1. Start ODK-X Survey. The form should now be available in ODK-X Survey.

Creating a Simple Survey Form

Typing the following in the survey worksheet of a workbook with an appropriate settings worksheet will result in a simple survey.

Creating a Simple Survey Example Form

clause

Condition

type

name

display.prompt.text

integer

person_age

How old are you?

if

data('person_age') >= 18

begin screen

text

pizza_type

What is your favorite kind of pizza?

integer

num_slices

How many slices would you like?

end screen

else

note

You are too young to be eating pizza

end if

The first row contains an empty clause and an empty condition column. Therefore, the display.prompt.text will be shown on the screen, and the resulting integer answer will be stored in the variable person_age.

On the next line there is an if in the clause column and data('person_age') >= 18 in the condition column. If the answer stored in the variable person_age is greater than or equal to 18, the following commands should be done until either an else or an end if tag is reached. Notice the other three columns are left blank.

In the next row, there is a begin screen tag in the clause column. The remaining four columns are left blank. Until an end screen tag is reached in the clause column, all the following questions will be displayed on one screen. In this case, the user will be asked to input their favorite type of pizza and how many slices they would like on the same page, assuming they are 18 or older.

In the next row, there is an else tag. Until end if is reached, anyone who did not satisfy the requirement for the if tag will be asked the following questions. In this case, a note to the user that they are too young to be eating pizza will be displayed.

Note

An important thing to remember when using the clause column is when to open and close new tags. The general rule is that the most recently opened grouping is the first to be closed.

Adding Multiple Choice Questions

There are three types of multiple choice questions supported by ODK-X Survey:

  • select_one

  • select_one_with_other

  • select_multiple

Multiple choice questions use the values_list column in the survey worksheet. The values_list column is what links a multiple choice question to its answer set contained on the choices worksheet.

The pizza survey example used earlier can be improved upon with multiple choice options.The resulting survey worksheet would look like this:

Adding Multiple Choice Questions Example Survey Worksheet

clause

Condition

type

values_list

name

display.prompt.text

select_one

yes_no

person_age

Are you 18 or older?

if

selected(data('person_age'), 'yes')

begin screen

select_multiple

topping_list

pizza_type

What are your favorite kind of pizza toppings (select up to 3)?

integer

num_slice

How many slices would you like?

end screen

else

note

You are too young to be eating pizza

end if

and the corresponding choices worksheet would look like this:

Adding Multiple Choice Questions Example Choices Worksheet

choice_list_name

data_value

display.title.text

yes_no

yes

Yes

yes_no

no

No

topping_list

pepperoni

Pepperoni

topping_list

olives

Black Olives

topping_list

onions

Onions

topping_list

mushroom

Mushrooms

topping_list

pepper

Green Peppers

topping_list

bacon

Canadian Bacon

topping_list

pineapple

Pineapple

Now, instead of typing their age, the user simply selects whether they are older than 18 or not. Furthermore, instead of entering the type of pizza they like, they can select from a list of toppings.

Tip

Because you determine whether a question is select_one or select_multiple from the survey worksheet, the same choice set on the choices worksheet can be used for both select_one and select_multiple questions.

Using Custom Section Worksheets

Custom section worksheets can be added to a workbook to make the control flow of a survey more readable. We could move all the previous questions about pizza to a new worksheet and name it Pizza. Our survey worksheet would then look like this:

Custom Section Worksheets Example

clause

condition

type

values_list

name

display.prompt.text

do

section Pizza

Tip

When splitting a survey into different sections, it is wise to put a note before each section call with display.prompt.text set to read Section <name_of_section>. This is because a do section <name_of_section> call is transparent to the user. Unless the form designer explicitly adds a note, the user will not realize that they entered a section.

Also, after leaving a section, if the user swipes back, the survey will go to the row before the do section call. If the user then swipes forward at this point, the survey will go to the beginning of the section they just completed. It is often beneficial to the user to put a note before entering a section and before leaving a section.

Using Calculations

The calculates worksheet is an optional worksheet. It consists of two columns:

  • calculation_name: Each row of the calculates page represents a function that can be used elsewhere in the workbook by referencing the individual calculation_name.

  • calculation: The calculation to be performed.

Note

The calculation column can store any valid JavaScript expression.

Tip

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

In general, calculations are referenced in the condition column of survey worksheets. For example, suppose that on the survey page under the variable name birthday the user entered their birthday for a question of type date. The calculates worksheet might look like this:

Calculates Worksheet Example

calculation_name

calculation

daysOld

(now().getTime()-new Date(data('birthday')).getTime())/1000/60/60/24

isBirthdayToday

calculates.daysOld()%365 == (now().getTime()/1000/60/60/24)%365

and one of the survey worksheets may look like this:

Calculation Survey Worksheet Example

clause, condition"

type

name

display.prompt.text

if

calculates.isBirthdayToday()

note

happyBirthday

Happy Birthday!

end if

Notice that the <calculation_name>s do not contain parentheses () at the end of them. However, when referencing them it is always in the format of calculates.<calculation_name>().

Tip

Variable names have scope for the entire workbook.

The calculates worksheet is handy because it adds readability to a workbook. Instead of having long, complicated JavaScript calculations in the survey worksheets, they can be consolidated in one, easy to reference location that allows for reusability. Also notice the consistent use of camelCase for variable naming across the different worksheets.

Using Queries

The queries worksheet is an optional worksheet.

For queries that get their data from external sources, the following columns should be used:

  • query_name

  • query_type

  • uri

  • callback

For linked_table queries, these columns should be used:

  • query_name

  • query_type

  • linked_table_id

  • linked_form_id

  • selection

  • selectionArgs

  • orderBy

  • auxillaryHash

Each row of the queries page represents a choice set that can be used by select prompt types in the workbook. In general, query_name is referenced in the values_list column of survey worksheets. For example, suppose that on the survey page under the variable name region the user is asked to select the region they are from. Then the user is asked to select which country they are from. The choices for the list of countries can be filtered based on the region the user selected. The queries worksheet might look like this:

Queries Worksheet Example

query_name

query_type

uri

callback

regions_csv

csv

"regions.csv"

_.chain(context).pluck('region').uniq().map(function(region){
return {data_value:region, display:{title: {text: region} } };
}).value()

countries.csv

csv

"regions.csv"

_.map(context, function(place){place.data_value = place.country;
place.display = {title: {text:place.country} };
return place;
})

The data for the queries is coming from the regions.csv file that is located in the same directory as the formDef.json and specified in the uri column. Thus, the query_type for both queries is csv. A snippet of the regions.csv file looks like the following:

regions.csv

region

country

Africa

Algeria

Africa

Angola

Africa

Benin

Knowing the structure of the regions.csv helps in understanding the callback function provided in the callback column. The callback function maps the results from the regions.csv file to the data_value and the display.prompt.text fields using JavaScript. The survey worksheets may look like this:

Queries Survey Worksheet Example

clause

condition

type

values_list

name

display.prompt.text

choice_filter

begin screen

select_one_dropdown

regions_csv

region

Please select your region:

select_one_dropdown

countries_csv

country

Please select your country:

choice_item.region === data('region')

end screen

The choice_filter in this example ensures that the options for the country question will only be the countries from the previously selected region. Notice that choice_item.region specifies that any country with a corresponding region equal to the answer stored by the region question will be displayed.

The queries worksheet is powerful because it allows more flexibility in terms of where data for the survey can reside.

Linked Tables

linked_table is the other use for the queries worksheet. linked_table allows you to launch a subform that can edit a different data table. For example, if a survey is dealing with information about households, the user may want to ask questions about the general household but also questions about specific users. linked_table can be used to launch subforms that ask questions about the specific household members. The survey worksheet may look like this:

Linked Table Survey Worksheet Example

clause

condition

type

values_list

name

display.prompt.text

choice_filter

text

house_id

Input the unique household id:

integer

num_members

How many people live in this house?

linked_table

members

Add and enter information for the different household members

select_one

members

household_head

Who is the household head?

The queries worksheet would look like this:

Linked Table Query Worksheet Example

query_name

query_type

linked_form_id

linked_table_id

selection

selectionArgs

newRowInitialElementKeyToValueMap

members

linked_table

members_info

house_members

house_id = ?

[ opendatakit.getCurrentInstanceId() ]

{ house_id: opendatakit.getCurrentInstanceId() }

First the user enters a house id for the house and answers an arbitrary question about its residents. This information is stored in the data table for general household information (specified on the settings worksheet under table_id). Then the user reaches a linked_table prompt that uses the values_list members. This is connected to the members query on the queries worksheet. It links to a different survey called members_info that edits a different data table. The selection criteria is that the house_id in the house_members data table matches the instanceID of this current household.

Initially this list will be empty since no members have been added. The user can click on the Create Instance button to add new people for this household. The house_id will be set automatically for this new member via the newRowInitialElementKeyToValueMap content, which specifies that the house_id field in the linked table should be initialized with the instanceID of the current household.

Note

The selection criteria and its type (in this case, house_id and text) must be added to the model subset of the subform (members_info) in order for selection criteria to be persisted to the database and for the subform to be found by its parent form; the selection criteria cannot filter on session variables since those values are never persisted.

When the user finishes the subform, the screen will return to the same linked_table prompt. At this point, the user can continue adding more users, edit an existing member's info, or go to a different screen.

The values_list for the select_one question prompt in the example above also uses the members query. Instead of being able to launch subforms to edit information about different members, the selection criteria is used to populate a multiple choice question. The answer to the multiple choice question is saved to the general household data table, not the members data table.

Internationalization

Survey offers the ability to display text in different languages. This requires usage of the settings worksheet to determine which language to use. However, for any language other than the default language, extra display columns need to be added. For example, if one of the non-default language options was Spanish (2-letter language code "es"), every worksheet with a display.prompt.text column would also need a display.prompt.text.es column. This is true for all columns that need an alternate language option.

Internationalization framework_translations Worksheet Example

type

name

display.prompt.text

display.prompt.text.es

text

user_name

What is your name?

¿Cuál es su nombre?

integer

user_age

How old are you?

¿Cuántos años tienes?

The labels used in the buttons and prompts supplied by ODK-X Survey are defined in the framework_translations sheet of the framework.xlsx file under config/assets/framework/forms/framework.xlsx Simply add your language code and translations to this sheet of this XLSX file and run XLSXConverter on it to enable support of your language across all of the built-in buttons and prompts within ODK-X Survey.

More Advanced Branching

ODK-X Survey supports situations where the user needs to be in control of which survey or section of a survey they are working on. To do this, the branch_label column is used, as well as the choices worksheet. It also utilizes a new question type: user_branch. The following example combines aforementioned surveys and allows the user to decide whether they want to fill out the survey about pizza, or the survey about birthdays.

A choice set needs to be added to the choices worksheet with the applicable branching options. The resulting choices worksheet would look like this:

Branching Choices Worksheet Example

choice_list_name

data_value

display.title.text

which_form

pizza_form

Order pizza?

which_form

birthday_form

Is it your birthday?

And the survey page would look like this:

Branching Survey Worksheet Example

branch_label

clause

condition

type

values_list

display.prompt.text

user_branch

which_form

Choose a survey to fill out

pizza_form

do section pizza

birthday_form

do section birthday

The XLSX file would then have corresponding section worksheets called pizza and birthday that contain the survey examples documented earlier.

Creating a Custom Initial Worksheet

When ODK-X Survey opens, it displays a list of the different forms available on the device. After the user has selected which type of form to work on, Survey launches the initial worksheet for that particular survey. So far the initial worksheet has not been discussed and if one is not explicitly included in the XLSX file, survey uses this default initial worksheet:

Custom Initial Worksheet Example

clause

Condition

type

display.prompt.text

if // start

(opendatakit.getCurrentInstanceId() != null)

opening

Edit form

do section survey

finalize

Save form

else // start

instances

Saved instances

end if // start

This checks to see if an instance of the current form has been selected (opendatakit.getCurrentInstanceId() != null). If it has, it opens that form. If not, it displays the instances that the user can edit. This utilizes three new types:

  • opening

  • finalize

  • instances

Warning

When creating a custom initial worksheet, it is very important to include a finalize type. After completing a survey, it is the finalize prompt that lets the user formally finish the survey so that the results can be used.

Using Validate

When users start having more control over which questions they are asked, it can lead to problems if they bypass required prompts. The validate feature allows for the form creator to require form validation in custom places. By default, the form performs a validation during the finalize section of the survey. However, this type of operation can be performed at multiple points throughout the survey on specific questions using the prompt type validate and the column validation_tags.

The following example will collect information from a user in section1 and section2 and will prevent completion of section3 if certain questions have invalid answers.

The survey page would look like this:

Validate Survey Worksheet Example

branch_label

Clause

type

values_list

display.prompt.text

welcome_screen

user_branch

which_branch

Choose the section to enter

goto welcome_screen

branch1

note

Selected Section 1

do section section1

note

Returning from Section 1

goto welcome_screen

branch2

note

Selected Section 2

do section section2

note

Returning from Section 2

goto welcome_screen

branch3

note

Selected Section 3

validate user_info

do section section3

note

Returning from Section 3

goto welcome_screen

The choices worksheet would look like this:

Validate Choices Worksheet Example

choice_list_name

data_value

display.title.text

which_branch

branch1

Do Section 1

which_branch

branch2

Do Section 2

which_branch

branch3

Do Section 3

The section1 worksheet would look like this:

Validate Section1 Worksheet Example

type

name

display.prompt.text

required

validation_tags

text

user_name

What is your name?

TRUE

user_info, finalize

integer

user_age

What is your age?

TRUE

user_info, finalize

note

Thank you for answering

The section2 worksheet would look like this:

Validate Section2 Worksheet Example

type

name

display.prompt.text

required

validation_tags

text

occupation

What is your current occupation?

TRUE

user_info, finalize

integer

user_age

How long have you worked at your current job (in years)?

TRUE

finalize

note

Thank you for answering

If the user selects to do section 3 on the welcome page, survey will jump to the branch3 branch_label. The first row says to validate user_info. Survey then checks that every question with the validation_tags user_info has been answered satisfactorily. If the questions have been answered correctly, it will go on to the next line (do section section3). If not, it will force the user to answer the missing, tagged questions.

The use of many different validation_tags can allow users to update information in the survey as it becomes available and to restrict questions that depend on other information. In general, the validation feature can be used to give users more control over their work while still maintaining a level of order and restriction.

Warning

Like the use of sections and gotos, validate has no user interface. In other words, when a user runs into a validate call, they will have no idea unless Survey finds something wrong with the form. Whenever using sections, gotos, or validates, if the form designer wants the user to be aware of what is happening, a note explicitly informing the user must be added.

Customizing Prompts

There are 3 ways to customize prompts:

  • Add additional columns to your XLSX Converter form definitions like inputAttributes to tweak existing prompts.

  • If that's too limiting, you can make a custom HTML template by setting the templatePath column. Templates can include <script> and:code:<style> tags. ODK-X Survey uses handlebars templates. Handlebars has a few built-in helpers for creating conditional templates and templates with repeated components: see their documentation.

  • Finally, if you need to parse data from a special type of input or retain some kind of state while your widget is active, you will need to delve into the ODK-X Survey JavaScript. By providing a customPromptTypes.js file in your form directory, you can define Backbone views that extend the base prompts.

Our HTML page rendering uses a custom database object coupled with Backbone views to define the event handling, validation, data model interactions, and construction of the rendering context object that is passed to Handlebars. The Handlebars templates make use of Bootstrap framework for UI components.

A custom prompt type available in the Application Designer repository is async_assign. With async_assign, a user is able to assign a value to a prompt using data collected from a different Survey form with a different underlying database table. As the name implies, the value is assigned to the prompt asynchronously.

Tip

async_assign must be used on a screen previous to where the prompt value will be needed.

Thus, a user should not use async_assign to assign a value to a prompt and then attempt to use the prompt within that same screen as the value may not have been assigned yet. Once the value is assigned to the prompt, it can be used in subsequent screens.

The reason for not being able to use the value of a prompt from an aync_assign within the same screen has to do with the design of Survey. Every instance of a Survey form that a user fills out creates a row in a database table. Although the database interactions in Survey are asynchronous, you are able to see your data changes on the screen immediately because the data for the row is cached in a model data structure. When async_assign is used, the formDef.json file for the other form is read to create a model. After that, the database table used to store the instances for the other form is queried to return the value(s) that are relevant for the assignment. These value(s) can then be manipulated for the assignment.

async_assign Types Table

Name

Return Type

Description

async_assign_max

number

Returns the maximum value out of all form instances
that meet a query criteria.

async_assign_min

number

Returns the minimum value out of all form instances
that meet a query criteria.

async_assign_avg

number

Returns the average of all form instances
that meet a query criteria.

async_assign_sum

number

Returns the sum of all form instances
that meet a query criteria.

async_assign_total

number

Returns the total of all form instances
that meet a query criteria.

async_assign_count

number

Returns the number of values from all form instances
that meet a query criteria.

async_assign_single_string

string

Returns the first string from a form instance
that meets the query criteria.

There are 2 forms that use async_assign in the Application Designer repository – the agriculture.xlsx and the visit.xlsx forms. In this particular example, we will look at the usage of the async_assign_single_string in the visit.xlsx form. Only the relevant portions for the example are shown.

async_assign_single_string visit survey Worksheet Excerpt

clause

condition

type

name

values_list

calculation

display.prompt.text

begin screen

async_assign_single_string

plant_type_query_text

plant_type_query

end screen

assign

plant_type

data('plant_type_query_text')

From the example, we can see that plant_type_query_text is assigned the value provided by plant_type_query. The value of plant_type_query_text is then used on the next screen to assign a value to plant_type. The model worksheet for the visit.xlsx form shows that plant_type_query_text is of type string. The relevant portion of the model worksheet is provided.

visit model Worksheet Excerpt

name

type

isSessionVariable

plant_type_query_text

string

TRUE

The queries worksheet shows that the plant_type_query will assign the value of the fieldName planting from the plot instance with the same plot_id as this visit instance to the plant_type_query_text prompt. See the relevant portion of the queries worksheet below.

visit queries Worksheet Excerpt

query_name

query_type

linked_form_id

linked_table_id

selection

selectionArgs

fieldName

newRowInitialElementKeyToValueMap

openRowInitialElementKeyToValueMap

plant_type_query

linked_table

plot

plot

_id = ?

[data('plot_id')]

planting

'{ plot_id : data('plot_id') }

{}

How to use async_assign:
  1. Within your_form directory, include the customPromptTypes.js file. If your_form was named test, your directory would be app/config/test/forms/test.

  2. Create a folder named templates in your app/config/your_form/forms/your_form directory. Copy the async_assign.handlebars file into this directory. In keeping with the example, this file would be app/config/test/forms/test/templates/async_assign.handlebars.

  3. In your XLSX file, create a worksheet called prompt_types. Copy and paste the following into this worksheet:

promptTypes Survey Worksheet

prompt_type_name

type

async_assign_max

number

async_assign_min

number

async_assign_avg

number

async_assign_sum

number

async_assign_total

number

async_assign_count

number

async_assign_single_string

string

  1. Now you can use the async_assign prompt types in your form.

The async_assign prompt types can be customized further if you are familiar with JavaScript.

Other Features

Different surveys and forms can also be entered using the external_link type, the url column, and the url.cell_type column. To access a separate survey stored elsewhere, a local url can be specified in the format: '?' + opendatakit.getHashString('<relative path to survey>', null). Converting the example above to this format would leave the choices worksheet looking the same. However, the survey worksheet would look as follows:

External Link Survey Worksheet Example

branch_label

clause

condition

type

values_list

display.prompt.text

url

url.cell_type

user_branch

which_form

Choose a survey to fill out

pizza_form

external_link

Open Form

'?' + opendatakit.getHashString('../config/tables/pizza/forms/pizza/', null)

formula

exit section

birthday_form

external_link

Open Form

'?' + opendatakit.getHashString('../config/tables/birthdays/forms/birthday/', null)

formula

exit section