Database Structure
The information used to populate your lists using the Dynamic Dropdown behaviors is all taken from a database that you create prior to adding the lists to your page. In order for your lists and menus to work properly, you must first identify your list/menu structure and configure your database to hold this information and make it identifiable to the behaviors.
The following are two possible database configurations that might be used with WA Dynamic Dropdowns.
ParentID is the unique key for each list option.
The Recordset SQL Statement then used to populate the parent list would be:
SELECT ParentID, ParentText FROM Parent ORDER BY ParentText
ChildID is the unique key for each list option.
ParentID is the link ID for each option to its corresponding Parent list option in the Parent table.
The Recordset SQL statement for the dynamic array would be:
SELECT ChildID, ChildText, ParentID FROM Parent ORDER BY ParentText
ParentText can also be used as the unique ID for each list option since there is no ID for the parent specified in the database.
The Recordset SQL Statement then used to populate the parent list would be:
SELECT DISTINCT ParentText FROM Parent ORDER BY ParentText
(the DISTINCT modifier will prevent repeat inserts in your parent table)
In order to create the dynamic array, the Child Table for these options must contain the following:
ChildText is the text for each list option.
ChildID is the unique key for each list option.
ParentText is used as the ID for the parent list since no key column exists in the database.
The Recordset SQL statement for the dynamic array would be:
SELECT ChildID, ChildText FROM Parent ORDER BY ChildText
The following are two possible database configurations that might be used with WA Dynamic Dropdowns.
Using Two Tables in the database
Parent Table
This table contains the list options and the values for your main list, the "Parent List", it must contain:- your Parent List options
- unique ID's for those options
ParentID is the unique key for each list option.
The Recordset SQL Statement then used to populate the parent list would be:
SELECT ParentID, ParentText FROM Parent ORDER BY ParentText
Child Table
This table contains the list options for your subordinate list, or "Child List", added by choosing their related "Parent" List options. It must contain:- your Child list options
- unique ID's for Child list options
- appropriate Parent ID's for each Child list option
ChildID is the unique key for each list option.
ParentID is the link ID for each option to its corresponding Parent list option in the Parent table.
The Recordset SQL statement for the dynamic array would be:
SELECT ChildID, ChildText, ParentID FROM Parent ORDER BY ParentText
Using One Table in the database:
Combined Table
This table contains the list options and the values for both lists, or "Parent List", it must contain: your Parent List options unique ID's for those options Take a look at the example:
ParentText can also be used as the unique ID for each list option since there is no ID for the parent specified in the database.
The Recordset SQL Statement then used to populate the parent list would be:
SELECT DISTINCT ParentText FROM Parent ORDER BY ParentText
(the DISTINCT modifier will prevent repeat inserts in your parent table)
In order to create the dynamic array, the Child Table for these options must contain the following:
- your Child list options
- unique ID's for Child list options
- corresponding Parent ID's for each Child list option
ChildText is the text for each list option.
ChildID is the unique key for each list option.
ParentText is used as the ID for the parent list since no key column exists in the database.
The Recordset SQL statement for the dynamic array would be:
SELECT ChildID, ChildText FROM Parent ORDER BY ChildText