Creating Local Views

  • Article

You can create a local view with the View Designer or the CREATE SQL VIEW command.

To create a local view

  1. From the Project Manager, select a database.

  2. Click the plus sign next to the Databases symbol.

  3. Under Databases, select Local Views and choose New.

  4. Choose New View.

  5. In the Add Table or View dialog box, select the table or view you want to use, then choose Add.

  6. After choosing the tables you want in your view, choose Close.

    The View Designer appears, showing the table or view you selected.

  7. In the Fields tab, select the fields you want to see in your view results.

  8. To view the results of your view, choose the Run button. You can also start a view by choosing New from the File menu and choosing View.

    -or-

  • Use the CREATE SQL VIEW command when a database is open to display the View Designer.

    -or-

  • Use the CREATE SQL VIEW command with the AS clause.

For example, the following code creates a view containing all the fields in the products table:

CREATE SQL VIEW product_view AS SELECT * ;
   FROM testdata!products

The new view name appears in the Project Manager. If you open the Database Designer, the view is displayed in the same manner as a table in the schema, with the view name in place of a table name.

In the previous example, the table name is preceded, or qualified, by the name of the table's database and the "!" symbol. If you qualify the table name when you create a view, Visual FoxPro searches for the table both in the open database list, including the current and any non-current databases, and in the default search path for the table.

If you don't qualify a table with a database name in a view definition, the database must be open before you can use the view.

Tip   When you create or use a view in the Project Manager, the Project Manager opens the database automatically for you. If you subsequently use a view outside the project, you must open the database or ensure the database is in scope before you can use the view.

Using a View Wizard

You can also create a local view with a view wizard.

To create a local view with a wizard

  1. In the Project Manager, select a database.
  2. Select Local Views, and choose New.
  3. Choose View Wizard.
  4. Follow the instructions on the wizard screens.

Creating Views with Stored SQL SELECT Statements

You can use macro substitution to store the SQL SELECT statement into a variable which you can call with the AS clause of the CREATE SQL VIEW command. For example, the following code stores a SQL SELECT statement into the variable emp_cust_sql, which is then used to create a new view:

emp_cust_sql = "SELECT employee.emp_id, ;
   customer.cust_id, customer.emp_id, ;
   customer.contact, customer.company ;
   FROM employee, customer ;
   WHERE employee.emp_id = customer.emp_id"
CREATE SQL VIEW emp_cust_view AS &emp_cust_sql

Finding a Table or View in a Database

If your database has many tables and views, you might want to quickly locate a specific table. You can use the Find command to highlight the table or view you want to work with.

To find a table in a database

  • From the Database menu, choose Find Object, and then select the table you want to find in the Find Table or View dialog box.

If you want to show only the tables or only certain views, you can choose to display only tables or certain views.

To display only the tables or views

  • From the Database menu, choose Properties, and then select the appropriate display options in the Database Properties dialog box.

See Also

Creating Views | Modifying, Renaming, and Deleting Views | Creating Queries | Creating a Multitable View | Accessing Remote Data | Displaying Data with Views | Local View Wizard