How to: Create Parameterized Views
You can filter records for a view without creating separate views for each filter value by creating a parameterized view. A parameterized view uses a SQL SELECT statement containing a WHERE clause that specifies a filter expression with a parameter. The parameter accepts values that can be supplied later by prompting the user or programmatically. Parameter names can be any combination of alphabetic characters, numbers, and single quotes.
To specify a parameter for a filter
Open the view in the View Designer, and click the Filter tab.
On the Filter tab, create a filter or select an existing filter.
In the Example box, type the parameter name.
Tip
To prompt for a parameter value when opening the view, precede the parameter name immediately with a question mark (?). When you open the view, the View Parameter dialog box appears and prompts you for a parameter value.
Note
The parameter name you provide is evaluated as a Visual FoxPro expression. If the evaluation fails, Visual FoxPro prompts for a parameter value. The parameter value supplied is sent to the data source as part of the SQL SELECT statement.
For more information, see How to: Edit Views and Filter Tab, Query and View Designers.
To create a parameterized view programmatically
Use the CREATE SQL VIEW command with the AS clause to specify a SQL SELECT statement.
In the WHERE clause of the SQL SELECT statement, include the filter expression containing the parameter name in the appropriate location.
For more information, see CREATE SQL VIEW Command and SELECT - SQL Command.
For example, the following code opens the sample Northwind database and creates a parameterized view that selects all records in the Customers table where the Country field matches the value supplied for the cCountry parameter:
OPEN DATABASE HOME(2) + "Northwind\Northwind"
CREATE SQL VIEW Customer_Remote_View ;
AS SELECT * FROM Customers WHERE Customers.Country = ?cCountry
When the following code opens the view, Visual FoxPro displays the View Parameter dialog box to prompt for a parameter value. After you type a parameter value, a browse window opens to display the results:
USE Customer_Remote_View
BROWSE
The following code deletes the view from the Northwind database when you are finished:
DELETE VIEW Customer_Remote_View
You can pass the parameter value programmatically instead of prompting for a parameter value. The following code creates the same parameterized view but omits the question mark. The code later stores the value "Sweden" programmatically in cCountry as the parameter value. When you open the view, the parameter value is passed to the view, and a browse window opens to display the results:
OPEN DATABASE HOME(2) + "Northwind\Northwind"
CREATE SQL VIEW Customer_Remote_View ;
AS SELECT * FROM Customers WHERE Customers.Country = cCountry
cCountry = 'Sweden'
USE Northwind!Customer_Remote_View
BROWSE
The following code deletes the view from the Northwind database when you are finished:
DELETE VIEW Customer_Remote_View