filter

Topic group Related topics

An SQL expression that filters out rows that do not match specified criteria.

Property of

Rowset

Description

A filter is a mechanism by which you can temporarily hide, or filter out, those rows that do not match certain criteria so that you can see only those rows that do match. The criteria is in the form of a character string that contains an SQL expression, like the one used in the WHERE clause of an SQL SELECT. Simple comparisons using the basic SQL comparison operators (=, <>, <, >, <=, >=) are supported; other predicates such as BETWEEN, IS NULL, IS NOT NULL and LIKE are not. Multiple comparisons may be joined by AND or OR. For example,

"Firstname = 'Waldo'"

In this case, you would see only those rows in the current rowset whose Firstname field was "Waldo". You can use the rowset’s Filter mode, initiated by calling the beginFilter( ) method, to build the expression automatically, and then apply it with the applyFilter( ) method. The alternative is to assign the character string directly to the filter property.

If the filter expression contains a quoted string that contains an apostrophe, precede the apostrophe with a backslash. Note that the single quote used in SQL expressions for strings and the apostrophe are represented by the same single quote character on the keyboard. For example, if this is the rowset and you want to display rows with the Lastname "O’Dell":

this.filter := "Lastname = 'O\'Dell'"

Setting the filter property causes the row cursor to move to the first matching row. If no rows match the filter expression, the row cursor is moved to the end-of-set; the endOfSet property is set to true.

While a filter is active, the row cursor will always be at either a matching row or the end-of-set. Any time you attempt to navigate to a row, the row is evaluated to see if it matches the filter condition. If it does, then the row cursor is allowed to position itself at that row and the row can be seen. If the row does not match the filter condition, the row cursor continues in the direction it was moving to find the next matching row. It will continue to move in that direction until it finds a match or gets to the end-of-set. For example, suppose that this is the rowset, and you execute the following to your program. If no filter is active, you would move four rows forward, toward the last row:

this.next( 4 )

If a filter is active, the row cursor will move forward until it has encountered four rows that match the filter condition, and stop at the fourth. That may be the next four rows in the rowset, if they all happen to match, or the next five, or the next 400, or never, if there aren’t four rows after the current row that match. In that last case, the row cursor will be at the end-of-set.

In other words, when there is no filter active, every row is considered a match. By setting a filter, you filter out all the rows that don’t match certain criteria.

To clear a filter, you can assign an empty string to the filter property, set the filter equal to null, or call the clearFilter( ) method.

In addition to using an SQL expression, you can filter out rows with more complex code by using the canGetRow event.

Note

When a field's lookupSQL property is set, and that field is referenced in the rowset's filter property, the value being compared by the filter is the field's true value, not the lookup value.