SetFilter

Description

Specifies filter criteria for a DataWindow control or DataStore.

Applies to

Syntax

integer dwcontrol.SetFilter ( string format )

short dwcontrol.SetFilter ( string format )

number dwcontrol.SetFilter ( string format ) 

Returns

Returns 1 if it succeeds and –1 if an error occurs. If no DataWindow object has been assigned to the DataWindow or DataStore, SetFilter returns –1. The return value is usually not used.

Usage

A DataWindow object can have filter criteria specified as part of its definition. After data is retrieved, rows that do not meet the criteria are immediately transferred from the primary buffer to the filter buffer.

The SetFilter method replaces the existing filter criteria—if any are defined for the DataWindow object—with a new set of criteria. Call the Filter method to apply the filter criteria and transfer rows that do not meet the filter criteria to the filter buffer.

The filter expression consists of columns, relational operators, and values against which column values are compared. Boolean expressions can be connected with logical operators AND and OR. You can also use NOT, the negation operator. Use parentheses to control the order of evaluation.

Sample expressions are:

item_id > 5

NOT item_id = 5

(NOT item_id = 5) AND customer > "Mabson"

item_id > 5 AND customer = "Smith"

#1 > 5 AND #2 = "Smith"

The filter expression is a string and does not contain variables. However, you can build the string in your script using the values of script variables. Within the filter string, string constants must be enclosed in quotation marks (see the examples).

By default, PowerBuilder performs comparisons in dictionary order. For example, the following expression shows all the rows in which column 2 begins with A, a, B or b:

#2 >= 'a' and #2 < 'c'

To perform comparisons in ASCII order, append “\s” to the format string. For example, the following expression shows only rows in which column 2 begins with a or b, because the ASCII values of uppercase letters are lower than the ASCII values of lowercase letters:

#2 >= 'a' and #2 < 'c' \s

The formatting that you enter for numbers and currency in filter expressions display the same way in any country. Changing the regional settings of the operating system does not modify the formatting displayed for numbers and currency at runtime.

If you need to use the % or _ characters as part of the string, you can use the escape keyword to indicate that the character is part of the string. For example, the _ character in the following filter string is part of the string to be searched for, but is treated as a wildcard:

comment LIKE ~'%o_a15progress%~'

The escape keyword designates any character as an escape character (do not use a character that is part of the string you want to match). In the following example, the asterisk (*) character is inserted before the _ character and designated as an escape character, so that the _ character is treated as part of the string to be matched:

comment like ~'%o*_a15progress%~' escape ~'*~' 

To let users specify their own filter expression for a DataWindow control, you can pass a null string to the SetFilter method. PowerBuilder displays its Specify Filter dialog box with the filter expression blank. Then you can call Filter to apply the user’s filter expression to the DataWindow. You cannot pass a null string to the SetFilter method for a DataStore object.

To remove a filter, call SetFilter with the empty string (“”) for format and then call Filter. The rows in the filter buffer will be restored to the primary buffer and positioned after the rows that already exist in the primary buffer.

Examples

Example 1

This statement defines the filter expression for dw_Employee as the value of format1:

dw_Employee.SetFilter(format1)

Example 2

The following statements define a filter expression and set it as the filter for dw_Employee. With this filter, only those rows in which the cust_qty column exceeds 100 and the cust_code column exceeds 30 are displayed. The final statement calls Filter to apply the filter:

string DWfilter2

DWfilter2 = "cust_qty > 100 and cust_code >30"

dw_Employee.SetFilter(DWfilter2)

dw_Employee.Filter( )

Example 3

The following statements define a filter so that emp_state of dw_Employee displays only if it is equal to the value of var1 (in this case ME for Maine). The filter expression passed to SetFilter is emp_state = ME:

string Var1

Var1 = "ME"

dw_Employee.SetFilter("emp_state = '"+ var1 +" '")

Example 4

The following statements define a filter so that column 1 must equal the value in min_qty and column 2 must equal the value in max_qty to pass the filter. The resulting filter expression is:

#1=100 and #2=1000

The sample code is:

integer max_qty, min_qty

min_qty = 100

max_qty = 1000

dw_inv.SetFilter("#1="+ String( min_qty) &

		+ " and #2=" + String(max_qty))

Example 5

The following example sets the filter expression to null, which causes PowerBuilder to display the Specify Filter dialog box. Then it calls Filter, which applies the filter expression the user specified:

string null_str

SetNull(null_str)

dw_main.SetFilter(null_str)

dw_main.Filter()

See also