Pages Menu
Categories Menu

Posted by on Aug 21, 2015 in Mobile Technologies, What's new | 12 comments

Advanced XLS forms coding: part 2 of 2

Advanced XLS forms coding: part 2 of 2

This post is a follow-up on last week’s “Advanced XLS forms coding: part 1 of 2“.

VIP Guest

Those who coded forms directly in XML can definitely recognize the value of the “choice filter” column in XLS forms, which allows to easily filter the choice being offered in a prompt, based on a previous selection. For example, you wouldn’t want to show the full list of 196 countries if the user already told you he comes from North America.

However, for some types of lists such as villages in a remote region, it can be hard to ensure you have an exhaustive list, therefore it would be preferable to always allow the user to select “Other” and then offer a text input for the missing value.

In this example, we have a list of social workers, based in different region (“prefectures”). We want to use the choice filter to show only the workers in the selected region of operation, however we need to offer some flexibility in case there’s a new hire or a replacement. For this to work, we must first add one entry in the social worker list in the “choice” tab. We add “Other” (“Autre”) option and assign it the value -88. In the column “prefecture”, where the choice filter operates, we also put “-88” as a value (or any value you usually assign to your “Other” statements).


The base list name for the choice filter with “Other” option always available.

In the main “Survey” tab, we have 2 prompts of relevance to this: the INT_Prefecture prompt, which is the region of operation that the user first selects. This is stored in the variable INT_Prefecture. The prompt INT_NameWS is the prompt where the social worker is selected. As can be seen in the choice_filter column, the selection is based on the choice made in the INT_Prefecture prompt. The form then takes that value, and looks into the “tslist_name, and shows all those for which the “prefecture” column value matches the INT_Prefecture value. But because we also added “or prefecture = -88”, it will also show any value in the “tslist_name that has value -88. This is our “Other” option.


The corresponding question, with the choice_filter code.

And the result is that we can both filter the list of workers according to the prefecture of activity AND always offer the option to enter another name, regardless of the prefecture selected:


The selection will filter answer according to our filter value, as well as always allowing “Other” value.

More patterns

Another way that using those patterns can lead to faster coding is when setting up the rather common “If other, please specify”. When coding a long form, it can be advantageous to setup a “generic” prompt of that type, without setting reference to any specific variable. It can then be copy-pasted as needed, while filling in the prompt name to which it refers only at the end:


A “template” question that can be copy-pasted, and filled with variable names.

I try to always append the suffix _OTHER to this type of question, as that allows to quickly identify them. I will generally use as a core part the same name as the question it refers to. For example, if the men’s question about need priority did allow for “If other”, I would name it NEEDPRIORM_OTHER.

I also have only set the generic envelop for the relevant condition for this question. In that case, if any of the need priority is answered by “Other priority” (which value, as always, is -88), then I want the prompt to appear.

The above can then be copy-pasted any number of times and to be functional it only requires adding a complete name and the references to the prompts in the relevant conditions. Proceeding this way is much faster than manually typing each and every such prompts in the form and the benefits add up the longer and more complex the form gets.

Alternative for “If other, specify”

There is an alternative as well for every question which needs to offer a text input option for “Other” types of answers: one must simply append “or_other” in the “type” column, as shown below:


Java Rosa’s built-in “or_other” prompt.

In the list choice, there is no need to add the “Other” option as this will be done automatically by the form:


No need to do anything special with the list: just add “or_other” under question type.

In the end, the output file in your platform of choice will automatically add a column for the “Other” text field, just like that would be the case with the manual coding option described previously.


This is an interesting alternative because it is really quick: it allows you to basically forget everything about the “If other” option, and let the form handle it. However, there will be some defaults choices that you’ll have to live with, should you choose that option:

  • You cannot decide what the label in the list of choices for the user will be: it will say “Other”. If you have a form in multiple languages, you will have to instruct your user that this means “Autre”, “Otro” or “Ostatni”.
  • Same for the coded value of the option: the value will be “other” in small case. If you have (wisely) opted for some sort of system in the assignment of your value, you might have to take this into account. In that case, I would opt to make it uniform and always assign “other” instead of “-88” to all choices that mean “Other”. Remember, “Do what you want, but do it for a reason”.
  • The name of the additional columns will always be “nameOfTheQuestion_other”, with “_other” being automatically added – this would be convenient with our system which is good.

Finally, we are forced to have one specific “other” column for each question. In the previous example (by coding manually the constraints), we had a system that would show only one “Other” column if ANY of the 3 questions have “Other” selected. This is because while we want to know if some choices are missing in the list, we might not be interested in knowing specifically if it applies to the 1st, 2nd or 3rd question: perhaps it is sufficient for us to know that the user wanted to specify something we haven’t listed.

Calculations: dealing with blanks

Performing in-form calculation can be very handy. This can help the enumerator double-check that the value do indeed add-up, or even code constraints in the range that is acceptable for a series of related questions. However, one problem is that if ANY of the prompt referred to in the calculation is left empty, then the result will be blank.

You may get around this by forcing an answer (“required” column), however it is wiser at times not to make all prompts mandatory – when answer cannot always be expected to be available.

There is a workaround for this, and it involved using IF statement to assign the value “0” to any unanswered question. Consider the following:


Allowing a complex calculation to go through even with missing answers.

  • For every variable involved in the calculation, the calculation tests to see if the value is a number equal or greater than 0:
    • If(${A1A_totAvant}>=0, ${A1A_totAvant},0)
    • If it is, then it will use that value in the calculation
      • If(${A1A_totAvant}>=0, ${A1A_totAvant},0)
      • If not, then it will use 0 as a value for the calculation
        • If(${A1A_totAvant}>=0, ${A1A_totAvant},0)

This works, because for the XLS form if an answer is skipped (let’s say that the number A1A_fled isn’t available), then the “value” assigned to that skipped question is blank, and blank will always be considered “less” than any other numeric value. Because a calculation can’t be performed on a blank value (a blank value isn’t 0, it is simply nothing), we must assign a value 0 if we want the rest of the calculation to be performed anyways.

Note that this does NOT assign a value in the original prompt – this is only to allow the calculation to be performed, so that a meaningful note can be showed to the user.

Testing, testing, testing….

Regardless of the platform on which your survey will ultimately be hosted, you will most likely be better off testing your form more often than less. Depending on your level of comfort with XLS form, things can get complicated. Catching problems early on, while they are fewer in numbers and easier to track, will always be easier than to code a whole form and then have to debug problems after problems.

One option for quick testing of forms in development is to use the offline converter (available at where all converter options are listed). When you submit an XLS form to the converter, it will tell you if any syntax is wrong, if there’s a typo in one of the list_choice or value you used in your constraints/relevant conditions, etc. The messages are generally very informative.

Another option is using the Kobo platform:

  • It takes directly the XLS form used for coding, instead of requiring a prior conversion to XML before being uploaded to the server
  • It can be tested directly in the browser, in a page-format that allows to have a global view of the form (instead of the page-by-page display on the phones)

Opening an account is free, therefore it makes sense to have one, even if it isn’t meant as a development platform. It is worth nothing, however, that the behaviour of the form in webform can be slightly different than on the phones. The webform also doesn’t allow to have a feel of how the form will behave with the smaller display of the phones actually used for the survey. It should therefore be used while the form is in development, but it doesn’t replace testing the form with the actual devices that it will be used on.


Before the XLS form, everything had to be coded in XML, which was more complicated and slower. Now that XLS forms have greatly reduced the complexity and time needed to code a survey, it is time to develop coding practices that maximize this benefit, and hopefully the ones above can serve as a good starting point to the development of a personal coding system that is accurate and effective.


  1. Language::English
    Same here.


  2. Hey there, just curious if there is a way to use regex() , search() , or similar pattern for choice_filter. I’m using Kobotoolbox and can’t seem to find a way to use searching for a single select of many choices.


    • Interesting question – you can definitely use regex() as a constraint, like so: regex(${value_to_apply_regex},[regular expression]) in the constraint column. Generally speaking with ODK (or rather java-rosa which is the standard on which the form is built), when you can use an expression somewhere you can use it in any field that can also be evaluated. For example, in a list choice, you can refer to variables in the survey (even if that’s not necessarily intuitive as we tend to think of them as static). One notable exception is the “default” field, which cannot be evaluated – it has to be set as a hard value. I think if you use SurveyCTO you can go around that, but generally speaking it cannot be set to another variable’s value.

      Given that choice filter accepts variable value (region=${some_variable}), I think it should be possible, using the regex() as above and a valid regular expression. It would be interesting to do a follow up on those – regex(), seach() etc. aren’t covered quite a widely a more common functions.

  3. How can I format a text field, into a specific format, so I can use it in xlsform?

  4. Hello!

    Thanks for this amazing blog. It is truly helpful.

    One question can we set a cascade based on values so for example if the enumorator id is between 150 and 200 then the regions that show should be his?


  5. Hello Francis,
    I am getting this error while using KOBO TOOLBOX
    “your form cannot be deployed because it contains errors:

    ODK Validate Errors: org.javarosa.xpath.XPathUnsupportedException: XPath evaluation: unsupported construct [filter expression] >> Something broke the parser. See above for a hint. Result: Invalid

    Can you kindly assist

    • Dear Charles,
      We don’t provide on specific bugs on this blog, please refer your question to the ODK forum:
      You can also get in touch if you want more in-depth support on MDC for your organization.

  6. Hi Francis!

    I’m coming back to this double blog post 3 years later to refresh my good coding practices. And I just wanted to comment about your “Calculations: dealing with blanks” section. It so happens that a couple of weeks ago, while coding a survey, I came across a function that does exactly that. Maybe you’re already aware of it: it’s the coalesce() function, which I read about in the ODK documentation (definition here).


  7. I’m using Kobocollect and have a large set of about 100 names of districts that I want to use in a single select and multiple choice questions. I want to import these districts into the questions and use search assist for both the single select and the multiple choice of these many districts. How do I achieve this?

    • Dear Daniel,
      We would recommend you use an external CSV to integrate your list of districts, with choice filters in place to filter your list of districts if you can with other admin information. You can then use the “search” function as you mention. To do this you can check out the “Managing external data” tutorial available on “” MDC Toolkit.
      For your specific question you can also refer to the great resources on the topic on the XLSForms website :
      Hope this is useful to you!

  8. Hi, I have a variable called “Date of Birth, DOB” where i want to make select_one out of six (6) options (1. Primary, 2. Secondary, 3. Tertiary, 4. University, 5. No formal education but literate, & 6. No formal education but illiterate) in the proceeding variable of “HH Head’s level of education” BUT i want to make a condition such that if HH head age is less than (<10 years) then only options 1,5 & 6 appear in the proceeding variable. Please help

    • Hi Mark, thanks for getting in contact. This can best be done I think by including some age based calculations and a choice filter.
      Linked is an example I hope you can adapt for your specific case. It will also be necessary to include the “today” question type as meta_data in order to do the calculations. The first calculation is for the age and the second is included in order to filter the education level question.

      I hope this is useful and if you have any further questions in order to get it to work, just let us know,



  1. Advanced XLS forms coding – part 1 of 2 | CartoBLOG - […] Part 2 to follow in a week’s time! […]

Post a Reply

Your email address will not be published. Required fields are marked *

🚚 📦 We're moving!

[French below]

The content of the CartoBlog was transferred to the Information Management Resource Portal in May 2021:

Please note that the CartoBlog will be decommissioned in early 2022. From now on, we warmly encourage you to consult the tutorials, benchmarking studies and other resources directly onto the new portal.

Thank you!

🚚 📦 On déménage !

Le contenu du CartoBlog a été transféré sur l’IM Resource Portal en mai 2021 :

Veuillez noter que le CartoBlog sera désactivé au début de l’année 2022. Nous vous invitons, dès à présent, à prendre l’habitude de consulter les tutoriels, benchmarkings et autres ressources directement sur le nouveau portail.

Merci !