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

Related Topics: PowerBuilder

PowerBuilder: Article

Filtering Grid Lines

Filtering Grid Lines

p> Have you ever used a grid DataWindow to maintain a table in which columns use DropDownDataWindows for selection, but one column's choices are dependent on another? It's a pretty common scenario, but one that's not so simple to code.

For example, let's assume we have a state table (see Table 1) and a city table (see Table 2) in our database, both using code to represent their textual values. My two DropDownDataWindows are coded to use a Display column of city or state, and a Data column of code.

Table 1 Table 2

What's required in the application, of course, is that when you change the State column, the City column shows only cities within that state (see Figure 1).

Figure 1
Figure 1:

At first glance it looks pretty simple. My first thought was to put some code on the ItemChanged event for the State column, to do a filter on the city DropDownDataWindow. A different approach would be to do a retrieve on the city DropDown when the state changes. However, both techniques yield the same problem: the filter/retrieve approach has the unwanted side effect of changing the City column display to the numeric code value when the value isn't in the city DropDown. For example, using the filter approach, if I was on the xyz row and changed the state field to Georgia, the rows with other states (e.g., Texas/California) would change to the number (the Code column) since the value doesn't exist anymore (it's filtered).

I found an elegant way to make this work that doesn't require much code. First, I coded my city DropDownDataWindow to retrieve all cities, regardless of the state. I then put some code on both the ItemChanged and the RowFocusChanged events to make things work.

Don't Filter...Hide!
Instead of performing a filter, I used the SetDetailHeight function. I wrote a function that loops through the city DropDown and hides all columns that don't match the selected state. This function is called whenever the state field and the row change in the main DataWindow. Setting the detail height to zero hides the row. The constant value DEFAULT_HEIGHT matches the value of the DataWindow so all visible rows have a uniform height (see Listing 1).

Now, simply add some code to the ItemChanged and the RowFocusChanged events (see Listing 2).

When the operator selects a new state, we call the function to hide (nonmatching) cities. We set the City column to null, which clears the field. It's also necessary to set the row in the city DropDown to guarantee that the command event fires in every case.

event rowfocuschanged;
IF currentRow < 1 THEN return
// hide cities when row changes
end event

The RowFocusChanged code ensures that the city list is conditioned when a new row is selected.

Almost But Not Quite
As with many approaches, what we've seen so far handles most cases, but not all. The problem is that if the user chooses to use the arrow keys to scroll through the rows, the hidden rows still show up. This seems strange since the detail height equals zero, but that's the way it works.

To resolve this issue we must do some tricks. The strategy is to detect RowFocusChanged event on the city child DataWindow and skip any rows that shouldn't be seen as the user scrolls through. It's widely known that the DataWindow command event triggers when a child DataWindow changes rows. By looking at the notification code (an argument to the command event), you can detect several otherwise undetectable events. To do this, create a Standard Visual User Object of type DataWindow and add a user event that's linked to pbm_command (I called mine ue_command). If you already have an ancestor DataWindow control, just add the user command event to it. Then place the user object on your form instead of a DataWindow control, and code the ue_command event (see Listing 3).

The ue_command event is triggered when things happen on the child DataWindow. We first check the "notificationcode" argument to the event to determine if it's what we're looking for. An argument value of 2048 indicates the row has changed. We also condition the code based on the column we're interested in. When triggered, we know the user has changed rows using the keyboard or mouse. Next, we loop through the DropDownDataWindow, looking for a city that's in the currently selected state. When we find one, we scroll to the row and return. If we don't find one, we set the row back to the last one that matched, saved in il_lastrow, causing the row not to change.

A possible downside to this approach is that if the second DataWindow (city in my case) has lots of rows, you'll retrieve them all even if you don't need them. This is easily resolvable by retrieving cities as needed.

The SetDetailHeight function can be used to hide rows in a DataWindow or a DropDown simply by setting the value to zero. I've found this to be a useful method of hiding rows without using the filter command. This may also be your first exposure to the command event on DataWindows, which has useful functionality as well.

More Stories By Dave Alessi

Dave Alessi is the founder of a commercial software company that develops and sells project management tools developed with
PowerBuilder and PowerJ. He has more than seven years experience developing with PowerBuilder, and more than 25 years of industry experience.

Comments (0)

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.