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:

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

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

Rename the XLSX file to your_table_id.xlsx

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.

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

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

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.

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

Connect your device to your computer with a USB cable.

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.

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 Skip Logic

Skip logic (Conditional Branching) is an amazing feature in ODK-X Survey that changes the next prompt or screen the user sees based on their current response. This enables the form creator to give users a unique and personalized experience depending on their answers throughout the entire survey.

Skip logic is implemented using if, else, else if and end if tags. It uses the clause and condition columns in the survey worksheet, the if, else and end if tags are placed in the clause column and the conditional expressions are housed in the condition column.

Below is the survey worksheet of an example survey that implements skip logic. ODK-X has built-in JavaScipt Operators which are useful for creating complex conditional expressions.

Using Skip logic Example Survey Worksheet
clause	condition	type	values_list	name	display.prompt.text
		select_one	order_list	menu	What would you like to get?
if	selected(data('menu'), 'doughnut') \|\| selected(data('menu'), 'bread roll') \|\| selected(data('menu'), 'cinnamon roll')
begin screen
		select_one	box_list	box	What would you like?
end if
if	selected(data('menu'), 'cake')
begin screen
		select_one	size_list	size	What size of cake would you like?
		select_one	flavor_list	flavor	What cake flavor would you like?
end screen
end if
if	selected(data('menu'), 'cupcake')
begin screen
		select_one	box_list	box	What would you like?
		select_one	flavor_list	flavor	What cake flavor would you like?
end screen
end if
		note	<h3>Order Summary for <u>{{data.name}}</u></h3>

In the example worksheet above, the respondent can pick through a number of pastries available and select one option. The next set of questions that follow are fully dependent on the choice the user makes. For example, if the user picked Cakes; they are asked for a flavor and size they would like whereas if the user picked Doughnuts; they are asked what size of box they would like.

Note

An important thing to remember when using the clause column is the opening if tag and the closing end if tags. The general rule is that all if tags must have a corresponding closing end if tag.

Note

Do know that if you are using required along with skip logic, it is important that both the required and if conditionals align.

The required column takes a conditional expression. If the condition resolves to true, the user will not be able to navigate to the next survey screen until the prompt is answered. If the prompt is left blank, its default value is false.

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	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
type	values_list	name	display.prompt.text
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	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 <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 async_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	type	name	values_list
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:

Within your_form directory, include the customPromptTypes.js file. If your_form was named test, your directory would be app/config/test/forms/test.
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.
Update customPromptTypes.js to include the path to your async_assign.handlebars template. In keeping with the example, line 13 of customPromptTypes.js should read templatePath: '../config/test/forms/test/templates/async_assign.handlebars',.
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

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

Using ODK-X XLSX Converter

Creating and Loading a Form into ODK-X Survey

Creating a Simple Survey Form

Adding Multiple Choice Questions

Using Skip Logic

Using Custom Section Worksheets

Using Calculations

Using Queries

Linked Tables

Internationalization

More Advanced Branching

Creating a Custom Initial Worksheet

Using Validate

Customizing Prompts

Other Features

Creating and Loading a Form into ODK-X Survey 