The autocomplete
tag creates an interactive menu of suggested text matches that updates as the respondent types into a text field.
Details
- The
autocomplete
tag is used with a text question widget and a "public" spreadsheet. - First, create the spreadsheet and add it to your survey.
- The sheet used for
autocomplete
must be a public sheet. - It must contain "id" and "text" columns.
- The "text" column contains only the values that are displayed to the respondent.
- The sheet used for
- Next, add the
autocomplete
tag to a text question. - By default,
autocomplete
will not display possible matches until at least three letters have been inputted.
Caution! When a sheet is "public," every cell of the sheet can be accessed by any user with a link to the survey. This includes columns of data that are never used in the autocomplete exercise. Never include sensitive information in public spreadsheets.
Match to text approach
The most basic usage of autocomplete
assumes a relatively small sheet (i.e., 1000 rows or less). The autocomplete
code reviews the data in the entire sheet and compares the respondent's text input to the values in the "text" column. With this approach, IDs can be in any format that is convenient for the programmer.
Match to id approach
When the spreadsheet is very large (i.e., thousands of rows), use the match to id approach to reduce the amount of data the autocomplete
code needs to review. This method pre-filters the results by comparing the respondent's text input to the values in the "id" column while still displaying the text from the "text" column to the respondent.
- To use this method, add the
autocomplete match id: y
tag to the autocomplete question. - The sheet should have IDs formatted to match what the respondent will type. Include spaces and punctuation as needed.
- To allow case-insensitive matching, all values in the "id" column should be lowercase.
- Lastly, include the tag
autocomplete lower: y
.
See an example using this method below.
Data storage and reports
In addition to storing the respondent's answer at the question, the autocomplete
tag also creates an "alternate" variable. Here is the difference between the two data points:
- Question variable (e.g., Q3): This variable stores the text from the "text" column if the respondent selects an item from the autocomplete menu. If no match is found, it stores the respondent's manual text entry instead.
- Alternate variable (e.g., Q3ALT): The alternate variable stores the value from the "id" column if the respondent selects an item from the autocomplete menu. If no option is chosen from the menu, Q3ALT will be blank.
Note: Even if numeric IDs are used in the "id" column, the data is stored as text.
Tags
The following tags can be used with the autocomplete
tag.
Tag | Description |
|
Specifies a different column in the sheet, other than "id" to store the value in the alternate variable. |
|
Accepts 'y' 'and 'n'' inputs; set to 'y' to reformat a user's text input into lowercase text. |
|
Accepts 'y' 'and 'n'' inputs; set to 'y' to match the text entered to the "id" column instead of the "text" column. |
|
Specifies the minimum number of characters needed to show potential matches from the sheet; defaults to 3. |
Additional examples
Specifying alternate variables
By using the tag autocomplete alt
, you can specify a different column to use for the alternate variable. In the image below, the "my_id" column has numeric IDs, and these numbers can be stored as closed-ended data, like a radio question, unlike the "id" column that will always store its data as text.
Simply apply the autocomplete alt
tag to the question and specify the column header containing the values you want to use for the alternate variable's data.
4. Which sauces do you like to eat with your foods?Download the fully formatted "groceries" sheet shown above here.
type: text
autocomplete: autocomplete.groceries
autocomplete alt: my_id
Using 'autocomplete match id'
To improve search time for larger sheets, use the autocomplete match id
method. It's also recommended to enter all text in the "id" column in lowercase and include the autocomplete lower: y
tag, allowing for both uppercase and lowercase text entries.
Download the fully formatted "cities" sheet here.
Note: If punctuation (e.g., hyphens) is used in the "text" column of a sheet, ensure the same punctuation is used in the "id" column. For example, in the cities sheet, "Knik-Fairview, Alaska" includes the hyphen in both the "text" and "id" columns.
Forcing a match
By default, questions with the autocomplete
tag will accept any text as a response. To require the response to match the options in the spreadsheet, use the validate
tag and fetch_cell
for a sheet lookup.
Using the same groceries sheet, when we select "Adobo" from the list, Q4 stores the text "Adobo," and Q4ALT stores the text from the "id" column, "adobo." The validation looks up the row "adobo," and retrieves the value "Adobo" from the "text" column. If we fill in the placeholders in the validation logic, we can confirm that Adobo equals Adobo, and we can proceed. If a respondent does not select an item from the list, the manually entered text would be stored at Q4, but Q4ALT would be blank. This would cause the validation to fail.
4. What is your favourite sauce?
type: text
autocomplete: groceries
validate: fetch_cell( name=> 'groceries', row => $Q4ALT, column => 'text') eq $Q4 { message: Choose an option from the list to continue.}
Using 'autocomplete' with conditions
To display subsets of a list, like brands by country or car models by brand, use the condition
decorator with the autocomplete
tag. Place the decorator immediately after the sheet name with the necessary logical expression to show the correct options to the respondent.
In the example below, the variable QCOUNTRY collects a value (1, 2, or 3) from the entry link when a respondent enters the survey. This value is then used in Q5, which, combined with the data in the autocomplete sheet, displays only the universities relevant to the selected country.
COUNTRY. Country
type: radio
translate: n
invisible: y
dvalue: if (getParm('c')>0) {getParm('c')} else {9999}
1. US
2. UK
3. Canada
9999. No country assigned
5. Which university do you attend?
instruct: Please start to type in the name of your university and a list of potential matches will appear for you to choose from.
type: text
autocomplete: surveyid.universitiesallcountries {if fetch_cell( name => 'surveyid.universitiesallcountries', row => [id], column => 'country' . $QCOUNTRY)==1}
autocomplete_match_id: y
ac_lower: y
dta: 97. Other
validation: anyChecked($Q5_ALT) or anyChecked($Q5_DTA,97) {message: Please select an item on the list or select "Other".}
The sheet has columns labeled "country1", "country2", and "country3", each containing a 0 or 1. A 1 indicates that a university should be displayed for that country. At Q5, we concatenate 'country'
with $QCOUNTRY
to dynamically reference these columns. If the referenced column has a value of 1 for a specific university, it will be shown.
Download the example "universitiesallcountries" sheet here to investigate further.
Accounting for accent marks or other diacritics
If you need to account for accent marks, cedillas, or other diacritics in your sheet entries, you can set up the autocomplete exercise to allow respondents to enter text with or without the diacritics. For example, with JavaScript included to handle specific diacritics often found in Spanish, typing "jalapeno" without the tilde on the 'n' will still match the sheet entry "Jalapeño Peppers".
Comments
0 comments
Please sign in to leave a comment.