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:

type	name	label	appearance
select_one region	hhidregion	Please select the household's region.	search('hhplotdetails')

list_name	value	label
region	regionid	region

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:

type	name	label	appearance
select_one district	hhiddistrict	Please select the household's district.	search('hhplotdetails', 'matches', 'regionid', ${hhidregion})

list_name	value	label
district	districtid	district

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:

type	name	label	appearance
select_one village	hhidvillage	Please select the household's village.	search('hhplotdetails', 'matches', 'districtid', ${hhiddistrict})

list_name	value	label
village	villageid	village

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:

type	name	label	appearance
select_one hhid	selectedhhid	Please select the household.	search('hhplotdetails', 'matches', 'villageid', ${hhidvillage})

list_name	value	label
hhid	hhid_key	household

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:

type	name	label	appearance
begin group	searchopt	HH search options	field-list
select_one searchoption	searchtype	Search type:	minimal
text	searchtext	Search text:
end group	searchopt

list_name	value	label
searchoption	contains	Contains
searchoption	matches	Matches
searchoption	startswith	Starts with
searchoption	endswith	Ends 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:

type	name	label	appearance
select_one hhid	foundhhid	Choose a matching household:	search('hhplotdetails', ${searchtype}, 'household', ${searchtext}, 'regionid', ${hhidregion})

list_name	value	label
hhid	hhid_key	household

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:

type	name	calculation
calculate	hhid	if(${hhidentrychoice}=1, ${selectedhhid}, ${foundhhid})

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

Previous Next