Databases Reference
In-Depth Information
3-4. Changing the List of Items in a Drop-down List Dynamically
Problem
You have a drop-down list. When you change the values in this drop-down list, you want the values in
another drop-down list to also change. In other words, you want to create a cascading drop-down list.
For example, you have an equipment request form. When you submit a request, you can zoom in on
a specific equipment using the category and subcategory drop-down list. When you change the
equipment category, the subcategory list refreshes to show only the equipment relevant to that category.
Solution
To test the solution in this recipe, you will first need to set up some sample database and form objects.
After that, you will create a cascading relationship between the two drop-down lists. Let's start with the
sample objects.
Setting up the Sample Objects
To setup the sample objects used in this recipe, follow these steps:
1.
Create the tables shown in Listing 3-9.
Listing 3-9.
Sample Equipment Request Tables
CREATE table "EQUIPMENTREQUEST" (
"REQUESTID" NVARCHAR2(10),
"CATEGORY" NVARCHAR2(255),
"SUBCATEGORY" NVARCHAR2(255),
constraint "EQUIPMENTREQUEST_PK" primary key ("REQUESTID")
)
/
CREATE sequence "EQUIPMENTREQUEST_SEQ"
/
CREATE trigger "BI_EQUIPMENTREQUEST"
before insert on "EQUIPMENTREQUEST"
for each row
begin
if :NEW."REQUESTID" is null then
select "EQUIPMENTREQUEST_SEQ".nextval into :NEW."REQUESTID" from dual;
end if;
end;
/
CREATE table "CATEGORY" (
"CATEGORYNAME" NVARCHAR2(255),
"CATEGORYID" NUMBER(6,2),
