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.

The tutorials show how to display a database column in a drop-down list. But what if you want to show multiple database columns in the list. For example, the VIR database table that comes bundled with the product has a FIRSTNAME column and a LASTNAME column. You might want the drop-down list to show both fields, such as "Jane Doe".

One solution is to add an alias to the database query. Using the VIR database table as an example, you drop the VIR database table on the page, and use the Query Editor to add VIR.EMPLOYEE.FIRSTNAME || ' ' || VIR.EMPLOYEE.LASTNAME AS FULLNAME to the query statement, as shown below.

SELECT ALL VIR.EMPLOYEE.ID,
                    VIR.EMPLOYEE.FIRSTNAME,
                    VIR.EMPLOYEE.LASTNAME,
                    VIR.EMPLOYEE.EMAIL,
VIR.EMPLOYEE.FIRSTNAME || ' ' || VIR.EMPLOYEE.LASTNAME AS FULLNAME
FROM VIR.EMPLOYEE    

To test the statement, right-click in the query pane of the Query Editor and choose Run Query. A dialog pops up explaining that the the IDE's parser only knows a small subset of common SQL syntax. Click continue and the Query Editor should output the query results.

You will also see that the the visual panes in the Query Editor for this particular rowset no longer work. This is because of the same reason that the IDE's parser only knows a small subset of common SQL syntax. This problem only affects the Query Editor.

To see the query in action, drop a Drop Down List component on the page, right-click the component and choose Bind to Data. Click the Bind to Data Provider tab, choose employeeDataProvider, select EMPLOYEE.ID for the Value Field, and select FULLNAME for the Display Field.

Click OK. Test and Run.

The above query works with the bundled database. You have to adjust based on the syntax of your database. For example, for MySQL, use something like select concat(member.lastname, ", ", member.firstname).

A new tutorial writer, Joe, recently joined the Creator Tutorials team. While working on the Tabset tutorial, he came up with this useful tidbit: how to make a selection from a drop-down list navigate to a different page.

1. Right-click the Drop Down List component and select Auto-Submit on Change.

2. Use the Page Navigation Editor to create links from the page with the drop-down list to the other pages. The rest of the steps assume that you have 3 links -- case1, case2, and case3.

3. Close the Page Navigation editor and return to the page with the Drop Down List component in the Visual Designer. Right-click the Drop Down List component and choose Configure Default Options. (The Delving into Components tutorial shows how to set the list items programmatically.)

4. Make the Value for each option be one of the link names that you added in the Page Navigation editor, such as case1, case2, and case3.

5. In the Visual Designer, double-click the Drop Down List component. The Java Source editor opens up on the process value change event handler for the compnent.

6. Make the body of the method similar to the following code that is shown in bold (using the correct id for your Drop Down List component).

    public void dropDown1_processValueChange(ValueChangeEvent event) {
        Application application = getApplication();
        NavigationHandler navigator = application.getNavigationHandler();
        FacesContext facesContext = getFacesContext();
        try {
            navigator.handleNavigation(facesContext, null,(String) getDropDown1().getSelected());
        } catch (Exception e) {
            log("can't navigate to " + (String) getDropDown1().getSelected());
        }
    }

7. Fix imports.

if (!"none".equals(getDropDown2().getSelected()))

I just finished upgrading the Using Databound Components to Access Databases for the final release of the product. Because this upgraded tutorial won't be published until after the final Creator 2 release comes out, I thought I would share the following piece of information that I added to the tutorial. I have seen several posts on the EA forum where people are asking how to reposition the cursor to a different table row. The answer is to call tableRowGroup1.setFirst(zero-based-index-value);.

If you use the table paging feature on a master detail page that is like the one in the tutorial (a drop-down list for the master data and a table for the detail data), you will see that when the user selects a different choice from the dropdown list, the table displays new data, but stays on the current page. For example, if the user switches to page 2 and then chooses a new item from the dropdown list, the table shows the second page of data for the new selection. To fix the problem, add the following code after the tripDataProvider.refresh() statement in the master component's value change event handler: tableRowGroup1.setFirst(0);. This ensures that the first page is always displayed when a new name is selected from the drop-down list.

A new tutorial, GoogleSearch Web Service Portlet, will be added to the EA tutorials page, hopefully today or tomorrow.

The upgrading of the Referencing Class Libraries tutorial from Creator 1 to Creator 2 was low on our priority list. However, several people sent a request for this tutorial to the CreatorDocsFeedback@Sun.Com alias. So, we have upgraded this tutorial and it is going through the engineering review process. Your input matters to us, so please continue to submit tutorial suggestions.