ApEx: Parent-Child Select lists and onChange Submitting

Within ApEx (Oracle Application Express), it is easy to create select lists that auto-populate based on the selection of a prior select list.

In this example, lets setup two select lists. The first will determine what populates the second, and the second submits with both entries. This could be good for selecting a record based on a two-key (or primary-secondary key) table/view. (You could use DML to then popualte a form given you know your record.)

Create the first select list as a select list with redirect. Setup the LOV with a SQL query to select the distinct possibilities of the primary key. Remember that LOV queries in ApEx require selecting two things...the select list display and its corresponding value. Here are the steps (with examples)

From a page definition screen

  1. Click the icon to add a new item
  2. Choose select list and click Next
  3. Choose Select List with Redirect and click Next
  4. Name your item and click next
  5. Add Null Text (like 'Select ID') and a null value (like -1)
  6. then make your LOV Query
  7. select distinct class.name n, class.id d
    from owner.class
    order by 1

  8. Provide a Label and click Next
  9. Click the Create button

Create the second select list as a a select list with submit. Again, create it as a LOV with a SQL query. This time, the SQL query will query for all records given the first key is satisfied (value retireved form the first menu. Again, we'll branch to the submit.

  1. Click the icon to add a new item
  2. Choose select list and click Next
  3. Choose Select List with Submit and click Next
  4. Name your item and click next
  5. Add Null Text (like 'Select Name') and a null value (like -1)
  6. then make your LOV Query
  7. select chair.name n, chair.id d
    from owner.chair, owner.class
    where class.id = chair.class_id
    and chair.id = :P2_CLASSNAME
    order by 1

  8. Provide a Label and click Next
  9. Click the Create button

Finally, create the branch. Select which page you wish to go to, passing the appropriate values.

  1. Click the icon to add a new branch
  2. Choose On Submit: Before Computation (Before Computation, Validation, and Processing)
  3. Choose Branch to Page or URL
  4. Select recipient page
  5. Set these Items (values from the two pull down lists will go to two fields (that can be hidden if desired) on the page you are branching to
  6. Click Next
  7. Click Create Branch

That's the bare bones process. Since the form will only submit onChange, you may wish to have a submit button as well in case you wish to edit the same item twice in a row (session will keep the pulldown entries as they were).