autocomplete (r8) – IntelliSurvey

The autocomplete tag is used to create an interactive menu of suggested text matches, which updates as the respondent types into a text field. To use, append this tag to a text question and reference a "public" spreadsheet.

Autocomplete requirements

The autocomplete tag needs a text question widget and a "public" spreadsheet. To parse a survey with the autocomplete tag, the target spreadsheet must first be created.

Creating the spreadsheet

Sheets used for autocomplete must:

be "public", and
contain 'id' and 'text' columns.

Making a sheet public

To make a sheet public, cell A2 must contain the following text: ##public

This is column 1, row 2 of a sheet, between the header row and first record row. If done correctly, the Browse grid in the Spreadsheets applet will display "yes" in the Public column.

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., up to about 1000 records).

A sheet using this approach can have IDs in whatever format is convenient to the Survey Programmer (SP). The 'text' column contains the only values that are used in the interaction with the respondent.

The autocomplete code compares the respondent text to the values in the 'text' column.
The resulting menu of suggested responses displays values taken from the 'text' column.

Match to 'id' approach

When the sheet to be used is very large (i.e., thousands of records), use the match to 'id' approach. To use this method, add the following tag to the autocomplete question: autocomplete_match_id: y.

A sheet using this approach should have IDs formatted to match what the respondent will type. The 'text' column still contains the values displayed to the respondent.

The reason this approach is recommended for larger sheets is that it pre-filters the results used in the respondent's browser-based JavaScript. Whereas the match to 'text' approach considers the entire collection of spreadsheet rows while the respondent is typing, this match to 'id' provides a much smaller collection of data for the browser to consider. This allows for a faster user experience even with very large data sets.

The autocomplete code compares the respondent text to the values in the 'id' column.
- To allow case-insensitive matching, all values in the 'id' column should be lowercase.
- Also include the tag ac_lower: y.
The resulting menu of suggestions still displays values taken from the 'text' column.

Include all spaces and punctuation in the 'id' column to ensure the proper results. For example, in the above example featuring cities, Las Vegas should have the ID of "las vegas, nevada". If it were stored as "lasvegasnevada", typing in "Las Vegas" would not return a match.

Syntax

# For smaller, match to text sheets:
X. Text for question requesting input selection from a sheet.
type: text
autocomplete: surveyid.sheet_name


# For larger, match to id sheets:
Y. Text for question requesting input selection from a sheet.
type: text
autocomplete: surveyid.sheet_name
autocomplete_match_id: y
autocomplete_lower: y

In the example QY above, the autocomplete match id tag will increase performance and decrease the load by filtering the 'id' column within a sheet, instead of the 'text' column.

See the Tags table (next) and the example below for more information.

User experience

As respondents type into a text field that uses autocomplete, the 'text' column of the associated sheet is dynamically filtered, continually updating the menu of results to show only options that match the text string entered.

For example, when "University of" is typed into a question using the below sheet as the reference, the six options containing the text "University of" are displayed in the menu that appears below the widget.

The respondent is able to interact with the text input in two ways. They may

choose a match from the menu, which fills the text input with the text value from the sheet, or
type something else that may not exactly or remotely match the values from the sheet.

If the intent of SP is to force a match, additional validation logic is recommended. See Forcing a Matching Response below, which utilizes the reporting outputs described in the next section.

Reporting output

The autocomplete tag creates two fields in the data:

The original question (e.g., Q3). This stores the text provided by the respondent, matching the value from the 'text' column if the respondent chooses an item from the autocomplete menu.
An "alternate" variable (e.g., Q3ALT). By default, this variable stores the value from the 'id' column.

In the example above, suppose the respondent clicks to choose the first result for Q3, "The University of Texas Medical Branch," and continues the survey. The two stored values for this exercise will be:

Q3: The University of Texas Medical Branch
Q3ALT: 102298 .

However, if the respondent does not choose an option from the menu, note that no value will be stored to Q3ALT.

Q3: My university is not shown
Q3ALT: no data .

Tip! Both Q3 and Q3ALT are open-ended text widgets, meaning their respondent data is saved as a text string. They are not closed-ended variables (e.g., radio).

Saving a value from another column

By using the tag autocomplete_alt, SPs can specify a different column to use for the alternate variable.

This is useful, for example, when the 'id' column needs to be text but the survey logic would benefit from a numeric ID.

Example:

Consider this sheet with 'myid' column:

The SP may wish to store the 'myid' numeric value for calculation later in the survey:

5. Enter the nearest city:
type: text
autocomplete: uscities
ac_match_id: y
ac_lower: y
ac_alt: myid

CITY_CL. City classification
type: radio
selectby: condition
1. Classification 1 { if $Q5ALT > 0   and $Q5ALT <= 100 }
2. Classification 2 { if $Q5ALT > 100 and $Q5ALT <= 200 }

Forcing a matching response/saving other sheet data

By default, text questions with the autocomplete tag will accept any value as a response. When an exercise requires responses that match the spreadsheet and disallows further edits by the respondent, additional validation logic can help.

In the example below, the main question is Q9. Only when the respondent selects an option from the menu (via mouse click, screen touch, or keyboard selection) will the Q9ALT variable be updated with the 'id' value. However, since a respondent could feasibly try to only type in a response (e.g., because "bear" is very short), it is possible that the necessary trigger to store the 'id' value could be missed. To mitigate this potential issue, the SP has added instructions of guidance.

Instructions help, but to truly enforce the rule, the validate tag uses fetch_cell for a sheet lookup using Q9ALT.

If Q9ALT doesn't have a value, the validation will be triggered. This would happen if the respondent does not select an option from the menu.
If Q9ALT does have a value, looking it up using fetch_cell will return the text from the 'text' column of the sheet. This text must match the text that is present in the Q9 text input. If it does not match, the respondent may have edited the resulting text. For example, the respondent might have selected "Bear" but then added " cub" to modify their selection. The validation prevents further movement in the survey (see warn to allow the modified text after a single validation warning).

Also in this example, the SP needs to associate the Q9 answer with a numeric ID stored in the 'myid' column. Again, fetch_cell provides the needed data by performing a lookup with the Q9ALT row ID.

Including a validation like the one shown below provides an enforceable way to ensure that Q9 and Q9ALT match each other. The data collected in Q9 will be similar in consistency to a closed-end variable, aligning perfectly with the available options in the spreadsheet.

9. What is your favorite animal?
type: text
instructions: Type an animal and click a suggestion from the menu that appears.
ac: animalsheet
ac_match_id: y
ac_lower: y

9NUM. The numeric 'myid' value for Q9
type: text
invisible: y
cvalue: fetch_cell( name=> 'animalsheet', row => $Q9ALT, column => 'myid')

validate: fetch_cell( name=> 'animalsheet', row => $Q9ALT, column => 'text') eq $Q9
message: Please be sure to (1) make a selection from the menu, and (2) refrain from further editing the text.

Examples

For the following examples, we will be using the sheets "colleges" for the questions pertaining to colleges, "cities" for the questions on largest cities visited, and "groceries" for the accentMap example. It may be helpful to download and open these sheets on your computer for reference. To download them, please click the links below.

Basic

In this example, a simple lookup based on the "colleges" sheet (shared above) is used. By default, autocomplete will not display possible matches until at least 3 letters have been input.

Notice how the autocompleting function operates when we set a higher minimum number of characters to use before matching the user's input to the sheet options. For comparison, the abbreviated versions of autocomplete and autocomplete_min — ac and ac_min, respectively — have been used here instead.

Specifying alternate variables

Applying the autocomplete tag to a question QX will automatically create a secondary/alternate variable, QXALT; for a given question, the matching 'text' column response will be stored for QX, whereas QXALT will store the 'id' column value. When the autocomplete_alt (or ac_alt) tag is applied, users can specify a different column from which to match the value stored in QXALT.

In the example below, Q3 uses the default setting. Q4 will store the city name that matches the value in the 'text' column, while Q4ALT will be modified by ac_alt, storing the value from the 'ranking' column (the city's rank in the top 10 city sizes for that state) instead of its ID. In this case, the results from matching the respondent's input to the spreadsheet are displayed on the next page of the survey.

Download the "cities" sheet for reference here.

Searching large lists

For larger sheets, the search time can become inefficient when using autocomplete to match a respondent's input to the 'text' column of the spreadsheet. To increase the system's search time, use the autocomplete_match_id (or ac_match_id) tag.

Since the sheet will be searching via the 'id' column and the 'id' column will be formatted with lowercase text, in order to provide flexibility to respondents' inputs, use the autocomplete_lower tag. This tag will automatically convert the respondent's text entry to lowercase, allowing for a flexible match between the formatted sheet and entered text. In the example below, Q5 and Q6 are driven by the same sheet. Q5 does not use ac lower: y whereas Q6 does. Notice how the lookup on Q5 does not permit text entries with uppercase letters to match to the sheet (e.g., "Pittsburgh" won't match on Q5, but it will on Q6; instead "pittsburgh" must be entered to match on Q5).

Additionally, the next page will share the QX and QXALT values for Q5 and Q6. Compare the results of these fields to the example above using default tags (Q3). While the filtering occurs on the 'id' column, the data is actually stored in the same way (QX stores the 'text' value, and QXALT stores the 'id' value, unless ac_alt is used to change the value of QXALT).

Download the "cities" sheet here.

Note that cities that include punctuation in their names (in the 'text' column) such as "Knik-Fairview, Alaska" do also include the hyphens in the 'id' column as well, so the hyphen must be input if the ac_min value is greater than 4 places.

In a table

In this example, the sheet "colleges" is used with table T8 for users to match their responses. Since autocomplete_min is set to '0', users may press the down arrow to immediately activate the pulldown. Keep in mind that setting an autocomplete_min too low may bog down the system until enough values have been input.

Download the "colleges" sheet here.

Accounting for accent marks or other diacritics

In this example, JavaScript is included that allows respondent input without diacritics to match with sheet entries that do have diacritics. If you type "jalapeno" without the tilde on the 'n', the sheet entry "Jalapeño Peppers" is still matched.

Download the "groceries" sheet here.

Related to

Tag	Abbreviated	Description
`autocomplete_alt`	`ac_alt`	Allows the user to specify a column from the spreadsheet that will be used to create an alternate/additional variable, QXALT, that will store the input from the that column. The default value is the 'id' column.
`autocomplete_lower`	`ac_lower`	Accepts 'y' (yes) and 'n' (no) inputs. Reformats a user's text input into lowercase text only (spaces and punctuation are still required if part of the text). Defaults to 'n'.
`autocomplete_match_id`	`ac_match_id`	Accepts 'y' (yes) and 'n' (no) inputs. Will see performance increases when used with larger sheets. Matches to the 'id' column for pre-filtering of large amounts of data. Defaults to 'n'. If set to 'n', the sheet is filtered based on the 'text' column. If set to 'y', the sheet is filtered based on the 'id' column.
`autocomplete_min`	`ac_min`	Specifies the minimum length of the input necessary to trigger the JavaScript look up; defaults to '3' characters. Note that an input of '0' functions the same as '1' (the menu will not be triggered until 1 letter or digit is entered). Specifying too large a number here could make the function less intuitive for the respondent since the pulldown will be triggered later.