Limiting the Scope of a View

Article
10/23/2006

When you access a remote data source, you're accessing potentially massive amounts of data. You can limit the scope of data selected into your view to be just the records you need at a given moment. This reduces network traffic and increases the performance of your view. For example, if you want to view information about customers in a particular country and their orders, you gain performance by downloading only the records for that country, rather than all customers, into your view.

One method you can use to limit the scope of your view is to add a WHERE clause to your view's SQL statement. If you want to look at the records for the customers in Sweden, you would create this SQL WHERE clause for your view:

SELECT * FROM customer ;
   WHERE customer.country = 'Sweden'

This code would effectively limit the scope of your view by downloading records for just the Swedish customers, but it would also require that you create a separate view for each country, because the actual value for one country's customer.country is hard-coded into your view's SELECT statement.

Creating a Parameterized View

You can limit the scope of a view without creating a separate view for each subset of records by creating a parameterized view. A parameterized view creates a WHERE clause in the view's SQL SELECT statement that limits the records downloaded to only those records that meet the conditions of the WHERE clause built using the value supplied for the parameter. This value can be supplied at run time, or passed programmatically to the view.

In the case of the previous example, you can create one view that allows you to download records for any country, simply by entering the country's name as you use the view.

To create a parameterized view

In the View Designer, choose View Parameters from the Query menu.

-or-
Use the CREATE SQL VIEW command with a "?" symbol and a parameter.

The parameter you supply is evaluated as a Visual FoxPro expression and the value is sent as part of the view's SQL statement. If the evaluation fails, Visual FoxPro prompts for the parameter value. For example, if you have the customer table from the Testdata database on a remote server, the following code creates a parameterized remote view that limits the view to those customers whose country matches the value supplied for the ?cCountry parameter:

OPEN DATABASE testdata
CREATE SQL VIEW customer_remote_view ;
   CONNECTION remote_01 ;
   AS SELECT * FROM customer ;
   WHERE customer.country = ?cCountry

You can supply a value for ?cCountry programmatically when you use the view. For example, you could type the following code:

cCountry = 'Sweden'
USE Testdata!customer_remote_view IN 0
BROWSE

Visual FoxPro displays the customer records for Swedish companies in the Browse window for Customer_remote_view.

Tip If your parameter is an expression, enclose the parameter expression in parentheses. This allows the entire expression to be evaluated as part of the parameter.

Prompting for User Input of a Parameter Value

If your parameter is not a variable or expression, you might want to prompt the user to supply the parameter value by using a quoted string as your view parameter. When you create a view parameter using a quoted string after the "?" symbol, Visual FoxPro does not interpret the string as an expression. Rather, you are prompted to enter the value for the parameter at run time. For example, the following code creates a parameterized remote view that prompts the user to supply a value for the ?'my customer id' parameter:

OPEN DATABASE testdata
CREATE SQL VIEW customer_remote_view ;
   CONNECTION remote_01 ;
   AS SELECT * FROM customer ;
   WHERE customer.cust_id = ?'my customer id'
USE customer_remote_view

When you use the view in the previous example, the View Parameter dialog box is displayed.

After you enter a valid customer ID, Visual FoxPro retrieves the record that matches that ID. If you enter the value "ALFKI" in the previous example and then browse Customer_remote_view, you see the customer record displayed in the Browse window.

By using a quoted string as a view parameter, you ensure that Visual FoxPro will always prompt the user for the parameter value.

Limiting the Scope of a View

Creating a Parameterized View

Prompting for User Input of a Parameter Value

See Also

Additional resources