Guidelines to be followed when designing ODK forms using Ms Excel

By Kihara Absolomon, Quiros Carlos, Rogena Jason, Njamba Harrison, Poole Jane


Open Data Kit (ODK) is a suite of Open Source data collection tools which aim at streamlining data collection. ODK has revolutionized the way surveys are conducted and have provided a means of moving away from manual, paper based data collection to an electronic platform.

 

We outline some guidelines to be followed when creating MS Excel spreadsheets which will be processed to become ODK survey(s). The aim of the guidelines is to help in the design of the MS Excel spreadsheets, standardize data collection tools and easen latter data management processes.

Settings

  1. In the excel spreadsheet, it is mandatory to have a settings sheet which should contain the form ID and the name of the form.
  2. Form IDs should be as short as possible and should not contain spaces.
  3. Form IDs should be representative of the project, form and version of the form. The prefered format for the form ID is {project initials}_{shortened form name}_{form version} eg. rmg_abf_v1
  4. Form titles should contain a short project prefix before the actual title eg. “RMG: Animal Breeding Form” with “RMG” in this case being the short project prefix.
  5. A form title shouldn’t necessarily contain the version number. However, incremental versions of an already deployed form should have the version number in the title.

Designing Questions

  1. The questions in the group should have a unique name
  2. Each question should have a question number, denoting the question number in a particular section.
  3. The question number should be preceded by a q, denoting ‘question’, eg. q23 meaning question 23 in a particular section
  4. The question name should include the section number and the question number eg. s12q23 meaning, question 23 in section 12.
  5. The combination of section and question numbers will form the prefix to the question name. eg. s12q23_ will be the prefix of question 23 in section 12.
  6. The question name should have a descriptive suffix describing the question. eg. weaning_weight showing that the question is about weaning weights. However the suffix should not be more than 20 characters and the less characters the better.
  7. The question name should be a combination of a prefix having the section number and question number and a suffix describing the question. eg. s12q23_weaning_weight showing that the question is about weaning weights and is found in section 12 question 23 of the survey.
  8. Select with “Other” as an option should be carefully evaluated and avoided if possible. They complicate the form and make grouping problematic. ODK evaluates the entered responses on moving to a next screen and if there is need to create an “other” field, it is done in the next screen. In case questions are grouped, this creates a challenge in implementing this functionality. If it is a must to have the “other” option:
  1. The question having the option other should not be in a group.
  2. If the question has to be in a group, they should be the last question in that group. This usually calls for splitting groups to accommodate this requirement.
  1. A group of questions which are determined by a preceding question must be grouped together. eg. A series of bull questions should be grouped and preceded by a question of the sex of the animal. If the answer to the sex of the animal is female, the group of questions can be skipped.
  2. To implement the above recommendations, it is highly advised to use Excel concatenate function and references to cells to allow preserving the names, constraints and relevance in the form.
  3. When adding new questions in new form iterations, the question names SHOULD NEVER BE MODIFIED. Instead add a .1 to the previous question name. eg. If we are to add a new question, weaning time, before the s12q23_weaning_weight in the second version of the form, the question weaning time will have s12q22.1_weaning_time

Sections

The form should be clearly divided in sections. Sections in a form can be made up of independent questions or questions in a table.

  1. The form should be clearly divided in sections.
  2. Each section should be grouped in one group.
  3. The section should have a section number.
  4. The section number should be preceded by an s denoting ‘section’ eg. s12 meaning section 12

Groups

Groups should be used when grouping a sub set of questions. Questions can be grouped mainly because of 2 main reasons:

  1. To consolidate related questions together to help in later on analysis (as defined in the designed questionnaire)
  2. To consolidate questions so that they can be displayed on the screen together

Factors to consider when creating groups:

  1. A group should have a group name.
  2. The group name should loosely define the contents of the group.
  3. The group name should start with the prefix grp_ followed by the group name
  4. The group name should be enclosed with the tags start_group and end_group
  5. Groups defining sections should also follow this guideline.

Tabular Data

A group of questions which is in tabular form are essentially groups but are repeating groups which are usually determined by the subject matter (livestock species) of the repeat, eg. ownership of different livestock species as from the table below. Tables should be treated with utmost care. Tabular questionnaires are presented in ODK collect as repeats and in the extracted data as supplementary sheets.

F1. Livestock Species

F2. Number owned  by the household at this location

F3. Number kept but not owned

F4. Number owned by household but kept elsewhere

F5.Total Owned

(F2+F4)

Owner - Code:

1

2

3

4

OTHER

1

2

3

4

5

 

Cattle

a. Local

No / Yes

 

 

 

 

 

 

 

 

 

 

b. Cross

No / Yes

 

 

 

 

 

 

 

 

 

 

c. Pure exotic

No / Yes

 

 

 

 

 

 

 

 

 

 

Buffalo

a. Local

No / Yes

 

 

 

 

 

 

 

 

 

 

Sheep

a. Local

No / Yes

 

 

 

 

 

 

 

 

 

 

b. Cross/Exotic

No / Yes

 

 

 

 

 

 

 

 

 

 

Goats

a. Local

No / Yes

 

 

 

 

 

 

 

 

 

 

b. Cross/Exotic

No / Yes

 

 

 

 

 

 

 

 

 

 

Poultry

a. Local

No / Yes

 

 

 

 

 

 

 

 

 

 

b. Cross/exotic

No / Yes

 

 

 

 

 

 

 

 

 

 

Donkeys/Horses

No / Yes

 

 

 

 

 

 

 

 

 

 

Rabbits

No / Yes

 

 

 

 

 

 

 

 

 

 

Other, specify

No / Yes

 

 

 

 

 

 

 

 

 

 

Factors to consider when dealing with tabular questions:

  1. Nested repeats are not allowed in ODK collect
  2. Determine the number of repeats, by asking the necessary question before the group and outside the repeat and then have the group repeated the exact number of times. eg. Select the livestock species that are kept by the farmer. From this question, design the section so that the group of questions are repeated the exact number of times as answered.
  3. Collapse fields which can cause nested repeats, eg. in the case above. Instead of having separate questions for species and origin (exotic, local, etc), have one question combined for species and origin and the repeated question tied to that question. eg. Local Cattle, Exotic Cattle, Pure Exotic Cattle, Local Buffalo, Local Sheep, Cross or Exotic Sheep, etc will be the main questions instead of the separate questions above.
  4. Follow the naming scheme for groups above.

Nested Groups

Nested groups are HIGHLY DISCOURAGED and should be used sparingly and only when grouping questions so that they can be displayed on a single page on ODK collect. The procedure for grouping follows the procedure as defined above and as follows:

  1. Nested groups should only be 1 group deep.
  2. The group name should have a prefix of grp_ and a suffix comprising of the section number and question number of the first question in the intended group. eg. If the first question in the intended group is in section 2 question 6, then the group will have a name grp_s2q6.

Grouping exceptions

  1. If a question has an or_other type, it CANNOT be in a group of questions which are to be displayed in one page.

Repeats

  1. Repeats should have a prefix rpt denoting the repeat section

Choices options

  1. Repetition of options should be avoided as much as possible. ie. There should be only 1 Yes/No block of options which will be used for all questions expecting yes/no responses.
  2. The list name for option lists should have the suffix of their related questions. eg. The options for the question of livestock species owned by a farmer (s2q3_lspecies), should have the list name as lspecies which is the suffix of the question.