_FOXDOC | Hackers Guide to Visual FoxPro 7.0

Set Filter, Set("Filter"), Filter()

SET FILTER lets you hide some of the records in a table based on specific criteria. SET("FILTER") and FILTER() tell you what the current criteria are.

Usage

SET FILTER TO [ lFilterExpression ] [ IN cAlias | nWorkArea ]

All records for which lFilterExpression returns .T. are visible. Those where lFilterExpression evaluates to .F. are hidden. Hidden records are ignored by almost every command. (You can still GO to the record number of a hidden record, but if you then issue BROWSE or EDIT, you won't see the record you expect. More important is that SELECT-SQL ignores filters placed with SET FILTER.)

You must move the record pointer for a filter to take effect. Until you do, the current record is still visible even if it doesn't match the filter condition. We usually use LOCATE with no FOR clause for this, which moves us to the first record that meets the filter condition. (Issuing BROWSE is another way of moving the record pointer, but not usually a good choice in applications.)

In FoxPro 2.x, SET FILTER was one of the few places in FoxPro where you couldn't use a UDF. In VFP, however, you can use your own functions in filter conditions—the function is evaluated for each record in the table to determine whether that record should be visible.

Omit lFilterExpression to turn the filter off and make all records visible.

New in VFP 7 is the ability to specify the work area to which the filter applies. In older versions, you have to SELECT the right area before issuing SET FILTER. Although it looks pretty strange, you can even issue SET FILTER TO IN SomeWorkArea to turn off the filter in a particular work area.

Example

USE Customer * Look at customers in the UK SET FILTER TO Country = "UK" BROWSE

Before Rushmore, you just didn't use filters. They were slooooow. With Rushmore, if you're careful, filters can be a useful tool. For a filter to be optimized, the left-hand side of each comparison must exactly match the key expression of an open index. "Exactly" here really means exactly, not more or less. If the tag is on UPPER(LastName), your filter expression must refer to UPPER(LastName)—a filter on LastName won't be optimized.

We often hear people complaining about filters being slow. After making sure the filter expression is optimizable (by ensuring that tags are available for each part of the expression), the next thing we always check is how they're moving around in the filtered table. Only commands that normally take a FOR clause are optimized by Rushmore when there's a filter in place. This means, in particular, that GO TOP, GO BOTTOM and SKIP aren't optimized. For more on this and some optimizable work-arounds, see "Faster Than a Speeding Bullet" in "Franz and Other Lists."

It's worth mentioning here that, while indexes are essential for optimizing filters, filtered indexes (those created with the FOR clause of INDEX) don't aid in optimization. When such a filter is in control, though, records are hidden just as if a filter were in place.

Usage

cCurrentFilter = SET("FILTER") cCurrentFilter = FILTER( [ cAlias | nWorkArea ] )

Both SET("FILTER") and FILTER() return the current filter expression as a string. This is handy when you want to save it while you set a different filter, then restore it later.

Since FILTER() has the ability to check the filter expression for any work area, we generally prefer it to SET("FILTER").

Example

* Preserve the original filter, if any cCurrentFilter = FILTER() * Look at Customers in France SET FILTER TO UPPER(Country)="FRANCE" * Do some processing here, then restore the filter SET FILTER TO &cCurrentFilter

Although the docs don't say anything about it, SET FILTER is scoped to private data sessions, meaning you can have the same table open in different forms (or different copies of the same form) and specify a different filter in each case. Also undocumented is that you can specify filters for views as well as tables.

In forms and reports, you can handle filters through the Filter property of cursors in the data environment. This is an OOPier approach. On the whole, we prefer to use OOP-based approaches rather than SET commands whenever possible.