A filter can be thought of as a mask over a database table that "filters out" certain records according to criteria you specify by either typing or selecting from a screen offering choices and helping you to build the filter command. Filters can be used to view and work with a subset of records in a database table without seeing the other records, even though they are there.
For a simple example, if I wanted to work with my customers in Hicksville, this filter would do the trick:
SET FILTER TO TOWN = 'HICKSVILLE'
In many cases you needn't actually write these filter commands because (a) many canned filters are already in your filters library, and (b) we will gladly help you construct new filters. Once constructed and tested, new filters join your library for re-use later.
Some filters also serve as models. The above example, to filter customers in Hicksville, is also a generic command that can be used to filter customers in any town you wish, just by replacing the name "HICKSVILLE" with another town name.
Click here to see the syntax of the SET FILTER
The FILTER command syntax is similar to SQL Select, and most FILTER commands can be converted to SQL SELECT commands. The reverse is true only for very simple SQL SELECT commands (because SQL SELECT is much more "powerful" then a FILTER command, with many more options)
A fundamental difference between using a FILTER command and SQL SELECT is that a filter command puts a "mask" over the database so only filtered records are visible - yet these records are actually in the database table - while SQL SELECT creates a temporary file (called a cursor) that contains copies of the selected records. These records may be used for virtually any purpose, but any changes made to these records are temporary only and will disappear when the file is closed.
Normally you would use:
a FILTER command when working with a subset of
your customers database, such as customers in a certain ZIP Code or area,
etc., that you intend to make changes to. This is because using a filter
only masks unwanted records, but access to the underlying database table
is the same as when a filter is not used
SQL SELECT to select records of database tables for query and reporting purposes. This is because selected records are extracted from the database table(s) into a temporary file that is operated on by the processing application, and changes to this temporary file disappear when the file is deleted at the end of the operation.
These are not absolute rules, however. Thanks to the magic of programming, there are operations that use SQL SELECT to create cursors which are accessed by software and in turn may update underlying database records.
These variations on filtering amount to mini-languages, each in their own right, an in-depth explanation of which is beyond the scope of this guide.
See "A research case involving FILTERS and SQL SELECT" for an example of where either command can be used to obtain some needed information.
Filters may perform slowly on a LAN workstation, depending on the complexity of the filter and the size of the underlying database table being filtered.
Excerpted from the topic on "Filter Conditions for Queries and Views" in the VFP guide:
"
In SQL SELECT statements, filter conditions specify filter criteria that records retrieved from a SQL SELECT statement must meet to be included in query or view results. When creating queries and views with SQL SELECT statements to retrieve data, you can use filter conditions in the ON and WHERE clauses to extract only those records that meet the filter conditions. For example, you can use filter conditions to retrieve only those records where a field's values are less than a filter value, such as a date.
You can specify filter conditions similar to specifying join conditions but using filter values from a single table and customize filter conditions using comparison operators to control how those field values compare to those filter values. Join conditions compare a field value in one table to a field value in another other table instead of a filter value in the same table.
For the syntax of filter conditions in SQL SELECT statements, see SELECT - SQL Command.
"