Advanced DropDownDataWindows

Don Draper

Table of Contents

Overview
Using GetChild()
Retrieving a DDDW Based on Another Columns
Using a Multi Result Set DDDW in a Multirow DataWindow
Undocumented DDDW Events
Stopping the User from Scrolling to Invalid Rows
Adding Color to a DataWindow
Speeding Up DDDWs With Caching Techniques
Conclusions

Overview

The DropDownDataWindow (DDDW) edit style is one of PowerBuilder's most useful features. It is essentially a DataWindow within a DataWindow. Its most common use is as a listbox type control where the DataWindow that is actually dropped down looks and acts like a listbox control allowing the user to select a row. However, this is just the beginning of what can be done with dropdowns. Since the DDDW is another DataWindow, your imagination is the limit. Instead of looking like a listbox, we can make it look like a data entry form, or a help form, even a calendar!

You Will Learn:

When you place a DDDW on a DataWindow, PowerBuilder follows a simple rule that governs the retrieval of the DDDW itself. When the parent DataWindow is retrieved or has the initial row inserted, the following sequence takes place for each DDDW

DropDownDataWindow Rule Number 1:

If there are 0 rows on the DDDW then issue a retrieve() for the DDDW.

What this means is that if you place a DDDW that has retrieval arguments, PowerBuilder will prompt the user for those arguments when you retrieve the parent DataWindow. I suspect this is not the normal behavior you want your application to have.

Unless your DDDW is a plain, ordinary, no arguments type DDDW, you'll need to populate the DDDW before you retrieve the primary DataWindow. To do so, you'll need an object reference to the DDDW so you can issue a Retrieve() or InsertRow() function. To get the object handle to the DDDW, you'll need to use the GetChild() function.

Tip: To ensure that a DropDownDataWindow isn't retrieved automatically, save the DDDW with a blank row by adding a row from the Rows --> Data menu in the DataWindow Painter. This saves the DataWindow with one blank row preloaded for you. Since PowerBuilder will see at least one row in the DDDW, it won't try to issue its own retrieve. You are then free to Retrieve() the DataWindow yourself.

Using GetChild()

Normally when you interact with a DDDW, you just use the column name to get the current value and set the current value. You don't actually issue functions directly to the DDDW itself. However, to issue your own retrieves or to manipulate the DDDW in any way, you'll need a way to reference the DDDW. To get this reference, you'll use the GetChild() function. What GetChild() does is provide a reference variable that points to the actual DataWindow in memory. You can use this variable to issue functions against the DDDW such as Retrieve(), InsertRow(), Modify(), etc.

The GetChild() function is declared as follows: 

integer GetChild( string column_name, datawindowchild dwc)

Notice that GetChild returns an integer. This integer tells you whether GetChild was able to return a reference to 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.

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

DataWindowChild dwc

IF dw_1.GetChild( "color_id", dwc ) > 0 THEN

   dwc.SetTransObject( SQLCA )

   dwc.Retrieve()

END IF

If the column, "color_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 did not exist on the DataWindow, GetChild() would return -1.

Here's another sample: 

DataWindowChild dwc 

IF dw_1.GetChild( "color_id", dwc ) > 0 THEN 

  dwc.InsertRow(0) 

  dwc.SetItem( "cust_id", "( New )" ) 

END IF

In this case, we added a row to the DDDW. Once we've got 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, Filter...anything!

Retrieving a DDDW based on the value of another column

Figure 1 : Database Schema from S-Designor

In Figure 1, we see the database schema for the database used in these examples. Take a look at the color, product, valid_product_color, and sales_order_items tables. Here's a description of those tables:  

Table Description
color These are all the possible colors that the company can produce.

product

 These are the products that the company manufactures
valid_product_color This is a cross-reference table that contains the valid colors for each given product.
sales_order_item These are items that were ordered. When an product is ordered, the customer will specify which color she wants.

 

What we'd like to have is a data entry window that lists the colors for the product currently being entered into the system. We could just create a DDDW off of the color table and let the user select the proper color. However, we'd then have to do some sort of validation. What we'd like to do is bring up a DDDW off of the valid_product_color table right after the user types in the product_id. To do so, our DDDW would have a retrieval argument for the product_id. This way the DDDW would only retrieve colors for a given product.

Here are the steps to follow to get our DDDW populated based on data we enter:

Create our DDDW based on the valid_product_color_table

Create a DataWindow which will be our drop down. This DataWindow is based on the valid_product_color table and has one retrieval argument: ai_product_id. This DataWindow is named d_product_color_list. Notice that this DataWindow is already saved with one empty row of data preloaded. (You can see this from the Rows --> Data menu in the DataWindow painter.)
 
Also, this DDDW should be sorted on product_id and color. This way, colors for a given product are grouped together which is required for our DDDW filtering technique to work.

Create a data entry DataWindow that will have our DDDW on it

Create a free form DataWindow named d_sales_item_entry from the sales_item table.

One of the columns on d_sales_item_entry is color_id. This column represents the color of the particular item the user will be entering. It has the DDDW edit style defined in Figure 2.

Figure 2 : DDDW Edit Style Definition for the column color_id.

What we'd like the user to do is pick from a valid list of colors for that product. To get this behavior, we're going to have to retrieve the d_product_color_list DDDW after we've populated d_sales_item_entry.

Create a function, wf_get_colors() to retrieve the colors for a given product

To populate the DDDW, we're going to need to know the product id of the current row. Also, if the user changes a product id, we need to repopulate the DDDW as soon as that change is made. Which events do we need to code script for? The events are rowfocuschanged and itemchanged. These fire off when the user changes rows ( I know, I know, there's only one row on this DataWindow right now...we'll get to that later.) or changes the product_id column.

Before we go out and code those functions, let's create a function to populate the dropdown. This way, we'll only write the code once. Then we'll just call that function from the events.

Here's the function. This function will go on our window. If you bring up the window w_sales_item_entry, you'll see the following function defined: 

public integer wf_get_colors( int ai_product_id ) 

DataWindowChild dwc 

Long            ll_return

IF dw_1.GetChild("color_id", dwc) > 0 THEN 

   dwc.SetTransObject( SQLCA ) 

   ll_return = dwc.Retrieve( ai_product_id ) 

   IF dwc.RowCount() = 0 THEN 

      dwc.InsertRow( 0 )

   END IF

   RETURN ll_return

ELSE 

   Return -1 

END IF

Code the DataWindow events, RowFocusChanged and ItemChanged

This function takes a product ID and retrieves its valid colors. You'll see that it is called from the rowfocuschanged and the itemchanged events: event: DataWindow rowfocuschanged 

// Ensure that there is a valid row available 

IF GetRow() < 1 THEN RETURN 

wf_get_colors( GetItemNumber( GetRow() , "product_id" ) ) 
event: DataWindow itemchanged 

CHOOSE CASE GetColumnName() 

   CASE "product_id" 

      wf_get_colors( Integer( GetText () )) 

END CHOOSE

Well, there we are. We've got a dropdown that responds to changes in our data. Pretty snazzy, eh? Well, lets take a closer look at some behavior we have-some behavior that we may not like. Take a look at Figures 3 and 4:

Figure 3: Data as it initially appears. Notice the color is Black.

Figure 4: User changes Product ID to 301. Notice color is now 'B'

What is happening here? Where did the description for Black go? Well, it went away. Why? Because black isn't a valid color for product ID 301. Only Green, Orange, and Pink are (must be a lava lamp). Because PowerBuilder could not find a row that matched the data 'B', it can't display the word Black anymore. This brings us to our second major rule for DropDownDataWindows.

DropDownDataWindow Rule Number 2:

The dropdown only shows description data for rows that are in the primary buffer.

Rule two means that if you filter out rows or delete rows from the dropdown, you're display could change if the DataWindow can no longer find the needed data in the DDDW.

So how do you overcome the problem we just encountered? Well there are several tactics you could try. You could simple set the color to null when the product id changes. This forces the user to select a new color. You could also check to see if the old color is still valid for the current one. A common way I see people do this is through embedded SQL. When the itemchanged event fires, a SELECT would be performed against the valid_product_color for the new product id and color. If there is a match, the color is still valid. Here's how you might code that in your itemchanged event: 

event: DataWindow itemchanged 

// This shows how to use embedded SQL to validate data 

// It should only be used when necessary 

INTEGER i_new_product_id 

INTEGER i_count 

STRING s_color_id 

 

CHOOSE CASE GetColumnName() 

  CASE "product_id" 

  // What is the current color? 

  s_color_id = GetItemString( GetRow(), "color_id" ) 

 

  // What is the new product ID? 

  i_new_product_id = Integer( GetText() ) 

 

  // Retrieve the valid colors for this product_id 

  wf_get_colors( i_new_product_id ) 

 

  SELECT count(*) 

  INTO :i_count 

  FROM valid_product_color 

  WHERE product_id = :i_new_product_id 

  AND color_id = :s_color_id ; 

 

  IF i_count <= 0 THEN 

    // If not, give a warning telling the user that 

    // the color is invalid and set the color to null 

    MessageBox("Warning", "The current color is " + & 

      "no longer valid. Enter a new one.") 

    SetNull(s_color_id) 

    SetItem( GetRow(), "color_id", s_color_id) 

  END IF 

END CHOOSE

Notice that I said it is how you might code it. One of my goals in development is to sent as little SQL to the database as possible and to minimize network traffic. Why should I got back to the server for data I already have. I've got the list of valid colors in my dropdown right? Well then, why don't we just search for our color ('B') in the dropdown. If it there, we've got a valid color. To do such a search, we'll use the Find() function of the DataWindow. Here's the code: 

event: DataWindow itemchanged 

// This shows how to use embedded SQL to validate data 

// It should only be used when necessary 

INTEGER i_new_product_id 

INTEGER i_count 

STRING s_color_id 

 

CHOOSE CASE GetColumnName() 

  CASE "product_id" 

    // What is the current color? 

    s_color_id = GetItemString( GetRow(), "color_id" ) 

 

    // What is the new product ID? 

    i_new_product_id = Integer( GetText() ) 

 

    // Retrieve the valid colors for this product_id 

    wf_get_colors( i_new_product_id ) 

 

    IF IsValid( idwc ) THEN 

      // Is the old color in the new result set? 

      i_count = idwc.Find( "color_id = '" + s_color_id + "'", 1, idwc.RowCount()) 

      IF i_count <= 0 THEN  

        // If not, give a warning telling the user that 

        // the color is invalid and set the color to null 

        MessageBox("Warning","The current color is " + & 

          "no longer valid. Enter a new one.") 

        SetNull( s_color_id ) 

        SetItem( GetRow(), "color_id", s_color_id) 

      END IF 

    END IF 

END CHOOSE

Using the DDDW in a multiple-row DW

In the preceding example, we used the dropdown in a single row situation. If your DDDW doesn't have retrieval arguments, you should have no problems using it in a multi-row situation. Take a look at the DataWindow sequence listed in Figure 5:

 

Figure 5 : Effect of Changing a Result Set with Multiple-Rows

You'll notice immediately that the color DDDW is not behaving properly. As we move to different rows, the DDDWs are showing different data. This is because we coded a call to wf_get_colors in our rowfocuschanged event. As we move rows, the DDDW is getting new data.

In the case of row 1, the colors Orange and Red are not valid colors for product ID 300. As for row 2, Black and Red are not valid colors for 301. However, the third case looks fine! This is because black, orange, and red are all valid colors for product ID 302.

Well, what can we do about this? Several things might spring to mind. Why don't we just bring all the possible rows back from the Database. Then we can just filter out the rows that don't belong when we change products. Well, on the surface this might sound fine. However, after running the filter on the rows we don't want, we'd still get the same behavior shown in Figure 5. Why? Because PowerBuilder only shows descriptions for the rows in the dropdown's primary buffer. When those rows are filtered out, they won't be in the primary buffer, they'll be in the filtered buffer. Therefore, we'll get "bad" behavior from our dropdown.

So, what can we do? All rows for all products must be in the dropdown's primary buffer but when we expand the dropdown, all those extra colors will appear as in the dropdown in Figure 6.  

Figure 6 : Bad Results When Selecting all Rows into DDDW

So how can we make the dropdown not show all of the colors for all of the products? The answer is a function introduced in PowerBuilder 4.0. So what is this grand function? SetDetailHeight(). SetDetailHeight lets you change the detail height of certain rows in your DataWindow. Here's how SetDetailHeight() is declared: 

integer SetDetailHeight( long start, long end, int height )

What we're going to do is set the height of every row in the DDDW that doesn't belong to the current product to 0. This will effectively make those rows invisible. So when the dropdown is expanded, all the user will see are the rows for the current product. The dropdowns in the other rows will still show their descriptions because all of the rows are still in the primary buffer. They're just can't be seen because of the detail height. So all we'll do is change the heights in the dropdown each time the user changes rows or adds/changes the product_id. Now, where are we going to put this code? Here's how I did it:

First of all, we're going to be making extensive calls to the dropdown. Because of this, I decided to create an instance variable to hold the reference to the DDDW. 

instance variable: DataWindowChild idwc

The best place to initialize this idwc variable is in the constructor of the primary DataWindow. Why? Because the variable only needs to be initialized once. 

event: DataWindow::constructor 

// Setup a handle to the color dropdownlistbox 

IF GetChild( "color_id", idwc ) THEN 

  MessageBox( parent.title, "Could not initialize color DDDW") 

END IF

Also, when we retrieve the dropdown, we're going to need to retrieve all rows-not just the ones for a given product. Remember, to have the dropdown's in rows other than the current row show descriptions, you'll have to have all colors in your DDDW. So, how are we going to retrieve all the rows? We'll, when I created the source DataWindow for the dropdown, I used a trick to allow me to get all rows back. Take a look at the SQL Select statement for the DataWindow object d_product_color_list. You'll see the following SELECT: 

  SELECT product_id, color_id, description, 

    FROM valid_product_color, color 

   WHERE ( color.color_id = valid_product_color.color_id ) 

     AND ( ( product_id = :ai_product_id ) 

      OR ( :ai_product_id = -999 ) ) 

ORDER BY product_id ASC, description ASC
Tip: Putting a phrase like ' OR :retrieval_argument = -999 ' allows you to call Retrieve(-999) and retrieve every row in your selection while ignoring other where phrases. I use this trick a lot when I'm developing systems. However, in a performance intensive production system, I'd most likely not have the OR there because it can cause some query optimizers to act strangely and retrieve rows slower than needed. This slowdown is something we should obviously avoid in lengthy or complex queries.

If you call idwc.Retrieve( -999 ), you'll get all the colors for every product. So, let's take a look at the dw_retrieve event for our DataWindow: 

event: DataWindow::dw_retrieve 

idwc.SetTransObject(SQLCA) 

idwc.Retrieve( -999 ) // Load the DDDW with all rows 

Retrieve( 2001 )

When the parent DataWindow is retrieved, the DDDW will be retrieved first. I've got the order number 2001 hardcoded just for sample purposes.

What have we done so far?

So what's left? Well, whenever the row changes, we need to set the detail heights of each unwanted DDDW row to 0. Where can we do this? Well, we've already got a function called wf_get_colors() which is called by our rowfocuschanged event. Why not just change wf_get_colors() to work the way we want and we'll minimize the changes to other code that we've already written.

So, let's take a look at our new wf_get_colors() function on our window: 

public integer wf_get_colors( int ai_product_id ) 

/* wf_get_colors( int ai_product_id ) 

   Populates the DDDW on dw_1 with the valid colors for the product_id. 

   Returns the number of colors found. 

   Assumes that the transaction object has been set. 

*/ 

STRING s_old_filter 

 

IF IsValid (idwc) THEN 

  IF ii_dddw_detail_height = 0 THEN 

    // Set the detail height the first time through 

    ii_dddw_detail_height = Integer( idwc.Describe ( "Datawindow.Detail.Height")) 

  END IF 

 

  idwc.SetRedraw(FALSE) 

  // Reset the height to the default 

  idwc.SetDetailHeight(1, idwc.RowCount(), ii_dddw_detail_height ) 

  // Filter out all rows belonging to the current product 

 

  IF IsNull( ai_product_id ) THEN 

    idwc.SetFilter( '0 = 1' ) // Filter out all rows 

  ELSE 

    s_old_filter = idwc.Describe(Datawindow.Table.Filter)

    idwc.SetFilter("product_id <> " + String( ai_product_id ) ) 

  END IF 

 

  idwc.Filter() 

  // Set the height to all these rows 0 

  idwc.SetDetailHeight( 1, idwc.RowCount(), 0 ) 

  // Reset the filter back to its previous setting. 

  idwc.SetFilter(s_old_filter) 

  idwc.Filter() 

  idwc.SetRedraw(TRUE) 

  RETURN 1 

ELSE // Invalid DWC 

  RETURN -1 

END IF

With this new, improved wf_get_colors() function, we get the behavior we want. When the user clicks on the dropdown, he'll get the colors that go with the current product. And, all the other dropdowns contain the proper description information. Wahoo we're done.

OK, not really.

The only drawback with this method is that although the user will only see the valid colors for a given product when the dropdown is pulled down, the user can still get access to the other values which are not valid by using the up and down arrows. The reason for this is because these values are still in the primary buffer, they just aren't visible in the DataWindow itself. So when the user clicks the up and down arrow, PowerBuilder moves them through the rows in the DDDW one at a time-including the ones that aren't normally visible when the dropdown is expanded.

Here's where we employ some arcane DDDW techniques.

So we're going to take a break from the pesky dropdown problem we have right now and learn something completely different.

Undocumented Events for DDDWs

When you use a DDDW there are only two events that you can normally use with PowerBuilder out of the box. They are: pbm_dwclosedropdown and pbm_dwndropdown. What if you want to know when the user changes rows in your DDDW? Or right clicks on the DDDW? Well, you can't program for the events normally because the DDDW isn't a separate DWC that you place on the window and only the DWC is the only place to put code for a DataWindow. So, how can we overcome this?

The thing that saves us is that as you work with a DDDW, PowerBuilder sends windows events to the primary DataWindow Control. I'm sure PowerBuilder is using these events internally, however, they aren't documented anywhere that mere mortals can find them. In 1995, Mark Brown wrote an article in the PowerBuilder Developer's Journal documenting these DDDW events. Mark discovered that, in PowerBuilder 4.0, PowerBuilder triggers WM_COMMAND events when the DDDW is doing its work. By defining a user event mapped to pbm_command, a programmer can, among other things, determine when the user changes a row in a DDDW.

There's one big catch to using this technique as published, as of PowerBuilder 5.0.2, the pbm_command event does not work. PowerBuilder will actually trigger the event but gives no way to get access to the row or DDDW that is being processed. This appears to be a bug since in every other event, PowerBuilder will pass the needed information. Luckily there is another way: the other event. If PowerBuilder fires an event that isn't coded or defined for a DataWindow, it will fire the "other" event. Since we won't declare anything that handles pbm_command, PowerBuilder will fire the "other" event instead. So we're going to write some code in the "other" event.

About the "other" Event
 
The "other" event fires many times as the user works with the DataWindow. As the user moves the mouse, clicks on the DataWindow, and types things, the other event is fired many, many times. Conventional wisdom in the PowerBuilder community says that putting anything in the "other" event is a severe no-no. This bit of wisdom was originally developed when PowerBuilder was in its 2.0 and 3.0 versions. At that time, the machines we used were 386s and 486s. Today, we're using Pentiums and fast 486s. With care the other event can be used; the trick is to do the minimum amount of work as possible so the system doesn't slow down as the other event is being fired over and over.

Each internal Windows event has two pieces of information called the longparm and wordparm. These are numbers that are used to pass useful information to the event. We're going to use the longparm and wordparm to determine which DDDW is getting action and which event has occured. When the user moves within a DDDW or when the DDDW is performing some action (such as retrieving data, changing RowFocus, etc.) the "other" event will trigger. PowerBuilder will include the DDDW which is currently being processed in the longparm. It includes the action that is being performed in the wordparm.

The longparm and wordparm actually contain two integers each; a high and a low integer. This allows you to actually pass four numbers to an event by combining them. PowerBuilder passes the handle to the DDDW in the Low integer of the longparm and passes the event in the High integer of the wordparm. To get the longparm and wordparm, we'll use two attributes that are passed to the "other" event: lparam and wparam. We'll use the IntHigh and IntLow functions to extract the integers from these arguments.

Here's the code to get the values we need: 

Event: DataWindow::other 

INTEGER i_DWC_Handle, i_DWC_Event 

i_DWC_Handle = IntLow( lparam ) 

i_DWC_Event = IntHigh( wparam )

Here's a list of some of the messages that PowerBuilder uses:

   

Number

Event

Number

Event

768

RetrieveStart

2049

SelectionChanged

769

RetrieveEnd

2311

MouseMove

1024

UpdateStart

2313

LeftButtonUp

1025

UpdateEnd

2314

RightClicked

1280

DoubleClicked

2315

RightButtonUp

1281

Clicked

2317

ScrollHorizontal

1281

RightDoubleClicked

2318

ScrollVertical

1536

DBError

2319

SQLPreview

1792

ItemFocusChanged

2320

Resize

2048

RowFocusChanged

2321

PositionChanged

So, here's how you would capture the RowFocusChanged event in your DDDW for color: 

Event: DataWindow::other 
INTEGER i_DWC_Handle, i_DWC_Event 
i_DWC_Handle = IntLow( lparam ) 
i_DWC_Event = IntHigh( wparam ) 
  
// Make sure that the message received is for the DDDW 
IF i_DWC_Handle = Handle( idwc ) THEN 
  // Determine which event on the DDDW has occured 
  CHOOSE CASE i_DWC_Event 
    CASE 2048 // RowFocusChanged on DDDW 
      l_cur_row = idwc.GetRow() 
  END CHOOSE 
END IF

Notes for PowerBuilder 4.0

You should still be able to use the other event in PowerBuilder 4.0. However, you can also map a user event to pbm_command and get the same results-without having to program the "other" event. The pbm_command event doesn't fire as much as the other event does.

In PowerBuilder 4.0, the longparm and wordparm aren't passed as arguments to events. Instead, you must use the message object to get the long and wordparm. One thing though, in 4.0, you don't need the wordparm because PowerBuilder 4.0 passes the DDDW handle and event in the longparm itself.

To get these two values out of longparm, we'll use the message object since the message object includes the longparm of the current event. A long variable is twice as long as an integer; that is, it contains two integers. PowerBuilder puts the DDDW being worked on in the low integer and the message being sent in the high integer.
 
Here's the code to get those two integers out of the longparm: 

Event: DataWindow::we_command mapped to pbm_command 
INTEGER i_DWC_Handle, i_DWC_Event 
LONG l_longparm 
l_longparm = Message.LongParm 
i_DWC_Handle = IntLow( l_longparm ) 
i_DWC_Event = IntHigh( l_longparm )

Stopping Users from Scrolling to Invalid DDDW Rows

Since we can now tell when the user changes rows on the DDDW, we can write code that determines when the user changes to a row that isn't valid for this occurrence of a product ID. To stop the user from scrolling to a color that isn't valid for the current item, we're going to write code for the RowFocusChanged event of the DDDW. If the user has moved to a row that isn't valid, we'll just move the user back to where he belongs.

For this technique of moving the user back to where he belongs to work, we're going to have to know the direction that the user was moving when the rowfocuschanged. If the current row is less than the previous row then we know the user is moving "up" toward the top of the DataWindow. Otherwise the user is moving down the DataWindow.

To keep track of the previous row, we'll need an instance variable to hold the row number as we move. Set this instance variable equal to the current row of the DDDW when the user enters the DDDW column. Here's the code: 

Instance Variable of Window or DWC: LONG il_prev_dd_row 
Event: DataWindow::itemfocuschanged 
// This code needed to track the previous row the 
// DDDW was on. With it we can determine which direction 
// the user is moving within the DDDW 
  
IF GetColumnName() = "color" THEN 
  il_prev_dd_row = idwc.GetRow() 
END IF

To complete the DDDW example, let's look at the code for the we_command event: 

Event: DataWindow::other 
INTEGER i_DWC_Handle, i_DWC_Event 
i_DWC_Handle = IntLow( lparam ) 
i_DWC_Event = IntHigh( wparam ) 
  
LONG l_cur_row 
LONG l_next_row 
INTEGER i_product_id 
STRING s_find 
  
// Make sure this is only run when the user is in the "color_id" column 
IF This.GetColumnName() <> "color_id" THEN 
  RETURN 
END IF 
  
// Make sure that the message received is for the DDDW 
IF i_DWC_Handle = Handle( idwc ) THEN 
  CHOOSE CASE i_DWC_Event 
    CASE 2048 // RowFocusChanged on DDDW 
    i_product_id = this.GetItemNumber( this.Getrow(), "product_id" ) 
    l_cur_row = idwc.GetRow() 
   
    /* 
      If the current row is for the current product then 
      do nothing except set the last row pointer 
    */ 
  
    IF idwc.GetItemNumber( l_cur_row, "product_id" )= i_product_id THEN 
      il_prev_dd_row= l_cur_row 
      RETURN 
    END IF 
  
    // Set up Find String for product_id search 
    s_find = "product_id = " + string( i_product_id ) 
    /* 
      Determine which direction the user moving: 
      If current > previous row then moving to the end 
      If current < previous row then moving to the top 
    */ 
    IF l_cur_row > il_prev_dd_row THEN 
      // Search Forward 
      l_next_row = idwc.Find( s_find , l_cur_row + 1 , idwc.RowCount() ) 
    ELSEIF l_cur_row < il_prev_dd_row THEN 
      // Search Backwards 
      l_next_row = idwc.Find( s_find,l_cur_row - 1,1 ) 
    ELSE 
      RETURN 
    END IF 
  
    /* 
      If the searched row was not found that means we 
      are at the top or the bottom of the list. 
      Therefore move the user back to the previous row. 
      Otherwise allow the user to move to the next row 
    */ 
    IF l_next_row = 0 THEN 
      idwc.SetRow( il_prev_dd_row ) 
      SetRedraw( TRUE ) 
      RETURN 
    ELSE 
      idwc.Setrow( l_next_row ) 
      SetRedraw( TRUE ) 
    END IF 
  END CHOOSE 
END IF

The code just shown prevents the user from scrolling with the up and down arrow to a value that doesn't match the current item number.

Adding Color to your Dropdown

Most dropdowns tend to be on the boring side-with good reason. Most of the time we just want a simple list from which to choose. However, we've got a list to choose from with colors. Why not show the colors?

Here are the steps taken to add color to the dropdown:

  • The columns, Red, Green, and Blue were added to the color table.
  • The DDDW, d_product_color_list, has the red, green, and blue columns added to the result set. (They are not displayed on the DDDW, however.)
  • A rectangle named 'color_box' was added to the DataWindow.  
  • The brush_color attribute for the color_box was set to the correct RGB value.

 

RGB Color Values

Colors in PowerBuilder (and almost all Windows programs) is set using RGB color values. In Windows a color is nothing more than a long value. For example, black is 0, red is 255, green is 65280, and blue is 16711680. Easy, right? Well, just remember that there is a function called RGB in PowerBuilder which will take values from 0 to 255 for the three color components. Red is RGB( 255, 0, 0 ), Green is RGB( 0, 255, 0 ), and Blue is ( 0, 0, 255 ). Finally, black is RGB ( 0, 0, 0 ) and White is RGB ( 255, 255, 255 ). From here you can choose over sixteen million colors (256 times 256 times 256).

Here's a listing of the color table in the sample:  

Color ID

Description

Red

Green

Blue

W

White

255

255

255

O

Orange

200

40

0

B

Black

0

0

0

G

Green

0

255

0

Y

Yellow

255

255

0

R

Red

255

0

0

P

Pink

232

91

126

Bl

Blue

0

0

255

 

Create a rectangle called color_box to the DataWindow.

 

The third step is to add a rectangle to our color DDDW. Here's what your DataWindow should look like once you've added and sized your rectangle. Name the rectangle color_box.

 

Code the brush color attribute for the color_box to show the correct color.

Now that you've got the rectangle on the DDDW, lets colorize it. To do so, right click on the rectangle and choose attributes. In the attribute for brush_color, enter the following line: 

RGB ( color_red, color_green, color_blue )

That is all there is to adding color.

Speeding up DDDW Usage Through Caching

Dropdowns are very flexible and allow us to create data driven applications. However, they have a down side also: performance. Imagine an order entry form with about ten dropdowns on it. Most of the dropdowns only have few rows on them. Well, each time you bring up an order, these ten dropdowns will need to be repopulated. I can tell you that ten small retrievals on a local SQL Anywhere engine on a fast machine will hardly be noticed. However, those same retrievals over a WAN after lunchtime at the end of the month is going to be quite slow. If one of the prime goals of client/server computing is to reduce the needed network traffic, why then, do we need those ten to fifteen selects for our order entry window?

The answer is that we probably don't.

There's at least two techniques to our rescue. DataWindow sharing and importing strings.

Here's how the sharing technique works:

Sharing Dropdown DataWindows

[Note: If you are using PowerBuilder 5.0, you can use the DataWindow Cache Service of the PowerBuilder Foundation Class Library (PFC) to achieve DDDW Sharing. See our DDDW Extension for the PFC ]

First, we assume that most dropdown data is fairly static throughout an application sessions. Dropdowns are mostly used for code lookups and they don't change much during the execution of an application. Because of these assumptions, we can build a master storage window that will hold the dropdown data throughout the life of the session. When we first retrieve a DDDW, we'll open in a special window. From then on, anytime we use the DDDW we'll just issue a ShareData() against the preloaded DDDW.

Here are the steps needed to use shared dropdowns to speed up our application:

Using this technique, we'll only share dropdowns as we use them. The w_dddw will be completely dynamic and only open the minimum number of DataWindows needed.

Create a reusable user object DataWindow control called u_dw_plain.

Go into the user object painter and choose New. Then choose Visual Standard user object. You'll then be asked the Standard Visual Type; choose DataWindow. Save your DataWindow object as u_dw_plain.

Create a "master" dropdown DataWindow holder. (w_dddw)

Create a new window. Make this window fairly small and set its visible style to FALSE. This window should not appear during normal processing. It will act as a DataWindow storage device. In this new window, create one function: 

/* 
  wf_sharedddw takes a datawindow and column name as arguments. 
  It loops through the available datawindows on the DDDW looking for a match. 
  If it finds one it will share the two datawindows together. If no datawindow is 
  found, then the w_dddw adds a new one to the window using an OpenUserObject() function. 
  The function returns 1 if the two DataWindows are shared successfully. 
  A -1 occurs of there was not a successful share or no rows were found. 
  Return Codes: 
   1 Function performed correctly 
  -1 Column Passed is not a valid DDDW column 
  -2 Cannot perform GetChild on the column 
  -3 Sharing with pre-existing DataWindow failed 
  -4 Failed to Open New User Object DataWindow 
  -5 Sharing with newly created DataWindow failed 
*/ 
  
INTEGER          i, i_num_controls 
STRING           s_dataobject 
DATAWINDOWCHILD  ldw_child 
DATAWINDOW       ldw 
u_dw_plain       ldw_plain 
  
/* 
  You can't just use DataWindowChild.dataobject, this won't work. 
  We have to use the Describe column to determine what the DataWindow is for a given column. 
*/ 
s_dataobject = adw.Describe( as_column + ".dddw.name" ) 
IF s_dataobject = "!" or s_dataobject = "?" THEN 
  RETURN -1 
END IF 
IF adw.GetChild( as_column, ldw_child ) < 0 THEN 
  RETURN -1 
END IF 
  
// Loop through each control, if it is a DataWindow then share 
i_num_controls = UpperBound( control ) 
  
FOR i = 1 TO i_num_controls 
  IF control[i].TypeOf() = DataWindow! THEN 
    ldw = control[i] 
    IF ldw.dataobject = s_dataobject THEN 
      IF ldw.ShareData( ldw_Child ) > 0 THEN 
        RETURN 1 
      ELSE 
        RETURN -3 
      END IF 
    END IF 
  END IF 
NEXT 
  
// No Matching DataWindow was found, add one. 
// Only way to dynamically add a DataWindow Control 
IF OpenUserObject( ldw_plain ) < 0 THEN 
  RETURN -4 
END IF 
  
Control[ i_num_controls + 1 ] = ldw_plain 
ldw_plain.dataobject = s_dataobject 
ldw_plain.SetTransObject( SQLCA ) 
ldw_plain.Retrieve() 
IF ldw_plain.ShareData( ldw_Child ) > 0 THEN 
  RETURN 1 
ELSE 
  RETURN -5 
END IF 
  
RETURN 1

When a window opens, have it register its dropdowns with w_dddw.

This is the easy part! Right before you do your retrieve, just call the function we defined. Here's how we'd do it for the d_sales_order_entry DataWindow included in the sample: 

Event: DataWindow ue_dwretrieve 
IF w_dddw.wf_sharedddw( this, "cust_id" ) < 0 THEN 
  MessageBox( title, "Could not share cust_id.") 
END IF 
  
IF w_dddw.wf_sharedddw( this, "region" ) < 0 THEN 
  MessageBox( title, "Could not share region.") 
END IF 

//... finish normal DataWindow retrieval

That's all there is to it. Now you've got a window that will always keep track of dropdowns. If you want, you can embellish this example with additional functionality such as refresh requests or timed refreshes. For example, you might want a certain dropdown to be re-retrieved if it has been over thirty minutes since the last retrieve.

You can test the w_dddw sample by running the Sales Order Entry sample application. Click the "Show Storage Window" button and type into the DataWindow on the storage window. After you type data in the description, you should see the dropdown data in the open Windows change.

Using ImportString for DDDW Storage

This technique is a variation of the DataWindow sharing technique. The advantage to this technique is that it doesn't involve any invisible windows or multiple DataWindow controls. It generally consumes less memory than the prior technique. However, the drawback is that during runtime, it could, use more memory as well as require more processing time as data is Imported. Let's take a look at the technique.

This technique involves a custom class user object (aka nonvisual.) This object has the same function, uf_sharedddw, that w_dddw had. However, there will be no DataWindow user objects created. Instead, the data for each dropdown will be stored in a string array. Then when a dropdown is needed, the array will be searched. If an entry is found then we use ImportString() to import the data into our dropdown. If an entry is not found then the dropdown is retrieved and the data is exported and stored into the string array.

The custom class object is called unv_sharedddw. Here is the object definition: 

Instance Variables: 
// Holds the dataobject name of the DDDWs 
STRING isa_dataobject[] 
// Holds actual data from the DDDW 
STRING isa_data[] 
// Holds the number of DDDWs loaded 
INTEGER ii_num_dw 
  
Function: integer uf_sharedddw( DataWindow adw, String as_column ) 
/* 
  uf_sharedddw takes a datawindow as an argument. 
  It loops through an internal array of DataWindows that it has data for. 
  If it finds an entry in the array, data is imported into the DDDW. 
  Otherwise a new entry is added and the DDDW is retrieved from the DataBase. 
  Finally, any new DDDWs retrieved are added to the array. 
  A -1 occurs of there was not a successful import. 
   
  Return Codes: 
   1 Function performed correctly 
  -1 Column Passed is not a valid DDDW column 
  -2 Cannot perform GetChild on the column 
  -3 Cannot Import Data 
*/ 
  
INTEGER i 
STRING s_dataobject 
DATAWINDOWCHILD ldw_child 
  
s_dataobject = adw.Describe( as_column + ".dddw.name" ) 
  
IF s_dataobject = "!" or s_dataobject = "?" THEN 
  RETURN -1 
END IF 
  
IF adw.GetChild( as_column, ldw_child ) < 0 THEN 
  RETURN -2 
END IF 
    
// Search the DataWindow Array 
FOR i = 1 TO ii_num_dw 
  IF isa_dataobject[ i ] = s_dataobject THEN 
    ldw_child.ImportString ( isa_data[ i ] ) 
    RETURN 1 
  END IF 
NEXT 
  
// No Matching DataWindow was found, add one. 
  
ii_num_dw = ii_num_dw + 1 
isa_dataobject[ ii_num_dw ] = s_dataobject 
ldw_child.SetTransObject( SQLCA ) 
IF ldw_child.Retrieve() < 0 THEN 
  RETURN -3 
END IF 
isa_data[ ii_num_dw ] = ldw_child.Describe("DataWindow.Data") 
RETURN 1

Of the two approaches, I prefer the Shared DataWindow technique. The reason is that it is completely flexible. Regardless of the retrieval arguments, the our wf_sharedddw function of w_dddw won't ever have to issue a Retrieve() statement. All that happens is that two DataWindow controls are shared. The retrieval can come later. However, in the ImportString() example, a Retrieve() must be performed so the non-visual object can capture the data into the isa_data[] array. I included the ImportString sample to make sure you understood that a DDDW can be used almost like any other DataWindow. One use of ImportString() for DDDWs might be the ability to save lookup data into an INI file. Then when a DDDW is loaded it might go to the local hard disk instead of the network. The bottom line is that the possibilities are endless and limited only by your imagination.

Conclusions

We've seen several techniques involving DropDownDataWindows. We've learned the following: