Using ODK XLSX Converter

ODK 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 XLSX Converter Reference.

Creating and Loading a Form into ODK 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 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 Survey. The form should now be available in ODK 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 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 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 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 Survey.

More Advanced Branching

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

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