Creating and Setting Up Remote Views

Creating a remote view involves essentially the same steps as creating a local view. The difference is that instead of basing the view on Visual FoxPro tables, you base the view on remote data. Also, you will normally create remote views when you are creating client-server applications.

Tip - Northwind ODBC Data Source: To illustrate the creation of a remote view that accesses remote data, we will use the Northwind Traders MS Access database, which is installed with MS Access. If you have the Northwind Traders database, you can use it to follow along. While you will probably use a different type of remote data when you need to create remote views (SQL Server or Oracle databases, for example), the principles involved are much the same.

Tip - Create Remote Data: It may seem obvious, but the first thing you need to have before you can create a remote view is remote data. See the "Remote Views and Remote Data" section in the Creating a Client-Server Application chapter manual for some ideas on how to create remote data if you don't already have it.

When you create a new remote view, you create it through the VPME Data Builder. The minimum setup required for a remote view in the VPME Data Builder is described below. The steps point out the view and field properties that you should set up before you build an Application Object that uses the remote view.

Ø To create a new remote view

1.   Remote Views Node: On the Data Builder List page select the Remote Views node under the name of the database in which you want to create the view.

2.   New: Click the New Button. VPME displays the Select Connection or Data Source dialog.

Tip: If the database already contains a data source (DSN) that you can use,  select the “Available data sources” option. Then, select the DSN you want. When you click OK, the Visual FoxPro View Designer will open for you to create the remote view.

3.   Create New Connection: With the “Connections” option selected, click the New button to create a new connection to the remote data on which you will base your view. VPME displays the Visual FoxPro Connection Designer.

·       Click the New Data Source button. VPME displays the Create New Data Source wizard. Select System Data Source and click Next.

·       In the list of drivers, select the ODBC driver for the data source you are using. In our example, we are selecting the “Microsoft Access Driver (*.mdb)”. Click Next. Click Finish. VPME displays the ODBC Microsoft Access Setup dialog.

·       Enter a name in the Data Source Name box. Enter a description in the Description box. Click the Database Select button. VFP displays the Select Database dialog. Use the dialog to find and select the remote data database. Click OK to return to the ODBC Microsoft Access Setup dialog. Click OK to close the Setup dialog and return to the Connection Designer.

·       Select your data source name in the Data source combo box. Click the Verify Connection button to test whether you can successfully connect to the data source. If the test works, click OK.

·       Click OK on the Connection Designer. Save the connection with a name.

·       Click OK. VPME closes the Connection Designer and opens the Visual FoxPro View Designer with the Open dialog displayed.

4.   View Designer: Use the View Designer to create the remote view.

5.   Open: Select the tables on which you want to base your new remote view. Click Add for each table. Click close.

6.   Select Fields: Using the Fields page at the bottom of the View Designer, move the fields you want to include in the remote view from the Available fields list to the Selected fields list.

Tip - First Field From Parent Table – Source Table: When you are creating a view from more than one table, the first field in the view should come from the main, or parent, table. VPME can then identify the "source table" of the view as the table containing the first field in the view. Identifying the source table of a view is necessary at various points in a VPME application. It is needed when determining the relationship between a view and another table, view or CursorAdapter.

Tip – Include Primary Keys: Include in the Selected fields list the primary key field for each table that you want to update using the view.

Tip – Add All>> Button: If you use the Add All button to select all of the available fields for the view, move a field in the Selected fields list. If you do not do that and later add a field to the view's source table, you will not be able to reopen the view in the View Designer.

7.   Update Criteria: When you are using a remote view to update a remote table, you need to enter the Update Criteria in the View Designer.

·       Key Field: Click the Update Criteria tab on the View Designer. The first thing you need to do is identify the primary key field for each table you want to update using the view. Click twice in the space underneath the key symbol to the left of each key field. A check mark should appear, and the Update All button should be enabled.

·       Select Fields to Update: If you want to update all the fields in the Selected fields list, click the Update All button. A check mark will appear to the left of all the fields except the field(s) you selected as the key field(s). Click under the pencil symbol to the left of the primary key field(s) so that the primary key field(s) will be updated too.

   If you do not want to update all the fields in the Selected fields list, select the fields you want to update by clicking under the pencil symbol to the left of each field.

·       Send SQL Updates: Click the Send SQL Updates check box.

   You have now set the Update Criteria, and the View Designer Update Criteria page should look like this:

8.   View Filters: If the remote view you are creating (a) will be used as a child view on a One-To-Many or Related Pages form, (b) will be used as an Initial Selected Alias view on a Related Forms parent form, or (c) will be used with the SQL Find Form, you must set filters for the view using certain variables with specific names expected by VPM.

Tip & Instructions: The filters you must set are described in the section View Filters. Following the instructions in the "View filters" section, set up the filters that you determine your view needs. Then, return to step 9 below to complete the creation and set up of your view. 

9.   Name Remote View: Close the View Designer. Save the view and give it a name. Visual FoxPro will create the new view, and VPM Enterprise will add it to the Data Builder.

View Designer Field Properties: While you can enter Field Properties in the View Designer, we recommend that you enter the view field properties in the VPME Data Builder. In particular, set up field validation on the Integrity page in the Data Builder instead of in the View Designer. The Data Builder provides predefined field validation rules that make setting up field validation easier (see Setting Up Field Validation).

However, if you need to use Field Properties outside your VPM Enterprise application, you should enter them in the View Designer. Any Field Properties you enter when you first create the view will be imported into the Data Builder.

10. Required Indexes: Create index tag definitions using the Tags Edit page. See Defining Indexes for Views to learn how to create index tag definitions for views. You need to create index tag definitions for views to make related forms, one-to-many forms and related pages forms based on views work. You can also create index tag definitions for the Toolbar Order control.

    Tip: You must create the indexes described in Required Indexes for Views.

   After you create the required indexes go to step 11.

11. View Properties: Click a view’s Edit page in the Data Builder..

·       Description: Enter a Description for the view.

See Setting View Properties on page 167 for the details on using the Data Builder to set up view properties.

12. Primary Key: Identify the remote view’s primary key index tag. Expand the view’s item list and select the primary key index tag. Click the Edit page. Check the VPM Primary checkbox on the Edit page to designate the tag as the primary key index tag.

13. Referential Integrity:  If the remote view contains a foreign key matching the primary key of another table, view or CursorAdapter, select the field in the treeview list and click the Edit page. Click the Integrity page and set up Referential Integrity validation for the field(s) in the foreign key. VPM Enterprise uses your Referential Integrity validation setup to determine how tables and views are related to each other in an application.  See Referential Integrity for the details.

14. Field Label & Grid Captions: For each view field that will appear on a form or report, select the Edit - Properties page and enter a descriptive label in the Description (Short) and Description (Long) text boxes. Enter the label you want in a grid header in the Description (Short) text box. Enter the descriptive label that you want to appear next to the field on a form in the Description (Long) text box.

More:

Creating, Setting Up and Using Matching Remote Views