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.

Waving Dead Chickens

Pop Quiz: What is the DataWindow equivalent to the GetRow() function in PowerScript? The answer appears below, but first we return to our regularly scheduled programming.

This tip is called "Waving Dead Chickens" because that's how it sometimes feels when dealing with strange symptoms: They go away after you perform some weird ritual with the keyboard. We're going to delve into the world of PowerBuilder 3 and 4 and (deep breath) what can go wrong with the popular technique of calling SelectRow() in the RowFocusChanged event to highlight the current row in a tabular DataWindow. And then we're going to see a PowerBuilder 5 solution.

For the record, Listing 1 shows a typical RowFocusChanged script that maintains the familiar "blue bar". It simply turns off all row highlighting and then turns it back on again for just the current row.

Listing 1: The Minimalist RowFocusChanged

   // Turn off all highlighting...
   this.SelectRow ( 0, false )
   // ...then highlight current row.
   this.SelectRow ( this.GetRow(), true )

Variations on this theme have been known to get pretty anal: Call GetSelectedRow() to make sure at least one row was highlighted before turning it off, check the return value from GetRow() to make sure it's not zero or -1, and so on. Listing 2 shows such a script; it's certainly robust and paranoid but is of absolutely no help with The Big Problem: row highlighting doesn't change when you want it to, and it sometimes disappears altogether.

Listing 2: Anal Retentive RowFocusChanged

   long ll_current_row
   long ll_selected_row
   ll_current_row  = this.GetRow()
   ll_selected_row = this.GetSelectedRow ( 0 )
   if ll_current_row < 1 then
      if ll_selected_row > 0 then
         // Turn off all highlighting.
         this.SelectRow ( 0, false )
      end if
   else
      // Turn off all highlighting...
      this.SelectRow ( 0, false )
      // ...then highlight current row.
      this.SelectRow ( ll_current_row, true )
   end if

Listing 3 is the standard solution to "highlighting sometimes doesn't change when I click on a different row." If the user clicks on an column with a non-zero tab order, the current row is automatically changed and RowFocusChanged fires. However, if the user clicks on some other part of the row, focus doesn't change and the blue line stays put. Listing 3 forces a call to ScrollToRow() which in turn causes RowFocusChanged to fire.

This script actually handles two situations with one call to Describe(). If the user clicks on a non-editable column, it returns "0". If the user clicks on the dead space between columns, Describe() returns "!" but the Integer() function converts that to zero. It's not an error, it's a feature, but it drives the author of Listing 2 crazy (yes, that's me, Marvin The Paranoid Android... the Paranoid part, certainly not the "brain the size of a planet part.)

Listing 3: Clicked and DoubleClicked

   long ll_row
   ll_row = this.GetClickedRow()
   // Scroll if it's not an editable column.
   if ll_row > 0 then
      if integer ( this.Describe &
         ( "#" &
           + string( this.GetClickedColumn()) &
           + ".TabSequence" ) ) = 0 then
         this.ScrollToRow ( ll_row )
      end if
   end if

So everything's OK, right? Wrong! There are at least three other situations where row highlighting can disappear, and the user must futz around clicking on different rows to get it back. The first occurs when the current row is deleted: zap, it's gone, and so is the blue line.

The second and third problems crop up when very first row is highlighted and Retrieve() is called again, or when the filter expression is changed. If the blue line was somewhere else it moves to row 1, but if it was already on row 1 it disappears. Calling ScrollToRow() sometimes works, sometimes not. Triggering RowFocusChanged also sometimes works, but depending on where it's done you can get an annoying flicker as the blue band appears, disappears and then appears again.

Listing 4 solves these three problems in outline form. Why it should be necessary to perform some of these incantations is beyond me, and after a certain point it ceases to be interesting. It works, let's move on. But not before this Tip: If you really really want to whack a misbehaving DataWindow and start over from scratch, just reassign dw_control.DataObject = "d_object".

Listing 4: Waving Dead Chickens

   1. Problem: Highlighting disappears after
               deleting a row.

     Solution: Post RowFocusChanged after
               calling DeleteRow().

   2. Problem: Highlighting disappears after
               re-retrieving.

     Solution: Call SetRedraw ( false )
               Call Reset()
               Call Retrieve()
               Call SetRedraw ( true )

   3. Problem: Highlighting disappears after
               changing the filter expression.

     Solution: Call SetRedraw ( false )
               Call SetFilter()
               Call Filter()
               Post RowFocusChanged
               Call SetRedraw ( true )

There's a fourth symptom that isn't so much a problem as a misunderstood feature: Sometimes the row highlighting disappears when the DataWindow sort expression is changed. The answer comes from Don Roof of Team Powersoft: "Calling the Sort() function doesn't un-select any rows which are currently selected. Call GetSelectedRow ( 0 ) after Sort() to get the new row number of the highlighted row. Then call ScrollToRow() to scroll the DataWindow to the desired location."

I let Don answer the question because, frankly, I thought Sort() and Filter() did the same thing, namely blow away the row selection. The transient nature of the symptom made it feel like one of the other truly weird problems, when all that was really happening is that sometimes the blue bar would scroll off-screen.

While we're on the subject of sorting, here's a frequently asked question: "How can I sort a DataWindow by the display value of a DropDownDataWindow column rather than the data value?"

There is an elegant solution, but first here's a Brute Force version that works in PowerBuilder 3. Let's say employee.dept_id uses a DropDownDataWindow to show the department names. Add dept_name to the select as follows: SELECT emp_id, employee.dept_id, dept_name FROM employee, department. Don't display this extra column, or put it in the Rows - Update list or anything like that, just sort on it. Give it a DataWindow column name like "hidden_dept_name" and put it in the Rows - Sort list instead of dept_id. When the rows are retrieved they appear in department name order.

That solution breaks down when the user changes a dept_id value and asks for the DataWindow to be re-sorted. The affected row won't move because the hidden_dept_name value hasn't changed. Calling Update() and ReselectRow() before Sort() would do the trick, but it won't let the user cancel changes later.

The ItemChanged script in Listing 5 shows a less dramatic method, one that automatically keeps the hidden_dept_name column up to date. Now when the program calls Sort() again everything will fall into place. Still Brute Force, and far too much code for such a simple request, but life with PowerBuilder 3 is sometimes brutish.

Listing 5: PB3 ItemChanged For Re-Sorting

   integer         li_RC
   string          ls_dept_name
   DataWindowChild ldw_dept_id
   long            ll_row
   if this.GetColumnName() = "dept_id" then
      li_RC = this.dwGetChild &
         ( "dept_id", ldw_dept_id )
      if li_RC <> 1 then
         MessageBox ( "Error", "dwGetChild" )
         halt close
      end if
      ll_row = ldw_dept_id.GetRow()
      if ll_row < 1 then
         MessageBox ( "Error", "GetRow 1" )
         halt close
      end if
      ls_dept_name = ldw_dept_id.GetItemString &
         ( ldw_dept_id.GetRow(), "dept_name" )
      if ls_dept_name = "" then
         MessageBox ( "Error", "GetItemString" )
         halt close
      end if
      ll_row = this.GetRow()
      if ll_row < 1 then
         MessageBox ( "Error", "GetRow 2" )
         halt close
      end if
      li_RC = this.SetItem &
         ( ll_row, "hidden_dept_name", &
           ls_dept_name )
      if li_RC <> 1 then
         MessageBox ( "Error", "SetItem" )
         halt close
      end if
   end if

PowerBuilder 3 does have the LookupDisplay DataWindow function to return the display value of a DropDownDataWindow column, but it isn't much help here because you can't sort on a computed field. Relief came with PowerBuilder 4 where you can simply make hidden_dept_name a computed field with LookupDisplay ( dept_id ) as its expression and put it in the sort list. No more extra column in the select, no more mucking about with SetItem, no more Listing 5.

And that brings us full circle, back to the Pop Quiz: What's the DataWindow equivalent to PowerScript's GetRow()? The answer isn't GetRow(), it's the new CurrentRow() function in PowerBuilder 5!

The two functions called GetRow() are actually completely different. The PowerScript version returns only one value at a time, the current row. When the DataWindow version is used in a row-level expression it returns a different value for each row. And that makes it very hard to do nice row highlighting in PowerBuilder 4, where column backgrounds are set to white for the current row and gray for the others.

There's no good way in PowerBuilder 4 to code the DataWindow expression "if this is the current row then use white, otherwise use gray." Go ahead, try it, you'll start by coding "if GetRow() = GetRow()" and quickly realize there's two meanings to this function and only one is available at a time. Or you'll try calling a global f_get_row() function and discover some really interesting effects when two DataWindows using the same technique are visible at the same time. For example, row highlighting might change on the wrong DataWindow when tabbing between controls or switching between windows.

The new CurrentRow() function in PowerBuilder 5 lets you code a completely straightforward and reliable expression for background.color: if (GetRow()=CurrentRow(), RGB(255,255,255), RGB(192,192,192)). The result is shown in Figure 1, and it looks a lot better than the blue line.

Figure 1: Row Highlighting Via CurrentRow()

The SelectRow() technique isn't perfect, and not just because of the strange behavior discussed earlier. Freeform and tabular formats are actually the same at runtime, and you can easily paint a combination that displays multiple freeform-style rows. The blue line looks absolutely hideous on that kind of layout because it covers the whole row. Even on ordinary tabular displays, a blue line extending from sea to sea is often annoying, and calling SetRowFocusIndicator() to get the BozoHand ain't much better. So congratulations go to Powersoft for fixing the little things, like CurrentRow().