Survey123 Tricks of the Trade: External choice lists
In this blog, you will learn about external choice lists. External choice lists allow you to work with lists stored outside of your XLSForm document, as CSV files.
When used appropriately, external choice lists can help you:
- Boost the performance of very large lists in the Survey123 field app
- Reuse a single list across multiple surveys
- Streamline updates to lists
I will assume that you are already familiar with XLSForm and Survey123 Connect.
When to use external choice lists?
Large lists: If your survey includes lists with more than a few thousand choices, it is generally a good idea to use external choice lists. They will give you a performance boost both in Survey123 Connect during the design process, as well as when your form is loaded in the Survey123 field app.
When you add very large lists in the choices worksheet of your XLSForm, you will note that the form validation process in Survey123 Connect slows down. This is because the content of the choices worksheet needs to be processed and uploaded into ArcGIS before it can be validated.
By moving your lists away from the choices worksheet into an external CSV file, the XLSForm validation process accelerates. On an average Windows machine, it takes Survey123 Connect about 3 seconds to process a list with 10,000 choices. If you move that list into a CSV file, it takes just over half a second. A list with 100,000 records in the choices worksheet can take more than 1 minute to process. The same list stored as a CSV file will process in less than 1 second.
The performance boost is also noticeable when you work with big lists in the Survey123 field app. Nobody likes to wait for a form to open! Specifically, the very first time a survey is loaded into the Survey123 field app, indexes are built to optimize the form. Indexes on very large lists can be built much more quickly from a CSV file than from the choices worksheet. Indexes are only built once, when the form is opened for the first time on the device, but the time difference is substantial.
If you use an external choice list in CSV format, the Survey123 field app will index it in less than a second even if you have many tens of thousands of records.
I generally use external choice lists when:
- A list contains more than 500 choices. In this case, I put the list into its own CSV file.
- When the combined number of choices across all the lists I have in the choices worksheet is larger than 500. In this case, I take the larger lists I have away from the XLSForm file and save them as CSV files.
Shared lists and streamlined list updates: As you design more and more surveys, you will see yourself having to reuse lists across multiple XLSForms. If items in the list change, updating the XLSForm designs across all of your surveys can be time consuming and error prone. Not to mention that for your changes to take effect, you will also need to re-publish every survey.
External choice lists bring clear advantages in this case: Replacing a CSV file is much easier than going into an XLSForm file an updating the contents of a worksheet. Even better, it is possible to have your survey reference a CSV file stored in ArcGIS! This means that many surveys can point to a single master list. Whenever you change that list, all the surveys will reflect the change. As I will describe later, this is done through content linking.
Should you then always use external choice lists? Not necessarily. I bet all your Survey123 forms include a 'yes/no' list. That is not a good candidate for a master list in CSV format. For lists that do not change and have few options, the choices worksheet within your XLSForm is the best place.
If you have a list that changes somewhat frequently, say once a month, or once a year, then using a linked external choice list is probably a good idea. This applies to surveys for the Survey123 field app as well as for web surveys. CSV items in ArcGIS can be updated manually, and programmatically.
Check out this Python notebook that @ZacharySutherby put together to programmatically update CSV files in your ArcGIS organization using the ArcGIS API for Python.
If you need to deal with a list that changes very frequently, such a daily or hourly, you may want to look at other alternatives such as dynamic lists. Dynamic lists are populated by querying an ArcGIS layer. Brett discusses their use in his Dynamic Choice Lists using Search Appearance blog post.
How to create an external choice list
External choice lists are stored as CSV files. Unlike the choices worksheet in your XLSForm, which can contain multiple lists in a single worksheet, a CSV file can only contain one list. The rules to create the CSV file are:
- The filename cannot contain spaces. The name of the file, becomes the name of your list.
- You must include at least two columns: name and label (just like in the choices worksheet):
- The name column includes the choice codes. No special characters, spaces or duplicates allowed.
- The label will be used to present the choice to the user.
In the screenshot below you can see a CSV file listing all Angiosperm families common in Brazil.
External choice lists can support other standard XLSForm columns such as language, but not the media and image columns (as of May 2022). The rules to use them are the same as for the choices worksheet in the XLSForm.
If you want to use choice filters, you can also add extra columns, just like you would if you were working in the choices worksheet of your XLSForm. In the example below, the CSV includes a list of common plant species in Brazil, and their corresponding family. The family column has been added to support choice filters in the Survey123 form.
As a general rule, you want to keep information in the CSV file to a minimum. If you do not need a column to support your survey, drop it from the CSV file.
One last note: If you use Microsoft Excel to put together your CSV file, choose the CSV UTF-8 option when saving. This will avoid issues with special characters included in the labels of your choices.
How to use external choice lists in Survey123 Connect
To work with external choice lists in Survey123 Connect, copy them into your survey's media folder. If you click on the Files button in Survey123 Connect, it will take you there with a couple of clicks.
You can store one or more CSV files within the media folder. Once you have the lists in the media folder you can reference them from your XLSForm design.
XLSForm basics
External choice lists are used with the select_one_from_file and select_mutliple_from_file question types. They are similar to a select_one and select_multiple, but:
- The Survey123 Standard and Advanced design templates do not include these question types. When you expand the list of available question types, you will not see them. You need to type it manually in the type column.
- To indicate the list name, type the name of the CSV file including .csv at the end. Make sure the file is in your survey media folder and match the filename exactly. It is case sensitive!
Important: External choices are commonly used for large lists. If that is the case, you should consider using either the autocomplete or minimal appearances. If you don't Survey123 will attempt to render all your choices in the form. If you have many thousands... it will not be pretty!
If you ever see Connect display a list like below, unable to display the proper labels, make sure your CSV file includes a column called label. It is common to get CSV lists from other systems lacking a label column. Always remember to rename one of the columns as the label!
A step by step guide
Creating the CSV files
I downloaded a database of Brazilian plants from FigShare. Out of this database, I created a couple of CSVs, which you can find as attached to this article. One CSV file for all the Angiosperm families, and a separate one for all the plant species. The original database looks like this:
Note that the first column (ID) has unique values. There is one unique ID for every plant species. This makes it a perfect candidate to become the name column in the Survey123 external choice list. For the label, I simply concatenated the Especie (species) and Genero (genus) columns.
Since I was interested only on species, I used the Microsoft Excel remove duplicate function to eliminate all plant subspecies and varieties. After having the name and label columns with respectively, the plant IDs and their scientific name, I removed all other columns to keep my CSV file as lightweight as possible.
I removed all except the family column, which I wanted to use to build a choice filter. The Brazil_Species CSV file ended up with over 76,000 unique choices.
To create the Brazil_Families CSV file I copied all the families into a new worksheet and then used the remove duplicate option to generate my list. I used the plant family values for both the name and label columns.
As you have seen, I was not able to use the original database right away. I had to massage the data to comply with the strict requirements I described above in the How to create an external choice list section.
The original size of the database is over 20Mb. The size of two CSV files I created is around 3Mb. Again, you can find both files attached to this article.
Copying the CSV files into the survey's media folder
- Open Survey123 Connect and sign in with your ArcGIS account
- Create a new survey using the Advanced template
- While in the design preview, click on the Files button from the left bar
- Navigate to the media folder and paste the Brazil_Species.csv and Brazil_Families.csv files
Using the CSV lists in the form design
- Add a new row into the XLSForm
- Set the type to select_one_from_file Brazil_Families.csv
- Set the name to family
- Set the label to Plant Family
- Set the appearance to autocomplete or minimal
- Save the XLSForm design and let Survey123 Connect validate and display your form
The list of plant families has barely over 200 choices. It is a very small list, but you got it working! The plant species list has more than 70,000 choices.
- Add a new row into the XLSForm
- Set the type to select_one_from_file Brazil_Species.csv
- Set the name to species
- Set the label to Plant Species
- Set the appearance to autocomplete
- Set the choice_filter column to family=${family}
- Save the XLSForm design and let Survey123 Connect validate and display your form
- Note that the list of species will be filtered based on the selected family
You can next publish your survey and try it in the Survey123 field app or in a web browser.
- Publish your survey
- Use the survey from the Survey123 field or web apps
Next, we are going to upload the CSV files into ArcGIS and link them to the survey. In this way, our CSV files could be reused again and again across many surveys. And if we were to add new plant species, we would just need to update the master CSV items in ArcGIS!
Uploading CSV files to ArcGIS
- In Survey123 Connect, click on the Files button and copy the path to the survey's media folder. This will help us later when we need to browse to the files to upload them into ArcGIS
- Using a web browser, sign in to ArcGIS
- Navigate to the Content tab
- Click on the New Item button as shown in the next screenshot
- Select the Brazil_Species file and upload it. Remember that you should have the path to its directory already in your clipboard.
- In the New item dialog, make sure you don't create a hosted feature layer with the data in the CSV. We just want to upload the file
- Set the title, tags and other properties and Save.
- Share the new CSV item with the same groups you plan to share your survey with
- Repeat the process to also upload the Brazil_Families.csv file
Linking the CSV items to your survey
- In Survey123 Connect, click on the Linked Content tab
- Click on the + Link Content button
- Select the Brazil_Species item and click OK
- Repeat to link the Brazil_Families item
Content linking does NOT require you to publish the form again. Once content has been linked, the Survey123 field and web apps will automatically check if the local copy of the content in the device needs to be refreshed or not. The check happens when the survey details page (the page where you see the Collect button) opens. Please note that no check is done if you bypass the survey details page. For example, no check will happen if you open the survey via app linking.
Programmatically updating CSV file items
CSV file items can be updated programmatically using a Python notebook. Check this sample written by Zach: https://github.com/Esri/Survey123-tools/tree/main/Update_CSV_Item