Chapter 22: Filtering, Sorting, and Grouping Rows

Filtering rows

To limit the data that is retrieved from a database, you can use WHERE and HAVING clauses and retrieval arguments in SQL SELECT statements for a DataWindow object. This reduces retrieval time and space requirements at runtime.

However, you might want to further limit the data that displays in a DataWindow object. For example, you might want to:

Retrieve many rows but initially display only a subset—perhaps allowing the user to specify different subsets of rows to display
Limit the data that is displayed using DataWindow expression functions (such as If) that are not valid in the SELECT statement

Using filters

In the DataWindow painter, you can define filters, which will limit the rows that display at runtime. Filters can use most DataWindow expression functions or user-defined functions.

Filters do not affect which rows are retrieved. A filter operates against the retrieved data. It does not re-execute the SELECT statement.

Defining a filter

You define a filter in PocketBuilder in the Specify Filter dialog box.

Figure 22-1: Specifying filters for retrieved data

To define a filter:

In the DataWindow painter, select Rows>Filter from the menu bar.

The Specify Filter dialog box displays.
In the Specify Filter dialog box, enter a boolean expression that PocketBuilder will test against each retrieved row.

If the expression evaluates to TRUE, the row is displayed. You can specify any valid expression in a filter. Filters can use any non-object-level PowerScript function, including user-defined functions. You can paste commonly used functions, names of columns, computed fields, retrieval arguments, and operators into the filter.

International considerations For applications to run the same in any country, filter expressions require U.S. notation for numbers. That is, a comma must always represent the thousands delimiter and a period must always represent the decimal place when you specify expressions in the development environment.

For information about expressions for filters, see the DataWindow Reference in the online Help.
(Optional) Click Verify to make sure the expression is valid.
Click OK.

Only rows meeting the filter criteria are displayed in the Preview view.

Filtered rows and updates Modifications of filtered rows are applied to the database when you issue an update request.

Removing a filter

You can remove filters in the Specify Filter dialog box.

To remove a filter:

Select Rows>Filter from the menu bar.
Delete the filter expression from the Specify Filter dialog box, then click OK.

Examples of filters

Assume that a DataWindow object retrieves employee rows and three of the columns are Salary, Status, and Emp_Lname. Table 22-1shows some examples of filters you might use.

**Table 22-1: Sample filters**
To display these rows	Use this filter
Employees with salaries over $50,000	`Salary > 50000`
Active employees	`Status = 'A'`
Active employees with salaries over $50,000	`Salary > 50000 AND Status = 'A'`
Employees whose last names begin with H	`left(Emp_Lname, 1) = 'H'`

Setting filters in a script

You can use the SetFilter and Filter functions in a script to dynamically modify a filter that was set in the DataWindow painter. For information about SetFilter and Filter, see the DataWindow Reference in the online Help.

View this book as PDF