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.

Evaluating LookupDisplay

How do I obtain the display value of a DropDownDataWindow column in a script?

PowerScript is only one of several languages that come with PowerBuilder. For example, if you use embedded selects and execute immediate statements you are coding in two slightly different dialects of SQL... you can tell they're different because the rules and limitations are slightly different.

The DataWindow object brings with it a whole other language which is visible in PowerScript when you call functions like SetFilter(), SetValidate() and especially Describe() and Modify(). These functions take string parameters and within those strings you code expressions and commands written in this embedded "DataWindow Language".

There are quite a few functions available in this DataWindow language that cannot be called directly from PowerScript. One of these is LookupDisplay() which returns the display value of a DDDW column. Here's how you can combine LookupDisplay() with Evaluate() in a call to Describe() to get that display value into a script:

   string ls_display

   ls_display = dw_sort.describe &
      ( "evaluate ( 'LookupDisplay ( dept_id )', " &
         + string ( dw_sort.GetRow() ) &
         + " )" )

   MessageBox ( "LookupDisplay", ls_display )

The call to LookupDisplay() provides the column name, and it is coded as a string parameter to Evaluate() to determine the value for a particular column. These nested calls are passed as a yet another string parameter to Describe() which makes the value available in your script.

Assuming the current DataWindow row is 47, the PowerScript parameter passed to Describe() looks like this:

   evaluate ( 'LookupDisplay ( dept_id )', 47 )

The runtime DataWindow engine then calls Evaluate() passing it these parameters:

   LookupDisplay ( dept_id )
   47

LookupDisplay ( dept_id ) is then evaluated for row 47 and it returns the display value which is then passed back by Evaluate() and finally Describe().

Some functions are available both in PowerScript and the DataWindow language. Because one of these is GetRow() it's tempting to eliminate the extra call to String() as follows:

   ls_display = dw_sort.describe &
      ( "evaluate ( 'LookupDisplay ( dept_id )', GetRow() )" )

That doesn't work for two reasons. First, the GetRow() DataWindow function is not the same as the PowerScript version; it returns the row for which an expression is being evaluated instead of the current row. It is used mainly for computed fields whose values are calculated for each and every row instead of just the current one.

The second reason is that Evaluate() doesn't accept an expression as the row number, it wants a simple literal. Even though PowerBuilder 5 provides the new CurrentRow() DataWindow function to work the same way as GetRow() in PowerScript, this expression won't work either:

   ls_display = dw_sort.describe &
      ( "evaluate ( 'LookupDisplay ( dept_id )', CurrentRow() )" )

So we're stuck with calling GetRow() from PowerScript, and converting it to a string, and concatenating it with other strings to build up these function parameters. The result is powerful and useful but it's also one of the most error-prone features when it comes time to code those nested strings. And the new PowerBuilder 5 dw.object.column syntax is no help with functions like LookupDisplay().