Loading multiple-choice options from pre-loaded data
Once your form has pre-loaded data attached, you can dynamically pull the choice lists for select_one and select_multiple fields from that data.
If your goal is to allow the user of your survey form to select the appropriate row for which you would like to pre-load data, your best bet is to start a new form with + and then Start new form, and to have the pre-loading code automatically added to your new form (as described in the topic on pre-loading). You will be able to specify that the user should choose the data to pre-load in one, two, or even three steps (e.g., choosing district, then village, then household), and you will be able to select which pre-loaded data columns to use as the choice labels for each step.
Click here to watch a video on pre-loading choice lists.In the form designer, you can check Dynamically load choices from pre-loaded data in the appearance of any select_one or select_multiple field. You can then use a wizard to choose which items (rows) to include from which attached dataset or .csv file. Finally, in the actual choice list, put just one choice that has the appropriate column names in the label, value, and (optionally) image fields; all matching rows will be loaded as choices, and the choice labels, values, and (optionally) image filenames will come from the columns you specify there in the choice list. (For example, I might put "name" as the label for my one choice and "id_key" as the value, if I wanted to list the names in the "name" column and record user selections using the ID numbers in the "id_key" column.)
In the spreadsheet form definition, multiple-choice fields with dynamic choice lists follow the same overall syntax as regular, static select_one and select_multiple fields: specify "select_one listname" or "select_multiple listname" in the type column of your survey sheet (where "listname" is the name of your choice list; e.g., select_one country), specify any special appearance styles in the appearance column, and include one or more rows for your listname on the choices worksheet. When the choice list should be pulled from pre-loaded data, there are just three important differences in the syntax to be aware of:
In the appearance column of the survey worksheet, you should include a search() expression that specifies which rows of pre-loaded data to include in the choice list. If you want to specify another appearance style, that should be entered into the appearance column first, followed by a space, followed by the search() expression (as in "quick search()"). Syntax for the search() expression itself is included below.
On the choices worksheet, a row should indicate which pre-loaded data columns to use for the label and selected value. In the list_name column, specify the name of your choice list as you normally would. In the value column, however, include the name of the column to use for uniquely identifying selected choices (this is normally an ID column of some sort). In the label column, include the name of the column to use for labeling the choices; if you wish to include multiple columns in the labels, just include a comma-separated list of all columns to include. At survey time, the value column will be dynamically populated based on the column name you put there, and the label column will be dynamically populated based on the column name(s) you put there.
Also on the choices worksheet, you can include a column name in the image column. If you do, the image filename to use will be pulled from the specified column. If you refer to image files in this way, you must always upload those image files as media file attachments when you upload your form to the SurveyCTO server (as you normally would).
For the search() expression itself, there are a series of options to indicate which rows of pre-loaded data to include in the choice list:
"search(source)": This single-parameter search expression includes all distinct rows as choices; you just have to specify the data source, which is either the name of the attached .csv file or the unique ID of the attached dataset (e.g., "search('hhplotdata')" if either the hhplotdata.csv file was attached or a dataset with the ID hhplotdata was attached). All rows in the specified dataset or .csv file will be considered as choices, but only distinct rows – those with unique selection values – will be listed for the user. In other words, duplicates will be automatically filtered from the list shown to users.
"search(source, 'contains', columnsToSearch, searchText)": This search expression includes all distinct rows that contain the specified text in the specified column(s) (e.g., "search('hhplotdata', 'contains', 'respondentname', ${nametofind})"). The third parameter specifies either a single column name to search, or a comma-separated list of column names to search. Rows with matches in any specified column will be included.
"search(source, 'startswith', columnsToSearch, searchText)": This search expression includes all distinct rows that start with the specified text in the specified column(s) (e.g., "search('hhplotdata', 'startswith', 'respondentname', ${nameprefix})"). The third parameter specifies either a single column name to search, or a comma-separated list of column names to search. Rows with matches in any specified column will be included.
"search(source, 'endswith', columnsToSearch, searchText)": This search expression includes all distinct rows that end with the specified text in the specified column(s) (e.g., "search('hhplotdata', 'endswith', 'respondentname', ${namesuffix})"). The third parameter specifies either a single column name to search, or a comma-separated list of column names to search. Rows with matches in any specified column will be included.
"search(source, 'matches', columnsToSearch, searchText)": This search expression includes all distinct rows that exactly contain the specified text in the specified column(s) (e.g., "search('hhplotdata', 'matches', 'respondentname', ${nametofind})"). The third parameter specifies either a single column name to search, or a comma-separated list of column names to search. Rows with exact matches in any specified column will be included.
"search(source, searchType, columnsToSearch, searchText, columnToFilter, filterText)": Finally, any of the four search types above can be further filtered to only include a subset of pre-loaded data. Simply add two extra parameters to any of the search types above, with the first extra parameter being the column name to filter and the second extra parameter being the exact value to filter. For whichever search is specified in the first four parameters, only rows exactly containing the sixth parameter value in the column named by the fifth parameter will be included (e.g., "search('hhplotdata', 'contains', 'respondentname', ${nametofind}, 'villageid', ${villageid})" to list all matching names within a particular village).
If you're editing your spreadsheet form definition directly, you can use the calculation-builder to construct your own search() expression rather than write it out by hand. The builder is available as a tool in the Your forms and datasets section of the Design tab. Once you run the builder, choose Pull pre-loaded data into a multiple-choice prompt as the calculation type, then enter your details and click Build. You can just copy and paste the resulting expression into your form's appearance column.
For a working example, see Pre-loading: Searching and selecting from pre-loaded data.
Two additional notes on usage:
Choices will be ordered, by default, in the order that they appear in your dataset or .csv file. If you want to specify a different ordering, include a numeric column in your dataset or .csv file named sortby; if present, choices will be ordered numerically, according to this sortby column.
You can include one or more static choice options, in addition to the dynamic ones loaded from your pre-loaded data. Simply include static choices as you normally would, on the choices worksheet. These can appear before and/or after the row that indicates the columns to use for your dynamic choices. The only restriction is that the values you specify for your static choices in the value column must be numeric.
