Share via

Creating Local Views

  • Article

Local views use Visual FoxPro SQL syntax to select information from tables or views stored locally on your computer. You can create a local view using the View Wizard, View Designer, or the CREATE SQL VIEW command.

You can also add one or more remote views to a local view, so that you can access information from Visual FoxPro and remote ODBC data sources using the same view. For more information, see Accessing Remote Data with Views.

You can use different methods to create views:

  • Use the Local View Wizard to guide you through the steps of creating views.

  • Use the View Designer to create and customize views quickly.

  • Use SQL SELECT statements, which offer more powerful and flexible ways to generate views using code.

    Note    The tables you want to use must be part of a project or database before you can use the Local View Wizard or View Designer to create a view.

Creating Local Views Using the Local View Wizard

If you want help with creating local views, use the Local View Wizard. The wizard prompts you to select the tables or views from which you want information and creates a query based on your answers to a series of questions. For more information about the wizard, press the F1 key for Help while you are using the wizard or see Local View Wizard.

To create a local view using the Local View Wizard

  1. Open a stand-alone database or, in Project Manager, select a free table, database, or database table.
  2. On the Tools menu, point to Wizards, and click All Wizards.
  3. In the Wizard Selection dialog box, click Local View Wizard, and click OK.
  4. Follow the instructions on the wizard screens.

You can also begin creating a view in the Local View Wizard, save the view, and then use the View Designer to modify it. For more information about the Project Manager, see Project Manager Window.

Creating Local Views Using the View Designer

If you do not need help with creating local views, use the View Designer. The View Designer provides a way for you to search for records that meet specified criteria for retrieving information stored in tables or other views. You can also organize and group the records as needed. For more information about the View Designer, see Query and View Designers.

Selecting Tables for Views

When adding more than one table to your view, you can specify join conditions to modify the scope of the results. Visual FoxPro displays the Join Condition dialog box for you to specify the join condition for each additional table you add to your query. For more information specifying join conditions, see Controlling Record Selection with Joins, Join Conditions for Tables, Queries, and Views, and Join Condition Dialog Box.

To select tables for a local view using the View Designer

  1. Open either a stand-alone database, or, in Project Manager, select a database.

  2. On the File menu, click New, then View, and New File.

  3. In the Add Table or View dialog box, select the table you want to use, and click Add.

  4. When you are finished selecting tables, choose Close.

    After you finish selecting tables, they appear on the View Designer surface.

Instead of using the automatically generated alias when you add a table or view, you can specify an alias for each table you use by typing it in the Alias box that appears in the Add Table or View dialog box.

You can also open the View Designer using the CREATE SQL VIEW command when a database is open or create a view by using the CREATE SQL VIEW command with the AS clause to specify a SQL statement. For more information, see CREATE SQL VIEW Command.

For example, the following code creates a view called product_view and contains all the fields in the products table:

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

When created, the name of the view 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 example, the table name is preceded, or qualified, by the table's database name and an exclamation point (!). If you qualify the table name when you create a view, Visual FoxPro searches for the table in the open database list, including current and previous databases, and in the default search path for the table. If you do not 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 that appears in the Project Manager, the Project Manager opens the database automatically. If you subsequently use a view outside the project, you must open the database or make sure the database is in scope before you can use the view.

Removing and Adding Other Tables

You can remove tables from and add other tables to your view.

To remove a table from and add another table to a view

  1. Select the table you want to remove, and click Remove Table on the View Designer toolbar.
  2. On the Query Designer toolbar, click Add Table, and then select the table or view you want to add.

For more information about adding multiple tables to views, see Creating a Multitable View.

Building Local Views in the View Designer

After selecting the tables you want to use for your local view, you can begin building local views in the View Designer. You can also select tables and create a local view in the Local View Wizard, save the view, and then use the View Designer to modify it.

To build a local view using the View Designer

  1. In the View Designer, click the Fields tab.

  2. In the Available fields list, select a field, and click Add.

    To select multiple fields, press the SHIFT key while selecting fields, and then click Add.

  3. To specify the order in which fields appear in output, click and hold the vertical double arrow button appearing to the left of the field you want to move in the Selected fields list. Move your mouse to position the field the way you want.

You can customize or define your view further in the View Designer by performing the following tasks:

  • Change the join condition of the selected tables in the Join tab.
  • Filter results by field in the Filter tab.
  • Organize results by field in the Order By tab.
  • Group results by field in the Group By tab.
  • Specify update criteria in the Update Criteria tab.
  • Select a different type of output in the Miscellaneous tab.

For more information about customizing your view and using the tabs in the View Designer, see Customizing Views and Query and View Designers.

Adding Comments to Views

You can also add comments or notes about the view to describe the its purpose.

To add a comment to a view

  1. Click the View Designer surface to bring it to the foreground.
  2. On the Query menu, click Comments.
  3. In the Comment box, type comments you want to make about the view.

Comments that you add to a view are stored in the database container (DBC). You can modify comments by editing the DBSETPROP( ) function call appearing in the SQL window following the SQL SELECT statement.

For more information about the SQL SELECT statement generated by a view, see SELECT - SQL Command. For more information about customizing views in the View Designer and using the SQL SELECT command in the SQL window, see Customizing Views and Customizing Views Using SQL SELECT Statements.

Running Local Views

After you finish creating the view, you can run it.

To run a view and view results

  • On the Query menu, click Run Query.

    Visual FoxPro displays the results in a browse window.

See Also

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