Previous Next

To open or use this sample form, go to the Design tab, scroll down to the Your forms and datasets section, and click + then Start new form; then enable Use a sample form as your starting point and choose Pre-loading: Searching and selecting from pre-loaded data from the list. You can also click here to download the spreadsheet form definition (this is a .zip file that contains a .xlsx sample form as well as a .csv file containing sample data for pre-loading).

If you upload the spreadsheet form definition to your SurveyCTO server manually, be sure to attach the .csv file – or upload the .csv file as a dataset named hhplotdetails and then attach that dataset – otherwise the form won't be able to find the data when users are filling it out.

This sample is precisely the same as the previous pre-loading sample form, except that instead of prompting for the user to double-enter the household ID, this sample allows the user to select among households in the .csv file. The new hhidentrychoice field allows the user to choose how to proceed:

Regardless of the user's choice, the next question will ask the user to choose a region:

The list of available regions comes from the .csv file. This is because the select_one field in the spreadsheet form definition calls the search() function in its appearance column and the choices sheet specifies the .csv columns to use for the choice labels and values:

select_one regionhhidregionPlease select the household's'hhplotdetails')


The "search('hhplotdetails')" appearance indicates that the choice options should include all unique values in the hhplotdetails.csv file. The value and label columns of the choices sheet indicate the .csv columns to use for the choice values and labels (regionid and region respectively).

If the user chose "Multiple-choice selections" at the first prompt, then the region choice will be followed by a choice of available districts within that region:

Here, the list of districts will be filtered to only include those districts within the selected region. This is accomplished with additional parameters to the search() appearance:

select_one districthhiddistrictPlease select the household's'hhplotdetails', 'matches', 'regionid', ${hhidregion})


Next, the district choice will be followed by a choice of available villages within that district:

This is again accomplished via an appropriate search() appearance and appropriate column names in the choices sheet:

select_one villagehhidvillagePlease select the household's'hhplotdetails', 'matches', 'districtid', ${hhiddistrict})


Finally, the village choice will be followed by a choice of available households within that village:

This is yet again accomplished via an appropriate search() appearance and appropriate column names in the choices sheet:

select_one hhidselectedhhidPlease select the'hhplotdetails', 'matches', 'villageid', ${hhidvillage})


If the user had instead chosen "Search region for household" at the first prompt, then the region choice would be followed by a choice of options for searching within the chosen region:

The workbook side of this looks like this:

begin groupsearchoptHH search optionsfield-list
select_one searchoptionsearchtypeSearch type:minimal
textsearchtextSearch text: 
end groupsearchopt  

searchoptionstartswithStarts with
searchoptionendswithEnds with

This includes the "field-list" appearance on the begin group row, in order to show multiple fields on a single screen; the searchtype field to allow the user to choose among the search options supported by the search() function ("contains", "matches", and so on); and the searchtext field to allow the user to enter the search text itself.

Then on the following page of the survey, the user will be able to select among all households identified in their search:

This is accomplished with particular parameters to the search() function:

select_one hhidfoundhhidChoose a matching household:search('hhplotdetails', ${searchtype}, 'household', ${searchtext}, 'regionid', ${hhidregion})


Here, the search() function specifies the search type, the column to search, the value for which to search, the column by which to filter, and the text by which to filter. The hhid row on the choices sheet specifies which columns from the .csv to use for choice values and labels (hhid_key and household respectively).

Because every row of the .csv file has a similar description in the household column (like "Household 1009 (village 2)"), certain searches will find no households (e.g., contains "z") and other searches will locate all 60 (e.g., starts with "H").

Note: given that there are two different methods of selecting the household (either multiple-choice selections or search), a different variable is used for each option's results: (1) selectedhhid, which is the household result when the the multiple-choice option is chosen, and (2) foundhhid, which is the household result when the search option is chosen. It is easier for later fields in the form to be able to reference just a single hhid field. Therefore, a calculate field is used to record the appropriate household ID for use later in the form:

calculatehhidif(${hhidentrychoice}=1, ${selectedhhid}, ${foundhhid})

The if() function is used to store the appropriate value into the hhid field, depending on the case.

Previous Next