*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
Breck Carter
Last modified: July 19, 1996
mail to: bcarter@bcarter.com
How do I change the contents of a DropDownDataWindow to show only those values that apply to the current row? I have DDDWs for province and city but when I try to filter the city DDDW to contain only those cities for this row's province, the display values on other rows are also affected.
Folks started asking this question about 5 minutes after Powersoft introduced the DropDownDataWindow feature. The simple answer is "You can't!" Although a DDDW column might appear on each master DataWindow row there is only one single DDDW buffer. Any operation that affects the contents of the DDDW is immediately reflected in all the master rows.
This applies to the SetFilter() and filter() functions: When the filter expression changes, the display values for any rows no longer included in the DDDW are replaced with the underlying data value.
For example, suppose the city DDDW is filtered to include only cities in the province "British Columbia" because that's the province for the current master row. A different master row where the province is Ontario and the city is Toronto will now display the code value (e.g., "T1") instead of the display value "Toronto".
A brute-force workaround involves two DDDW columns based on the same underlying database column. We can't have separate DDDW buffers for each master row at least we can have two separate buffers.
The first city DDDW is display-only (protected) and always includes all the city display values. It is overlaid on top of the second city DDDW which is updateable and has a drop-down arrow showing. It is this second city DDDW that gets filtered based on the current row's province value.
Visually, it works like this: The filtered DDDW comes to the front
whenever the cursor is placed on the city column. For all the other
master rows the protected DDDW comes to the front and displays the
correct city name. Here's what it looks like:
The column displaying "V1" and "V2" is included to demonstrate what's happening under the covers: Those are the LookupDisplay() values for the filtered DDDW. As you can see, when the current province is Ontario all the cities in other provinces are represented by their corresponding codes. These are the display values we're trying to avoid by doing all this work.
Listing 1 shows what happens in the ItemFocusChanged event, and Listing 2 shows that some of the same processing is done in the RowFocusChanged script (all in PowerBuilder 5):
Listing 1: ItemFocusChanged Changes Filtering
integer li_RC
DataWindowChild ldwc_city
string ls_province_code
// Is it one of the city columns in the master DW?
if ( this.GetColumnName() = "city_code" ) &
or ( this.GetColumnName() = "city_code_in_province" ) then
this.SetRedraw ( false )
// Get the current province from the master DW.
ls_province_code = this.GetItemString ( row, "province_code" )
li_RC = this.GetChild ( "city_code_in_province", ldwc_city )
if li_RC <> 1 then
MessageBox ( "Error", "GetChild" )
return
end if
// Change the filter expression on one of the two DDDWs.
li_RC = ldwc_city.SetFilter &
( "province_code = '" + ls_province_code + "'" )
if li_RC <> 1 then
MessageBox ( "Error", "SetFilter" )
return
end if
li_RC = ldwc_city.Filter()
if li_RC <> 1 then
MessageBox ( "Error", "Filter" )
return
end if
// Make sure that DDDW is the current column.
li_RC = this.SetColumn ( "city_code_in_province" )
if li_RC <> 1 then
MessageBox ( "Error", "SetColumn" )
return
end if
this.SetRedraw ( true )
end if
Listing 2: RowFocusChanged Also Changes Filtering
integer li_RC DataWindowChild ldwc_city string ls_province_code this.SetRedraw ( false ) // Get the current province from the master DW. // Change the filter expression on one of the two DDDWs. ls_province_code = this.GetItemString ( currentrow, "province_code" ) li_RC = this.GetChild ( "city_code_in_province", ldwc_city ) if li_RC <> 1 then MessageBox ( "Error", "GetChild" ) return end if // Change the filter expression on one of the two DDDWs. li_RC = ldwc_city.SetFilter ( "province_code = '" + ls_province_code + "'" ) if li_RC <> 1 then MessageBox ( "Error", "SetFilter" ) return end if li_RC = ldwc_city.Filter() if li_RC <> 1 then MessageBox ( "Error", "Filter" ) return end if this.SetRedraw ( true )
The behaviour isn't perfect even with the script shown in Listing 3. The user must click twice on the city DDDW to get it to drop down: One click to get the underlying DDDW to come to the front and another to get the list to appear. It works normally if the user clicks on the drop-down arrow instead of the display value: The two DDDWs are carefully sized and aligned so the arrow attached to the underlying DDDW is always visible. The upper DDDW doesn't have an arrow because it's display-only.
Listing 3: Clicked Script Improves Behaviour
string ls_object long ll_tab_pos // Move the current row if necessary. if row <> this.GetRow() then this.SetRow ( row ) end if // Move the current column from the display-only DDDW // to the updateable DDW if necessary. ls_object = this.GetObjectAtPointer() ll_tab_pos = pos ( ls_object, "~t" ) if left ( ls_object, ll_tab_pos - 1 ) = "city_code" then this.SetColumn ( "city_code_in_province" ) end if
From the user's point of view having separate input fields for province and city ain't too friendly: It's just too easy to change the province, forget to fix the city, and then try to save the data. It isn't programmer-friendly either because now you have to include cross-column validation rules in your program.
An alternative solution is to put the correct combinations of city and
province in a single DDDW and let the user change both values with a
single action. Here's what it might look like:
The new DDDW is based on a join of both city and province tables to get the names and codes. Its buffer contains the city and province codes but they are not used as either display or data values on the master DataWindow. Instead, a third computed column is used for both those purposes:
city_name || ', ' || province_name as city_province
The exact same computed column is included in the master DataWindow select list as well as the city and province codes. The computed column is visible and associated with the new DDDW while the city and province codes are marked as updateable in Rows - Update but are not displayed to the end user.
Whenever the user picks a new value from the DDDW the program must reflect that change in the hidden province and city codes contained in the master DataWindow. This is done in the ItemChange event script shown in Listing 4.
Listing 4: Simpler Solution With ItemChanged
integer li_RC
long ll_master_row
long ll_child_row
DataWindowChild ldwc_city_province
string ls_province_code
// Find the current row in the master DW.
ll_master_row = this.GetRow()
if ll_master_row < 1 then
return
end if
// Is it the DDDW column that's changing?
if ( this.GetColumnName() = "city_province" ) then
// Find the chosen value in the DDDW.
li_RC = this.GetChild ( "city_province", ldwc_city_province )
if li_RC <> 1 then
MessageBox ( "Error", "GetChild" )
return
end if
ll_child_row = ldwc_city_province.GetRow()
if ll_child_row < 1 then
return
end if
// Copy the hidden DDDW column values to the corresponding
// hidden (and updateable) columns in the master DW.
this.SetItem ( ll_master_row, "province_code", &
ldwc_city_province.GetItemString &
( ll_child_row, "province_code" ) )
this.SetItem ( ll_master_row, "city_code", &
ldwc_city_province.GetItemString &
( ll_child_row, "city_code" ) )
end if
This technique is valuable when cascading "intelligent" keys have been used for various tables instead of single-column "surrogate" keys.