How to: Filter Data

You can limit the records that you access to only the data you want by using a filtered index or temporary filter condition. When you create a filtered index, Visual FoxPro creates index keys in the index file for only those records that match the filter expression. Filtered indexes are useful for creating complex filter conditions.

To create filtered index

1. Open the Table Designer to modify the table and choose the Indexes tab.

2. In the Filter box, type a filter expression.

   -OR-

   Build a filter expression by clicking the ellipsis (...) button to the right of the box.

3. Choose OK.

For example, the following filter expression selects only those records for customers in Canada:

customer.country = "Canada"

To create filtered indexes programmatically

• Use the INDEX command and include the FOR clause to specify a filter expression.

For example, suppose you want to prepare a mailing for your company's sales representatives and you want to sort the mailing by country. You might create an index that filters the employee table so that only the records for sales representatives appears, ordered by country and last name. The following code creates a filtered index and displays the filtered data in a browse window:

OPEN DATABASE (HOME(2) + 'Data\TestData')
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.

For more information, see INDEX Command.

Temporary Filters

You can also filter data temporarily without creating a filtered index.

To filter data temporarily

• Use the SET FILTER command.

  SET FILTER is particularly useful when you want to specify a temporary condition that records in a table must meet to be accessed.

In the following example, SET FILTER filters the Customer table to show only the customers in Germany:

OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Customer
SET FILTER TO country = "Germany"
BROWSE

SET FILTER accepts any valid Visual FoxPro logical expression as the filter condition. You can turn off the filter for the current table by using SET FILTER TO without an expression.

After you use SET FILTER, only those 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.

For more information, see SET FILTER Command.

See Also

Tasks

How to: Create Indexes (Visual FoxPro)

How to: Display Records in Descending Order

Other Resources

Working with Table Indexes