Pages Menu
Categories Menu

Posted by on Dec 17, 2019 in Information Management, Mobile Technologies | 0 comments

Advanced XLS forms coding n°4

Advanced XLS forms coding n°4

This post is a follow-up on 3 previous posts Advanced XLS forms coding: part 1 of 2, Advanced XLS forms coding : part 2 of 2 and Advanced XLS forms coding n°3.

Times flies while ODK is growing and new features appear… which means new toys for coding! In this blog post, we are going to focus on 3 different ways of integrating external data.

Using an external file as an attachment to your survey can be very useful when you work with a big dataset or you would like to link your survey to another database as it can both improve the performance and make it possible to update a list of options without having to upload a new version of the form. To manage external data you have several options: here is a recap of when to use each of them.

So what are we going to focus on here? In a tutorial of the MDC Toolkit, we already detailed how the pulldata() function works with an external CSV. Unfortunately, as shown in the table above, using such a function isn’t always possible depending on what you wish to do. In this blog post, we are therefore going to look at the two other ways: the use of the XML file and of the search() function.

See “Managing external data” on the MDC toolkit website:

Note: The use of these functions can be effective in several XLSForm fields: choice_filter, calculation, relevant, choices, etc. It will depend on the purpose of your use of external data.

1- Work with more advanced criteria than what the pulldata () function offers

Using the pulldata() function has several limitations.  It can only pull data from one column of your csv file and if there are multiple rows which match your pulldata() criteria, it will pull only the first one.  The search function allows for greater flexibility in filtering the csv data and can also return multiple matching values which is useful for example when populating a dynamic choice list for a select_one or select_multiple question.

The first way of using it is rather simple: for instance, you can search (‘Jean’) and it will extract all data from the list called “Jean”.

You can also set it up in a more advanced way. For that, this function needs to be combined with options such as:

  • startswith
  • endswith
  • contains
  • matches

This will allow you have all rows with match in any specified column to be included (in the search).

For example: search(‘externalCSV’,’startswith’,’location’,${city})

  • the first parameter (“externalCSV”) is the name of your csv file
  • the second parameter is the function you want to use to search it: here the function is going to look at the beginning of the words
  • the third parameter specifies the filter on which the search function is based  (often the name of the column where you want values to be taken from)
  • the fourth parameter is the name of the variable to which the filter needs to correspond

/!\ The search() function does not work with all the tools from the ODK world. It is in fact perfectly compatible with tools such as ONA, on both the Collect side but also the Enketo online capture interface. However, for KoboToolbox users, this function works on the Collect side but the version of Enketo implemented within KoboToolbox is not updated and therefore does not work for this function (on the day of writing of this blog post i.e. 26/11/2019). Therefore depending on the tools you plan to use make sure that you test it thoroughly to avoid any unwelcome surprises

/!\ The search() function will only work if the list is in an external csv.  It cannot be used to filter data from the “choices” tab or from an external xml file (see “work with an XML file below” section). 

See an example in the following XLSForm and external CSV: 

Further information on the search() function can be found here:

2- Without using a csv: work with an XML file

This second function is dedicated to advanced users. It’s possible to use XML files as an alternative to CSV files to store external data which can be referred to in calculations, constraints or relevancies.  This method allows the form creator to deal with yet more complex cases than those seen with the two previous functions by using the instance function. It makes it possible to have more advanced filter criteria than the search() function presented above and also to work with metadata and really complex nodes (interdependency between several variables, existence of grouped questions inside a form, etc.) in databases. You have several possibilities:

  • have a select_one or select_multiple question where your XML file contains the list of choices. You need to specify the question type: “xml-external” and the function instance will be in the “choices” sheet
  • include data from your XML File in other formulas: calculation, constraint, relevant or even choice_filter.

To code this function properly please follow the instruction here:

For example:

What the formula means:

  • your XML file is called “site_list”
  • data is going to be taken in the “provincename” column
  • it will only display data from this column when the values match specific values of the column “name”, the specific value is the answer to the question “site” in the XLSForm
  • /root/item/ is the path in the XML file to get data

Keep posted for further blog posts on Advanced XLSForms coding!

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 !