Combining Views

You can build a view that is based on other views. You might want to do this if you need a subset of the information available in multiple other views, or if you want to combine local and remote data in a single view. A view based on other views, or on a combination of local tables and local or remote views, is called a multitiered view. The view that combines other views is the top-level view. You can have multiple levels of views between the top-level view and the local or remote base tables. When you use a multitiered view, the views on which the top-level view is based and any Visual FoxPro base tables used in the top-level or intermediate-level views appear in the Data Session window. Remote tables do not appear in the Data Session window.

Combining Local and Remote Data in a View

To combine remote and local data in a view

1. Create a remote view and add one or more tables from the remote server.
2. Create a new local view and add the remote view you just created to it.
3. Add the related local table to the view and join them on a common field.
4. Set a filter in the local view and run it.
5. Update the view results to update both the local table and the remote view.
6. Close the local view and then the remote view to update the data on the remote server.

You can also combine local and remote data in a view by creating a new local view based on a local view and a remote view.

To create a view that combines local and remote data

• In the Project Manager, select a database, choose Local Views, and choose New to open the View Designer. Add any combination of tables, local views, and remote views into your view.

  -or-

• Use the CREATE SQL VIEW command.

For example, to create a local view that combines information from the local Employee table and the remote Orders table, you can use the following code:

OPEN DATABASE testdata 
CREATE SQL VIEW remote_orders_view ;
   CONNECTION remote_01 ;
   AS SELECT * FROM orders
CREATE SQL VIEW local_employee_remote_orders_view ;
   AS SELECT * FROM testdata!local_employee_view, ;
   testdata!remote_orders_view ;
   WHERE local_employee_view.emp_id = ;
      remote_orders_view.emp_id

Updating Local and Remote Data in a View

When you update data in a multiple tiered view, the updates go down one level, to the view on which the top-level view is based. If you want to update the base tables on which a multiple tiered view is built, you must issue a TABLEUPDATE command for each view in the structure.

See Also

Working with Views | Creating Rules on View Fields and Rows | Manipulating Offline Data | Creating Queries | Data Session window | Creating Offline Views | Customizing Views