About filters


Related Topics


What is a filter?

A filter can be thought of as a mask over a database table that "filters through" certain records according to criteria you specify. Filters can be used to view and work with a  subset of records in a database table (usually the customers table) without seeing the non-selected records, even though they are there in the database. .


For a simple example, if I wanted to work with my customers in Hicksville, this filter would do the trick:




Some canned filter commands are available in your filters library. If you have a need for a filter not in the library, write us with a clear understanding of what it is that you want and we'll see what we can do. Once constructed and tested, new filters are added to the library for re-use later.


Some filters also serve as models. The above example, to filter customers in Hicksville, is also a model command that filters on customers in a given town. If you have a similar need but for a different town, you can clone this filter command and change the town name to another.  



Click here to see the syntax of the SET FILTER


The difference between filters and SQL Select

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 the 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, but these records can be changed. SQL SELECT, on the other hand, creates a temporary file (called a cursor) with copies of the selected records. Since these are copies of the selected records, changes made during the session would be lost when the temporary cursor is closed.  


Normally you would use:



These are not absolute rules, however. Thanks to the magic of programming, it's possible to use SQL SELECT to create temporary cursors that can be updated because software will replicate the changes made in the temporary file to the real file, a technique called "SQL Pass-Through".



See "A research case involving FILTERS and SQL SELECT" for an example of where either command can be used to obtain some needed information.



Caution with filters


H2OS customers are cautioned not to make extended use of filters. "Set a filter, use it, then turn it off" Filters have been implemented with the notion they would be used in limited ways for specific operations, but the normal connection to the customers database would be without a filter.  



Performance considerations


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.




Filters used in SQL SELECT commands

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.