The DropDownDataWindow (DDDW) edit style is one of PowerBuilder's outstanding features. Yes, I know there are a lot of new and exciting capabilities in the upcoming release of PowerBuilder, but in this article I'll try to solve some of the current problems with the existing features that are popping up in nearly every project I've seen.

Here I'll focus on DropDownDataWindows, including:

• How to get started with DDDWs
• Filtering DDDWs without losing the display value in other rows
• Catching the collapsing of a DDDW
• Trapping the cursor keys in a DDDW
• Autocomplete DDDW values

Getting Started
The basics are well covered in the PowerBuilder User's Manual, but if you still have problems defining them look at Figure 1. Click on the column you want and its edit style (yes, click on edit on the properties) as DDDW (choose DropDownDW as Style Type).

Next, choose your DataWindow, display column, and data column. The display column is the kind of data that's displayed to the user. The data column value is the one that's saved into the database.

You might also consider checking the "V ScrollBar" property. This is something a lot of users forget; when you test your application, you have just a few rows in your DDDW. When you go into production you'll have a lot of rows there, but now the user can't scroll with the mouse to these rows.

Now we'll start by describing some problems beginners frequently run into when they are using DDDWs.

The first problem I've encountered as a trainer in the Fast Track to PowerBuilder classes is the following:

A student creates a DataWindow and wants to define a column with the DropDownDataWindow edit style. He or she opens a second DataWindow painter and creates the DDDW and saves it (remember the naming convention for DDDWs? Yes, it's d_dddw_xxxx, where xxxx is your part of the name). Now the student switches back to the column and configures the edit style. The next step is to test the DDDW column by inserting a new row or collapsing the column on an existing row. Well, the student expects to see his or her DDDW data from the database, but that's wrong. The DDDW is empty (see Figure 2), but why? Well, the DDDW is filled only after a retrieve of the DataWindow. So the student reretrieves the data and voilà, everything is fine when we look at the column.

The next thing we should talk about is DDDWs with a retrieval argument. I'm quite sure you've already seen that: if you use a DDDW that has retrieval arguments, PowerBuilder will prompt the user for those arguments, displaying an ugly window when you retrieve the parent DataWindow. This is, of course, not the normal behavior you want your application to have.

The solution is to retrieve the DDDW before you get data for the primary DataWindow. The problem is that we need a reference to the DDDW so we can issue a Retrieve() or InsertRow() function on it. To get the object handle to the DDDW, use the GetChild() function.

Since PowerBuilder 8 we are able to prevent the retrieve of the DDDW by disabling the "Autoretrieve" property. This lets us retrieve the DDDW anytime (also after the retrieve of the master DataWindow).

We can also prevent the retrieval of a DDDW by saving it with a blank row and adding a row on the DDDW in the DataWindow painter. To do so click on the column specification window on the data tab and insert a blank row using the right-mouse button. This saves the DataWindow with one blank row preloaded for us. Since PowerBuilder will see at least one row in the DDDW, it won't try to issue its own retrieve. This also allows us to retrieve the DDDW anytime.

Now that we know how to prevent the retrieve of a DDDW, the next step is to get the DDDW handle and retrieve it alone. We can accomplish this by using the GetChild function. Normally when you interact with a column on a DataWindow, you use the column name to get or set the current value. However, we want to interact with the DDDW that is on a column, not with the column itself. GetChild() provides us with a reference variable that points to the actual DataWindow in memory. We can use this variable to issue functions against the DDDW such as Retrieve(), InsertRow(), and Modify().

Here's a sample of how to use GetChild():

DataWindowChild dwc
IF dw_1.GetChild( "dept_id", dwc ) > 0 THEN
dwc.SetTransObject( SQLCA )
IF dwc.Retrieve() = 0 THEN &
dwc.InsertRow(0)
END IF

Notice that GetChild returns an integer. This integer tells you whether GetChild was able to return a reference to the child DataWindow into the variable dwc. GetChild looks on your parent DataWindow for the column you specify. It then assigns the DataWindow control used to retrieve that column to the variable dwc.

If the column "dept_id" is a DDDW, then dwc should hold a reference to the DDDW. If we had misspelled the name of the column or the column didn't exist on the DataWindow, GetChild() would return -1. As usual don't forget to use SetTransObject on the child before you code a retrieve. Once we have our reference to the dropdown, we can do almost anything with it that we can do to a normal DataWindow including using Modify, Describe, SetTransObject, Find, Sort, and Filter.

Be sure that the data value for a column that uses the DropDownDataWindow edit style is limited to 511 characters.

Share DropDownDataWindows
If you use a lot of DDDWs, it might be a good idea to populate a DataStore for each DDDW and share with the DataStore. An important point to remember is that you must prevent the retrieve of the DDDWs prior to sharing (this is usually accomplished in the open event of the window) or the DDDWs will initially do a retrieve. If this happens, you will be retrieving twice for each DDDW! To stop the retrieve of the DDDW, do an insert row into the ChildDataWindow from the open event of the window.

Get the Display Value
I've come across a problem with a PB 6.5 app: on a DataWindow I have a DDDW that accesses a simple two-column (name, number) table. The DDDW uses "name" as its display column and "number" as its data column. The user is able to select a name from the list, and the number is stored in another table. So far, so good.

The problem occurs when I want to programmatically access the data contained in the display column of the DDDW (i.e., the name the user selected). I have gone through all the documentation I can find, and I can't figure out how to obtain this information.

Solution #1 (Recommended)
Take advantage of the Describe() Evaluate function. In the following code the DDLB or DDDW column is called state_code.

string ls_rownumber, ls_displayvalue

ls_rownumber = string(dw_1.getrow())
ls_displayvalue = dw_1.describe("Evaluate
( 'lookupdisplay(dept_id) ', "+ls_rownumber+" )")

This solution does not require the definition of an additional computed column on the DataWindow.

Note: This solution will not work in the itemchanged event of the main DataWindow. It must be done in an event that occurs after the itemchanged event has completed by creating a custom unmapped user event ue_ getdisplayvalue, and then windowname.postevent (ue_getdisplayvalue) from the itemchanged event of the DataWindow.

Solution #2 (Computed Column Approach)
Go into the DataWindow painter and add a computed column. The expression should be Lookupdisplay(dept_id) where dept_id is the name of your DDLB or DDDW column. Name the computed column "display". Place the computed column anywhere since we will make it invisible with Modify().

Next, go into the window and add a user event called ue_lookup. In the script for this event code:

// displayvalue will contain the display value
the user has selected from ddlb or dddw column
string displayvalue
displayvalue = dw_1.getitemstring(dw_1.getrow(),"display")

In the Itemchanged event for the main DataWindow (dw_1) code:

// check to see if the ddlb or dddw column is
the correct one they are changing.
// use the column number (#) of the ddlb or dddw column.
if getcolumn() = 3 then
parent.event post ue_lookup()
end if

Adding a DDDW at Runtime
I had some trouble getting this to work; apparently you have to reconnect to the database for it to function properly.

Here is an example that builds the Modify string with the attributes (see Listing 1) (Listings 1-3 can be downloaded from www.sys-con.com/pbdj/sourcec. cfm.):

• Make "dept_id" the data column.
• Make "dept_name" the display column.
• Insert an arrow on the dropdown list.
• Allow editing in the text box.
• Insert a vertical scroll bar on the dropdown list.

Be sure that we are assigning the DataObject of the child DataWindow dynamically, which might end in an error message in the compiled code only. The GetChild function returns -1 when we try to reference the DropDownDataWindow. This is because PowerBuilder compiles only "used" objects into an executable. If we assign a DataObject (or in our case a DDDW) programmatically using a string, PowerBuilder does not recognize this DataObject as used. The solution is that we assign a pbd out of the pbl containing that particular DataObject (in our case "dw_popup_table_list"), or we should include it in a .pbr file as:

pibblename.pbl(dw_popup_table_list)

Filter DDDWs
You may have already encountered this problem: we would like to filter a DDDW depending on a value in the current row. If we filter out some values in the DDDW, the dropdown shows only description data for rows that are in the primary buffer. In all other rows we see the data value.

The trick here is not to filter. You need to have the description or display value in the primary buffer. One method is to use SetDetailHeight to mimic "filtering."

• Figure 1
• Figure 2
• Figure 3
• Source Code
• Figure 4
• Zip file