Coordinated Drop-Down Lists

I have seen a lot of inquiries in the users alias and the forum about how to do coordinated drop-down lists. Coordinated drop-downs are basically a set of nested master-detail records that follow the same pattern that we show in Using Databound Components to Access a Database. With coordinated drop-downs, whenever the user selects a new item from the master drop-down, you need to refresh the list in the detail drop-down.

In the following example, I show how to coordinate drop-down lists that contain data from the Travel database. The first drop-down lists all the PERSON records. The second drop-down shows all the TRIP records for the selected PERSON, and the third drop-down shows all the FLIGHT records for the selected TRIP. Finally, the detail data shows data from the selected FLIGHT.

The trick with coordinating multiple drop-downs, is that you need to trickle down the refreshing of the lists. A change in the first drop-down's selection requires refreshing the second list, which in turn, requires refreshing the third list, and so on.

In this example, I took the easy route. When the user makes a new selection from the PERSON drop-down, I select the first record by default and coordinate the TRIP drop-down by showing all the trips for the first-person. Then, I show all the flights for the first TRIP record, and the detail data for the first FLIGHT record.

  1. Create your Sun Java Studio Creator (JSC) or NetBeans Visual Web Pack (VWP) project.

  2. Drop three Drop Down List components, a Property Sheet component, and a Message Group component onto the page in the Visual Designer. The Message Group component is to help diagnose problems if something goes wrong.

  3. Select each component and, in the Properties window, change the id property as follows, from top to bottom:

    1. personDD
    2. tripDD
    3. flightDD


  4. Drag the PERSON table from the Travel database (in the Servers window for JSC, and in the Runtime window for VWP), and drop it on the personDD Drop Down List component in the Visual Designer.

  5. Drop the TRIP table on the tripDD component, and drop the FLIGHT table on the flightDD component.

  6. Right-click each Drop Down List component and choose Bind to Data. Make the following selections:

    1. personDD. Value Field: PERSON.PERSONID, Display Field: PERSON.NAME
    2. tripDD. Value Field: TRIP.TRIPID, Display Field: TRIP.DESTCITY
    3. flightDD. Value Field: FLIGHT.FLIGHTID, Display Field: FLIGHT.DEPAIRPORT


  7. Now, right-click each Drop Down List component and choose Auto-Submit on Change from the pop-up menu. This will cause the page to be submitted to the server each time a new selection is made from any list, and will cause the appropriate change event handlers to be called.

  8. Drag the FLIGHT table again, but this time drop it on a blank spot on the page. A dialog box appears. Choose the radio button for creating a flightRowSet in the page bean, and click OK.

    I chose to put the other queries in the session bean because I didn't want to lose the query data everytime the page is submitted. As the detail data pretty much changes on each submission, I am choosing to put this rowset in the page bean.

  9. In the Outline window, double-click the SessionBean1 > tripRowSet node to open it in the Query Editor. In the Query Editor, right-click on the PERSONID cell in the first column and choose Add Query Criteria. Select the Parameter radio button, click OK and close the Query Editor.

  10. Do the same for the flightRowSet, but add the query criteria for the TRIPID. Close the Query Editor.

  11. Now, double-click the flightRowSet in the page bean, add query criteria for FLIGHTID, and close the Query Editor.

    This query is for the detail data.

  12. Display the page in the Visual Designer (it should be displayed already), and click Java to switch to the source editor.

  13. Add the following code to the bottom, just before the final end brace:
    public void initFlightDropDown() {
        try {
            tripDataProvider.cursorFirst();
            getSessionBean1().getFlightRowSet().setObject(
                    1, tripDataProvider.getValue("TRIP.TRIPID"));
            flightDataProvider.refresh();
        } catch (Exception e) {
            error("Cannot switch to trip " +
                    tripDataProvider.getValue("TRIP.TRIPID"));
            log("Cannot switch to person " +
                    tripDataProvider.getValue("TRIP.TRIPID"), e);
        }
    }
    
    public void initFlightDetail() {
        try {
            flightDataProvider.cursorFirst();
            flightRowSet.setObject(
                    1, flightDataProvider.getValue("FLIGHT.FLIGHTID"));
            flightDataProvider.refresh();
        } catch (Exception e) {
            error("Cannot switch to flight " +
                    flightDataProvider.getValue("FLIGHT.FLIGHTID"));
            log("Cannot switch to flight " +
                    flightDataProvider.getValue("FLIGHT.FLIGHTID"), e);
        }
    }
    
  14. Add the following code shown in bold to the prerender method:
    public void prerender() {
        if ( personDD.getSelected() == null ) {
            try {
                personDataProvider.cursorFirst();
                getSessionBean1().getTripRowSet().setObject(
                        1, personDataProvider.getValue("PERSON.PERSONID"));
                tripDataProvider.refresh();
            } catch (Exception e) {
                error("Cannot switch to person " +
                        personDataProvider.getValue("PERSON.PERSONID"));
                log("Cannot switch to person " +
                        personDataProvider.getValue("PERSON.PERSONID"), e);
            }
            initFlightDropDown();
            initFlightDetail();
        } else {
            try {
                // Synchronize data providers with current selections
                personDataProvider.setCursorRow(
                        personDataProvider.findFirst(
                        "PERSON.PERSONID", personDD.getSelected()));
                tripDataProvider.setCursorRow(
                        tripDataProvider.findFirst(
                        "TRIP.TRIPID", tripDD.getSelected()));
                flightDataProvider.setCursorRow(
                        flightDataProvider.findFirst(
                        "FLIGHT.FLIGHTID", flightDD.getSelected()));
    
            } catch (Exception e) {
                error("Cannot switch to selections");
                log("Cannot switch to selections", e);
            }
        }
    }
    
  15. Click Design in the editing toolbar to switch to the In the Visual Designer.

  16. Double-click the personDD Drop Down component (the top one) to cause the IDE to add a process value change handler for the component. The IDE switches to the added code. Add the following code shown in bold to the method:
    public void personDD_processValueChange(ValueChangeEvent event) {
        try {
            getSessionBean1().getTripRowSet().setObject(
                    1, personDD.getSelected());
            tripDataProvider.refresh();
        } catch (Exception e) {
            error("Cannot switch to person " +
                    personDataProvider.getValue(
                    "PERSON.PERSONID"));
            log("Cannot switch to person " +
                    personDataProvider.getValue(
                    "PERSON.PERSONID"), e);
        }
        initFlightDropDown();
        initFlightDetail();
    }
    
  17. Click Design to switch back to the Visual Designer, double-click the tripDD Drop Down List component (the middle one) to create its change event handler, and add Add the following code shown in bold to the method:
        public void tripDD_processValueChange(ValueChangeEvent event) {
            try {
                getSessionBean1().getFlightRowSet().setObject(
                        1, tripDD.getSelected());
                flightDataProvider.refresh();
            } catch (Exception e) {
                error("Cannot switch to trip " +
                        tripDataProvider.getValue(
                        "TRIP.TRIPID"));
                log("Cannot switch to trip " +
                        tripDataProvider.getValue(
                        "TRIP.TRIPID"), e);
            }
            initFlightDetail();        
        }
    
  18. Click Design one more time and double-click the flightDD Drop Down List component (the bottom one). Add the following code shown in bold the change event handler:
    public void flightDD_processValueChange(ValueChangeEvent event) {
        try {
            flightRowSet.setObject(
                    1, flightDD.getSelected());
            flightDataProvider1.refresh();
        } catch (Exception e) {
            error("Cannot switch to flight " +
                    flightDataProvider.getValue(
                    "FLIGHT.FLIGHTID"));
            log("Cannot switch to flight " +
                    flightDataProvider.getValue(
                    "FLIGHT.FLIGHTID"), e);
        } 
    }
    
  19. Take a look at the code you have added. The code in the prerender method first handles the case where the user first lands on the page and nothing has been selected. It seeds the drop-down lists with the first record from the PERSON query and synchronizes queries for the nested detail drop-down lists and the detail data. If there is a selected person, it synchronizes the cursor positions in the queries with the drop-down list selections.

    The change event handlers synchronize the appropriate detail queries. A change in PERSON selection refreshes the trip, flights, and flight detial queries. A change in TRIP selection refreshes the flights and flight detail queries, and a change in FLIGHT selection refreshes the flight detail query.

  20. The last thing we need to do is to display some detail information. Click Design to display the page and, in the Outline window, expand propertySheet1, expand section1, and select property1 (you can also select the property1 in the Visual Designer, but I find it easier to work with the property sheet in the Outline window).

  21. In the Properties window, change the label property to Airline:.

  22. Drop a Static Text component on property1 in the Outline window.

  23. Expand property1, right-click staticText1, and choose Bind to Data.

  24. Choose flightDataProvider1 (not flightDataProvider) from the drop-down list, select FLIGHT.AIRLINENAME, and click OK.

  25. If you want, you can add more Property components to the Property Sheet Section, add Static Text components to the Property components, and bind the Static Text components to other columns in the database table, as I did in the above screenshot.

  26. Now you are ready to click Run Main Project and test your coordinated drop-downs.
Comments:

Post a Comment:
Comments are closed for this entry.
About

divas

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today