使用数据窗口表达式

The DataWindow has been around since the first version of PowerBuilder and is indeed the reason many shops have chosen PB.

Around for over 10 years, it has remained the most powerful data control in any language. No other language has anything like it - not even Visual Basic or Java. If your application requires a complicated presentation layer containing heavy database transactions, the DataWindow is the control of choice.

As PowerBuilder versions have progressed, so has the functionality of the DataWindow; most PowerBuilder programmers are aware of the basic functionality. This column series will focus on intermediate/advanced uses. Hopefully by the end of this series you'll discover that the DataWindow is even more powerful than you thought.

Using Expressions
Basically, DataWindows are made up of other objects, such as columns, text, and drawing objects such as lines. These objects have properties that can be set either at design time via the DataWindow painter, or at runtime via direct object manipulation or the modify function. When setting a property for an object within a DataWindow, the value of that property will be in effect for every instance of that object, i.e., a change in the text color of a database column object will change the text color for that column for every occurrence within the detail band. No surprise there.

What if you wanted to conditionally change the value of an object's property? For example, a common accounting standard is to change the text color of a numeric column to red only if it's negative. What if you wanted to show a column containing an employee's salary if he or she worked in a specific department, or basically determine if a column is visible based on the value of another DataWindow column? When a value of a DataWindow object is changed conditionally, it's changed with an expression. Expressions can be applied at design time when based on some static criteria, or in a script at runtime when the criteria is dynamic.

Design Time Expressions
The simplest way to apply an expression is in the DataWindow painter. To see what properties for an object can be changed via an expression, look on the property pane after selecting an object within the DataWindow. If the property will accept an expression, a small thumb containing a red icon will appear on the right of the property in question. If the thumb is green it indicates an expression has already been specified for that property.

In Figure 1, an expression will be applied to the bold property of the salary column. With the column selected, navigate to the property pane, choose the font tab, and then click on the expression thumb for the font property. The response window shown in Figure 1 should appear.

Notice how the window heading contains the property in which the expression will be applied. The expression window is divided into three main areas: columns, expression, and functions.

ColumnS
Displays all database columns from the SELECT statement, as well as any computed columns to be evaluated.

Expression
The expression area contains the value or the logic in which the property will be applied. Similar to an IF statement, whatever is supplied in this TextBox must evaluate as a Boolean. Also, valid values for the expression are the same ones that would be relevant for the property in which the expression is being applied. In Figure 1, the Font.Weight property is being changed. A valid value is an integer specifying the weight of the text; 400 for normal or 700 for bold. If we were changing the visible property, valid values would be 0 for invisible and 1 for visible. If we wanted to make this column bold, we could simply add "700" as our expression (this is a valid Boolean value because without an IF statement it will be evaluated as TRUE). If we were to leave out an IF statement, all rows returning from the database would be displayed as bold, which of course is not what we're trying to do. This leads us to the next area - functions.

Functions
The use of functions allows for the Boolean evaluation or some other condition in which an expression will be applied. The functions will differ depending on which kind of DataWindow object is being used. There are dozens of expression functions that are adequately documented in the PowerBuilder help, so I won't provide a detailed explanation. Instead, I'll focus on syntax and implementation.

Boolean Evaluation
The simplest and most common of the Boolean functions is the IF statement.

if( b, t, f )

Quite simply, this statement will evaluate a Boolean condition denoted by b. If the expression is TRUE, the property (or other functions) denoted by t will be applied or executed. If the expression returns FALSE, the property or function denoted by f will be applied or fired. The signature or any DataWindow expression function (Boolean or otherwise) is denoted by these single alphanumeric placeholders. Common placeholder values are listed in Table 1.

To revisit our original objective: to make the salary column bold if the salary is greater than 50,000, the expression would read:

if( salary > 50000 , 700, 400)

Figure 2 shows the expression that would display.

If multiple conditions must be evaluated, the IF function can be nested. For example, let's say the salary column will be bold if the employee earns over $50,000 a year and works in Cambridge. The IF statement can be changed as follows:

if( city = "Cambridge", if( salary >0,700, 400 ), 400 )

Just like a standard PowerScript IF statement, the IF statement, if used as a DataWindow expression, can contain an unlimited number of nested IFs. The amount of nesting is limited only by readability. As you can imagine, this can become unreadable really fast.

When testing the values for multiple columns, it may make more sense to use a CASE statement. As you can imagine, the CASE statement is similar to the CHOOSE CASE that can be used in PowerScript. The syntax for the CASE DataWindow expression is:

case( x when a then b when c then d...else e)

For example, if you want the text in the salary column to be displayed in blue if the employee is "L" (on leave), red if the employee is "T" (terminated), or black for all other statuses, the expression would read:

case( status when "L" then RGB(0,0,255)
when "T"
then RGB(255,0,0)
else (RGB(0,0,0)))

Notice in this example, the function RGB is nested within the expression. Nesting of most functions within expressions is allowed as long as the return data types match. In this example, the text color property is being changed. Since it expects a long (data type), and the RGB function returns a long, RGB can be used.

Don't want to use hard-coded values directly as part of the expression? The Expression Painter supports the use of user-defined global functions. Let's say that the salary column is visible only to someone who works in the same city as the employees in the database. When creating the expression, it's impossible to determine the city of someone who is not yet logged on. This can be solved by creating a function that at runtime will fire and determine the city of whoever is using the system.

// Function f_getaccess
// Determine if a city should be displayed.
If gs_city = "Concord" Then
Return TRUE
Else
Return FALSE
End If

This simple function simply returns a TRUE/FALSE depending on the city. For simplicity, the logged-in user's city is a global string variable. This function would be more powerful if it determined this simple business rule more dynamically, perhaps firing off a stored procedure - you get the picture.

It's easy to plug in this function in the Expression Painter. The expression for the visible property would be:

if( f_getaccess(), 1,0)

The function will allow the DataWindow to display only the salaries for employees who work in Cambridge.

In addition to using functions for the evaluation portion of the expression, they can be used in the value portions as well. The following expression, if applied to the text property, will display salaries over $50,000 in red, and the rest in black.

if( salary > 50000, f_over50k(),f_under50k())

Functions used in expressions can also take arguments. For example, if you needed to create a DataWindow that would highlight (make bold) the employee who earns the highest salary by city, the following global function can be written:

// f_checksal
// as_city is a function argument
decimal{2} ldec_sal
select max(salary)
into :ldec_sal
from employee
where city = :as_city;
Return ldec_sal

Using SQL, this function takes the passed city and queries the database for the largest salary for that city. This decimal value is then passed back to the DataWindow. The DataWindow expression for the bold property of the salary column would read:

if( f_checksal( city ) = salary , 700, 400 )

It's important to point out that when using functions that take arguments, the expression is never evaluated within the DataWindow painter (in plain English - it won't work). To see the result of the expression, the application needs to be run or previewed. The result of this expression is displayed in Figure 3.

Other Evaluations
The examples discussed so far use expressions to affect the appearance of objects within a DataWindow. Expressions can also be used to change DataWindow behavior. For example, usually in retrieval, the user should not be allowed to change the value of a primary key - such as an employee number. However, the user will need to supply a value in this column when adding new records. In this scenario, only existing rows need to be protected. The following expression, when applied to the protect property of a column, will protect only existing rows.

if( isRowNew(), 1,0 )

Runtime Expressions
When an expression can't be determined at design time, it can be applied via PowerScript. Runtime expressions can use the same expression functions that can be specified at design time. What makes runtime expressions especially appealing is that they allow the look and feel of a DataWindow to dynamically change as the user is interacting with it. Also, business rules can be applied on the fly and can be changed depending on some dynamic criteria. Applying expressions within PowerScript has other advantages as well. DataWindow bands, for example, can use expressions but only at runtime. Like design-time expressions, runtime expressions are applied on a row by row basis for DataWindow columns. Unlike design-time expressions, they're not checked by the compiler.

Syntax
The syntax of a runtime expression is a bit daunting, but as you'll soon see after a bit of study it's not so bad. The basic syntax of a runtime expression is the same whether it's applied in a modify statement or manipulates the object directly.

<Default Value><Tab><Expression>

All of the above must be contained within single quotes. If we wanted to change the Font.Weight property for the salary column, the syntax would be:

'400~t if(salary>50000,700,400)'

This syntax can be specified within PowerScript as:

dw_1.Object.Salary.Font.Weight='400~t if(salary>50000,700,400)'
(or)
dw_1.Modify("Salary.Font.Weight='400~t if(salary>50000,700,400)'")

Both statements will do the same thing. The usual rules apply when using a modify function versus direct object manipulation so they won't be discussed here. Some differences that bear repeating follows.

Direct Object Manipulation (aka Dot Notation)

When using direct object manipulation, the compiler ignores everything after the object identifier. If an error exists either in the syntax or by using an incorrect expression, a runtime error will occur.

Only one expression at a time can be applied.

The use of variables or functions within the syntax is not allowed.

Modify Function

The string argument is evaluated at runtime. If there's an error, the function will fail silently.

More than one expression can be applied as long as a space separates them. Also, a modify function performs only one operation on the DataWindow, no matter how many modifications it's performing. When applying more than one modification, the modify function is faster than firing multiple direct object manipulation functions. The following modify statement will change two expressions to the salary column - for the price of one!

dw_1.Modify("Salary.Font.Weight='400~t if(salary>50000,700,400)' " + &
"Salary.Color='255 ~t if(salary>50000,255,0)' ")

 

Since it takes a string as an argument, the modify function allows the use of variables and functions as part of the expression. The following code snippet is valid:

dw_1.Modify("Salary.Font.Weight='400~t
if(salary>50000," + String(ll_font) + ",400)'")

Expressions with Bands
Expressions can also be used to manipulate the appearance of a DataWindow band. Currently, the only two properties that can accept expressions are the color and pointer properties, and like other DataWindow objects, expressions can be determined during design time or at runtime. All rules regarding syntax for previously discussed DataWindow objects apply to bands as well. Although this section deals with band expressions, a more detailed discussion on bands will be included in a later article. DataWindow bands are:

• Detail
• Footer
• Summary
• Header
• Trailer

A commonly used expression changes the color of the band. The following fun expression alternates the color of each detail line from white to gray. To supply an expression to a band during design, simply right-click on it.

if(mod(getrow(),2 ) =0,RGB(255,255,255),RGB(192,192,192))

The resulting color change in the detail band is displayed in Figure 4.

Header and Trailer Bands
Supplying expressions for the detail, summary, and footer bands is relatively straightforward. Header and trailer bands are a bit more difficult. One notable difference is that direct object manipulation cannot be used. Expressions must be specified with a modify function. The expression syntax for header and trailer bands are specified below:

DataWindow.BandName.#.property = expression

where bandname is either "header" or "trailer" and the number of the group you want. When bandname is header or trailer, the group number must exist.

The following design-time expression will change the color of the DataWindow header to red if there are more than 100 rows of data:

if( rowcount() > 100,rgb(255,0,0),rgb(192,192,192 ))

If this code was specified within PowerScript, we would drop the expression into a modify function.

lsdw = "DataWindow.Header.Color ='16777215 ~t if(rowcount() > 100,
" + & "rgb(255,0,0),rgb(192,192,192))'"
lsrc = dw_1.Modify(lsdw)

Notice in this code example that the header number is not supplied. This is because every DataWindow has a default header and footer band. If the DataWindow has groups, the group number must be specified. The following code example is based on a DataWindow that's grouping the rows on the city column. In the trailer band, there's a computed field named city_total. This field sums up all salaries by city. If the summation of the salaries is greater than 100K, the color on the band will change to red.

if(city_total > 100000,rgb(255,0,0),rgb(192,192,192))

This expression shows the correct syntax when placed in the color property of the trailer band. The same expression if used in PowerScript would read:

lsdw = "DataWindow.Trailer.1.Color ='16777215 ~t " + &
"if(city_total > 100000,rgb(255,0,0),rgb(192,192,192))'"
lsrc = dw_1.Modify(lsdw)

Notice how the group number for the trailer band is specified. The resulting DataWindow is displayed in Figure 5.

Expressions with Shapes
The real fun is applying expressions to DataWindow drawing objects - shapes. Changing the appearance and behavior of shapes can really make your DataWindows come alive. Many applications use the SetRowFocusIndicator function to denote the current row in a DataWindow. Placing a drawing object on the left-hand side of the first column, then applying an expression would have a better visual effect.

The following expression is applied to the visible property of an oval object in a DataWindow. Basically, this oval will be visible only when the row is current.

if( currentRow() = getrow() ,1,0)

This could have been supplied in PowerScript as:

lsdw = "Oval1.Visible ='0 ~t if(currentRow() = getrow() ,1,0)'"
lsrc = dw_1.Modify(lsdw)

Pictures can be used to good effect as well. Figure 6 contains a bitmap of a checkmark in the detail band. If the status column in that record is equal to '"L"eave, the checkbox will display. For this to work, the following expression is used in the visible property for the picture control.

if( status = 'L', 1,0 )
In PowerScript the expression can be coded as follows:
lsdw = "p_1.Visible ='0 ~t if(status=~"L~",1,0)'"
lsrc = dw_1.Modify(lsdw)

Notice how double quotes have to be used to denote the string value "A", and tildes have to be used to tell the compiler not to consider the double quotes the start or the end of the entire string argument. The resulting DataWindow is displayed in Figure 6.

I'll wrap up this section on expressions by applying an expression to a DataWindow rectangle object. Starting with the previous example, I'll place a rectangle over the detail band, set its color to white, then send it to the back of the band. Then I'll apply an expression that will make it visible only on the current row, basically acting as a row focus indicator. The DataWindow is displayed in Figure 7.

See how the rectangle works with the oval to denote the current row? The expression is applied to the visible property of the rectangle.

if( getRow() = currentRow(), 1,0 )

Expressions are a valuable tool in DataWindow customization. Whether applied in the DataWindow painter or supplied dynamically at runtime, expressions add muscle to the functionality of the DataWindow while providing subtle pizzazz. The utility of expressions are only limited by your imagination.