XLSForm is a tool to simplify the creation of forms. Forms can be designed with Excel and XLSForm will convert it to an XForm that can be used with B.exson tools. This tutorial teaches you to build your forms using Excel. Please refer to the sample Excel file as you read through the tutorial.

Basic format

Each workbook must have two worksheets: survey and choices.

• 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 they should be presented. Most rows represent a question; the rest of the rows specify control structures such as groups.
• The choices worksheet is used to specify the options for multiple choice questions. Each row represents a multiple choice option. Choices with the same list name are considered part of a related set of choices. This allows a set of choices to be reused in multiple select questions (for example, yes/no choices).

Each of these worksheets has a set of necessary columns that must be present for the .xls file to be valid. Additionally, each worksheet has a set of optional columns that allow further control over the behavior of each entry in the form, but are not essential to the specification. Every entry must have values for each necessary column, but any of the optional columns may be left blank from entry to entry. For example, in the survey worksheet, each question must have a name specified, but one question may have a numerical constraint specified while another has an image file and a third may have niether. These columns, necessary or optional, may appear in any order. Optional columns may be left out completely. Any number of rows may be left blank. All .xls file formatting is ignored so that dividing lines, shading, and font formatting can be used to make the specification more readable.

Question Types

B.exson Collect supports a number of simple question types:

To collect the name and gps coordinates of a store use the following Excel file:

 

Muliple Choice Questions

B.exson Collect has support for both ‘select one’ and ‘select all that apply’ questions. To write a multiple choice question requires adding a second worksheet called ‘choices’ to our Excel workbook. Here is an example of a ‘select one’ question:

1. There are three columns on the ‘choices’ sheet: ‘list name’ contains the name of multiple choice list this choice belongs to. ‘name’ holds the value that will be submitted to the database if this choice is selected. ‘label’ is what will appear on the phone for this choice.
2. To add a select one question to your survey you must add a question with type ‘select_one list-name’ where ‘list-name’ must match a list from the ‘choices’ worksheet.

We can also add multiple choices questions that allow multiple answers to be selected, like so:

Notes / Caveats:

• Make sure you don’t forget the first row and the headings “list name”, “name” and “label.”
• Make sure that your column headers in the “choices” sheet match those in the “survey” sheet (i.e. there will be errors if you columns are “label:English” on the survey and “label” on the choices worksheet)

 

Specify Other

For multiple choice options, one might want to add an “Other” option, which if picked has to be specified further by the user in a free text input field. This is possible through xlsform by having an option list that includes “Other” as an option, and a second question that is relevant only if “Other” was selected the first time around. But because this has to be done a lot in the early phases of surveying, xlsform has a shortcut to make this easy.

This form with automatically ask the user if her/his favorite toppings are cheese, pepperoni, sausage or other, and if the user selects other, what their favorite topping is. Try it out! Notes / Caveats:

• If you have data like this, when you export data, in the pizza_toppings column, you will see a value ‘other’. A second column will have the answer for the questions which the user selected ‘other.’ This makes doing data analysis more cumbersome, so we don’t recommend the or_other construct for large-scale data collection efforts. The option is perfectly appropriate for pilots, however.

 

Cascading Selects

It is possible to create cascading selects (select type questions where the options depend on the options selected in previous questions). For example, you could display cities in a select question based on the country selected in a previous question. In order to use cascading selects you will need to create a choice_filter column in your survey sheet and add some attribute columns to filter on in your choices sheet. There is an example XLSForm available here There are a few caveats to bear in mind: or_other is currently not supported for cascading selects. Best practice for naming attribute column headers in the choices sheet is to begin your names with attr and only use letters (no spaces).

Metadata

B.exson Collect makes a number of metadata fields available for collection:

If I wanted my survey to collect all of this metadata I would put the following at the top of the survey:

Notice there are no labels associated with the metadata, that’s because the phone captures these variables automatically. These questions will not appear on the screen of the phone, but will be included in the xml file B.exson Collect writes to store and transmit the survey data. To see this fields in action, fill out a survey on your phone, connect your phone to your computer and use the sd card as a USB flash drive, navigate to /sdcard/odk/instances and checkout the xml files in the folders there. This will give you an idea of what the data looks like.

Hints

It’s easy to add hints to questions. Simply type in text under the hint column for a question.

 

Grouping Questions

To create a group of questions try the following:

This is a good way to group related questions. To create a repeating group of questions use the following construct:

This is a list of household members, with the name and age of each household member. The phone will ask the name and age of the first household member, and then ask if the enumerator wants to add another ‘Household Member’ group. If the enumerator responds with a ‘yes’ then the name and age of the second household member will be asked, and so on. All data within repeats will be exported in a different worksheet inside excel files.

Nesting Groups of Questions

Groups of questions can be nested:

 

Grouping Questions on a Single Screen

By setting a group's appearance column to field-list you can display multiple questions on a single screen of your survey.

 

Skipping Questions

One great feature of B.exson Collect is the ability to skip a question based on the response to a previous question. Below is an example of how to do this using XLSform:

(choices worksheet ommitted; see above). This will ask the respondent, “Do you like pizza?” and ask which topping the respondent likes if he/she like pizza (and skips the question otherwise). The entry in the ‘relevant’ column is a true or false XPath expression. When the expression evaluates to true the question will be asked, otherwise it will be skipped. XLSforms have some convenient syntax so you don’t have to put the XPath in for the question likes_pizza, instead you can put ${likes_pizza} and the XLSform converter will replace ${likes_pizza} with the absolute XPath to the question named likes_pizza. Here are a couple additional examples of relevance formulas: ${number_x}=${number_y} selected(${select_question}, 'option')

If you want to skip a group of questions put the relevant attribute on a group like follows:

 

Constraining Responses

 

Here we’ve put a constraint on age so that no one can be older than 150 years. If the enumerator puts 151 B.exson Collect will not allow the enumerator to swipe to the next question. The column ‘constraint’ takes an XPath expression. A question is accepted if the ‘constraint’ column evaluates to true. Generally, a constraint pops up a default message telling the enumerator that he/she cannot go forward, but a constraint_message column can override this message on a question-by-question basis.

Multiple Language Support

It’s easy to add multiple languages to a survey. You simply have to name your label::language1 label::language2 etc., and your surveys will be available on multiple languages. To select a different language on the phone, press the Menu key, and the “Change Language” option. For the form below, “English” and “Español” will show up as the possible options.

Adding a hint in a different language in a hint, or adding specific media files for a given language is also possible; you simply use the ::language construct. See the xlsform standard document to see exactly what kinds of column headers can accept a language modification.

Formulas

Formulas are used in the constraint, relevant and calculate columns. Formulas are composed of functions and operators (+,*,div,etc) that are for the most part derived from the XPath specification. The formula evaluation functionality in B.exson is provided by Javarosa.The XPath operators are documented here.

Calculates

Your survey client can perform calculations using the values of preceding questions. In most cases this will require use of a calculate question. For example, in the survey below, we have calculated the tip and shown it to the surveyor. Our survey sheet will look like this:

 

Media

You can also make questions that show the user an image, a video or an audio file as a way of asking a question. In order to do this, you will need to put the media in /odk/forms/formname-media folder on your phone, and reference their filenames from xlsform in a media column.

 

Settings Worksheet

You can include a settings worksheet in your xls file similar to the following:

settings
	form_title	form_id	public_key	submission_url	default_language	version
	example title	example_id	IIBIjANBg...	https://example-odk-aggregate.appspot.com/submission	English	2

They do the following: form_title: The name of the form presented to users. form_id: The name used to identify the form submission_url: The url of a server that submitted forms are to be sent to. public_key: If form instances are to be encrypted, a public key needs to be included in the form. default_language: In localized forms, this sets which translation should be used as the default. None of these settings are required.