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:
I will assume that you are already familiar with XLSForm and Survey123 Connect.
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:
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:
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.
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.
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:
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!
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
Using the CSV lists in the form design
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.
You can next publish your survey and try it in the Survey123 field app or in a web browser.
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
Linking the CSV items to your survey
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.