Dynamically populate choice lists with CSV data
In the last blog post Pull CSV data into your forms, we showed you how to pull data from a previous survey into your new survey using CSV files. Another application of this feature is to pull select_one or select_multiple choice lists from CSV files.
See the example below and the explanation that follows:
XLSForm survey sheet
| type | name | label | appearance |
|---|---|---|---|
| select_multiple fruits | fruit | Which fruits do you like? | search(‘fruit_choices’) |
XLSForm choices sheet
| list name | name | label |
|---|---|---|
| fruits | fruit_name | fruit_label |
CSV file named ‘fruit_choices’ uploaded to the Media section of your survey
| fruit_name | fruit_label |
|---|---|
| apple | Apple |
| mango | Mango |
| pineapple | Pineapple |
| watermelon | Watermelon |
You can take a look at this XLSForm here and the example CSV file here. If you download these files to try on your own, make sure you download them in Excel and CSV formats, respectively.
The syntax on the survey worksheet is the same as a normal select_one or select_multiple field. You still need to include a list name on the survey worksheet that matches the list name on the choices worksheet.
The only two differences are the search expression in the appearance column on the survey worksheet and the name and label fields on the choices worksheet:
- In the appearance column on the survey worksheet, you need to include a search expression that will locate and pull the CSV data that matches the search terms. In the example above, the search expression search(‘fruit_choices’) will search the entire CSV file ‘fruit_choices’ and include all rows specified in the fruit_name and fruit_label columns. If you want to include only certain rows in the CSV file and not all of them, use a multiple parameter search expression. See the next section for information on multiple parameter search expressions. Note that you can combine different appearance attributes by putting a space between them, e.g., quick search(), combines the quick and search appearance attributes.
- On the choices worksheet, the name and label columns should point to the names of the columns in the CSV file where the answer choice values and labels are located, respectively.
When filling out the survey, the form will automatically search the specified CSV file and load the answer choices for the question without the user noticing any difference!
As mentioned in the previous blog post, the pre-load CSV data feature only works with ODK Collect right now.
Multiple parameter search expressions
The example above used a single parameter search expression. All rows in the CSV file were included in the answer choice list. To be more exclusive with your search, you can use multiple parameter search expressions. These include ‘contains’, ‘startswith’, ‘endswith’, and ‘matches’ search parameters. For detailed instructions on how to use these search parameters, see the ODK post: http://opendatakit.org/help/form-design/data-preloading/.
If you have any questions about dynamically populating choice lists, or if you get stuck somewhere, shoot us an email at support@ona.io, and we’ll sort it out!