Filtering Data

You can limit the records you access to only the data you want by using a filtered index. When you create a filtered index, only records that match the filter expression are available for display and access.

To filter data using a filtered index

• In the Table Designer, select the Index tab and enter a filter expression in the Filter box for the index you want to filter.

  -or-

1. In the Project Manager, select the table you want to add an index to and choose Modify.

2. In the Indexes tab, create or select an index.

3. In the Filter box, enter a filter expression.

   For example, try creating a filter that selects only the records for customers in Canada:

   customer.country = "Canada"
   

4. Choose OK.

   -or-

• Use the optional FOR clause with the Index command.

If you include the optional FOR clause with the INDEX command, the index file acts as a filter on the table. Index keys are created in the index file for just those records that match the filter expression. For example, if you were preparing a mailing to go to the sales representatives in your company, and you wanted to sort the mailing by country, you could create an index that filtered the employee table so that only the records for sales representatives appeared, ordered by their country and their last name. The following code creates a filtered index and displays the filtered data in a Browse window:

USE employee
INDEX ON country+last_name FOR title = "Sales Representative" ;
TAG reps_cntry
BROWSE

When you view the Browse window, only the sales representatives are shown; the records for other employees do not appear at all in the Browse window.

A filtered index builds an index only for records that match the filter expression.

Filtering Data Temporarily

You can use the SET FILTER Command to filter data temporarily, without building a special filtered index. This command is particularly useful when you want to specify a temporary condition that records in a table must meet in order to be accessible. To turn off the filter for the current table, you can issue SET FILTER TO without an expression. For example, you could issue the following command to filter the customer table to show only the customers in Germany:

USE customer
SET FILTER TO country = "Germany"
BROWSE

The SET FILTER command accepts any valid Visual FoxPro logical expression as the filter condition. Once you issue the SET FILTER command, only the records that satisfy the filter condition are available in the table. All commands that access the table respect the SET FILTER condition. You can set a separate filter for every open table.

See Also

Accessing Records in Descending Order | Enhancing the Efficiency of Indexes | Working with Records | Table Designer | INDEX Command | Creating One Index | Creating Multiple Indexes | Ordering by Multiple Fields | Index Creation for Tables