How to: Filter Records in Views
When you access a local or remote data source, you can retrieve potentially large amounts of data. However, you can narrow the scope of the records to only those you need for the view by providing a filter. Filtering records also reduces network traffic when working with remote data and increases the performance of your view.
You can filter records in views by specifying a filter expression. When generating the view, Visual FoxPro evaluates the filter expression and compares the result with values in view fields. If the values meet the requirements of the expression, Visual FoxPro includes the records in the result set. Adding a filter expression adds a WHERE clause to the view's SQL statement.
For example, suppose you want to create a filter that includes only those customers from a certain country. In the following lines of code, the WHERE clause contains a filter expression that selects all customers in a customer table where the country field in the customer table contains the value "Sweden":
SELECT * FROM Customer ;
WHERE Customer.Country = 'Sweden'
This code increases performance by retrieving only those records for a particular country rather than all customers. However, this code also requires that you create a separate view for each country because the actual value is coded into the view's SQL SELECT statement. Instead, you can create a parameterized view which uses a parameter in the filter expression to accept a value for the WHERE clause. For more information, see How to: Create Parameterized Views.
To filter records in a view
Open the view in the View Designer, and click the Filter tab.
In the Field Name list, select one of the following:
- A table field that you want include in the filter expression.
-OR-
<Expression> to open the Expression Builder so you can build a filter expression.
Note
If you are creating a view from a remote data source, the functions available in the Expression Builder reflect the functions supported by that server. For a list of functions that your remote server supports, check your server documentation. Visual FoxPro does not attempt to parse the expressions you specify; instead, it passes them to the remote server.
In the Criteria list, select the comparison operator that you want to use.
Tip
To negate the condition, click the NOT column for the expression.
In the Example box, type the value or expression you want to use to compare the field value with.
For more information, see Filter Tab, Query and View Designers and Expression Builder Dialog Box.