PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

DDDW Tips and Tricks

DDDW Tips and Tricks

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 &

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:


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."

  • Unfilter all rows and set detail height to "standard height".
  • Filter out rows you want to display (yes, the good ones).
  • SetDetailHeight to 0 for the rows in the primary buffer (this will make them invisible).
  • Unfilter the DDDW.

    You'll be left with all the rows so the display value can be found but the rows you don't want to see have a height of 0 (see Listing 2).

    The Cursor Keys
    We can code an event using pbm_dwnkey to get the keys pressed by a user on our DataWindow; however, if you type the up or down arrows while the cursor is positioned on a DDDW, the itemchanged event usually gets triggered, bypassing the pbm_dwnkey event. This might be bad in some situations; for example, we have problems trapping as these keys are the first and last row in our child DataWindow. The solution is to trap these in a user event mapped to pbm_command.

    From the win32 helpfile (WM_COMMAND):

    The WM_COMMAND message is sent when the user selects a command item from a menu, when a control sends a notification message to its parent window, or when an accelerator keystroke is translated.

    This means that in PowerBuilder, whenever an event occurs in the DataWindowChild it sends a notification code to the parent DataWindow. This code can be intercepted in the user event mapped to a pbm_command DataWindow event. If we want to experiment to determine which events you can intercept, try adding the following code to ue_command mapped to pbm_command:

    mle_status.text += "hwndchild = " + String(hwndchild)
    + ", " + "childid = " + String(childid) + ", "
    + "notificationcode = " + String(notificationcode) + "~r~n"

    The code needed to intercept the dropdown arrow is:

    int li_rc
    li_rc = this.GetChild("dept_id", ldwc_ddlb)
    IF childid = Handle(ldwc_ddlb) THEN
    CHOOSE CASE notificationcode
    CASE 2048
    Post Event ue_DDLBRowFocusChanged()
    END IF

    Some samples for notification code are shown in Table 1.

    One caveat, the pbm_command event is invoked for just about everything that goes on in a DW that's not already captured by another event. In other words, it will be invoked a lot. To limit the number of times the code in this event is invoked, you can map another user event to the event ID pbm_dwnmousemove. Don't put any code in this event. This will cause all mouse move events to go to the other event instead of the one mapped to pbm_command.

    Collapsing the DDDW
    This might also help you, if you have one or more rows in your DDDW where your display and data value appear more than once, but you want to set another column depending on a DDDW column that's not used for the display or data value. If you have such a row, PowerBuilder does not recognize (or fire) an itemchanged event, which (from PowerBuilder's point of view) is correct but does not solve our business problem. A good solution is to trap the cursor keys and look at which row the user is in in the DDDW and set the dependent column by hand.

    You can achieve what you want by mapping one user event to pbm_dwndropdown to figure out when a DDDW was opened and another one to pbm_ncpaint, which is fired when a DataWindow needs to be repainted, and then code something like Listing 3.

    Autocomplete DDDW
    We would like to implement the type-along search functionality for a DropDownDataWindow. This means that when we start typing in our DDDW column we want the column to display the next matching value automatically (see Figure 3).

    The steps to accomplish this are:

    • Make your DDDW column editable.
    • Code the editchanged (for DDLB) and itemfocuschanged (for DDDWs).
    This can be found in the PFC. I've prepared a complete sample where I extracted the code from the PFC; it can be downloaded from PBDJ's Web site, www.sys-con.com/pbdj/sourcec.cfm, or from www.bhitcon.net. In this sample you'll find the autocomplete feature as well as everything we were discussing.

    The DropDownDataWindow (DDDW) edit style is one of PowerBuilder's most useful features. It's essentially a DataWindow within a DataWindow. Its most common use is as a listbox type control where the DataWindow that's actually dropped down looks and acts like a listbox control, allowing the user to select a row. But as we've seen, this is just the beginning of what can be done with dropdowns. DDDWs are a powerful feature in PowerBuilder; they're not easy to use, but if you know how to use them correctly you're one step closer to becoming a PowerBuilder expert.

  • More Stories By Berndt Hamboeck

    Berndt Hamboeck is a senior consultant for BHITCON (www.bhitcon.net). He's a CSI, SCAPC8, EASAC, SCJP2, and started his Sybase development using PB5. You can reach him under [email protected]

    Comments (1)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.