Custom Actions: CascadingMenus


This custom action is used for creating a set of two or more drop down menus where the contents of each drop down depend on the selection made on the previous drop down. The typical example of these menus is car manufacturer, year, and model dropdowns. The values in the dropdowns come from a database query.

Create a set of drop down question with no values and note their names. Then create a question of type Action, and fill it out as indicated here.

The Action question MUST follow the dropdown quesions,not precede it.

In the Action field, enter:
CascadingMenus dropdown1,dropdown2,dropdown3 where each of these is the name of one of the empty dropdown questions. There is no limit to how many dropdowns can be used. For a single dynamic dropdown, use DynamicDropDown instead.

The Enter Script field contains a SQL statement.

The SQL statement selects from a table exactly twice as many columns as there are dropdown menus. Each pair of table columns is used to fill the corresponding menu. The first column of each pair is used to fill in the value stored by a menu option and the second column is used for the visible text of that option. If the value and the text are the same, repeat the same column name; note that some databases require using an AS clause after the repeated column. A where clause can be used, and question piping can be used anywhere in the SQL statement.

Add datasource=JNDIDataSourcename right after CascadingMenus to execute the SQL query using a different data source. Normally, CascadingMenus finds tables in the same database that all of ViewsFlash uses. But sometimes, it is useful to find the data in a different schema or database altogether. In this case, create an additional JNDI Data Source and put its name in the datasource parameter. The additional database does NOT need to be the same database brand (eg., Oracle / DB2 ). More detail here.

Note that [/authenticateduserid] is the tag, described in question piping, that contains the user id of the current logged-in respondent.
Note that [/dbtablenameprefix] will use that paramer if set up in the servlet parameters to provide a prefix for the table name.

Example. A database table CARS has been created with columns MAKE, MAKE_LEGEND, YEAR, MODEL, MODEL_LEGEND and STATE. Columns MAKE AND MODEL contains a code, wherease the corresponding LEGEND columns contain human-readable names. Column YEAR contains the actual year, and no legend is necessary. Three empty dropdowns are created for Make, Year, and Model, with appropriate explanatory text ( "Select the make of the car").
The Action contains:
CascadingMenus Make,Year,Model
The SQL in the Script field contains:
select MAKE, MAKE-LEGEND, YEAR, YEAR, MODEL, MODEL_LEGENDfrom CARS where STATE='[/state]' ORDER BY MAKE_LEGEND, YEAR DESC, MODEL_LEGEND

The table contains one row for every unique combination of make, year and model and state code where that model is certified. The questionnaire contains a field "state" whose value is used in the WHERE clause of the SQL query to limit the records to only those sold in the state. The ORDER BY clause sorts the resulting elements accordingly.

This Action is always executed before the page that contains the Action is composed.

Next: SQLSelect