Procedure: Creating a virtual table

To create a virtual table, point to New on the Explorer menu item and choose Table (Virtual).

1. Name the table.

A virtual table is required to have a Virtual Table Name, a Display Name, and a Physical Name. The Virtual Table Name is used in scripts to identify the table. We suggest using underscores rather than spaces in the name. The Display Name is the name shown on screen to the user, and the Physical Name is used when creating a view on a SQL Server.

2. Specify the series.

The table series allows you to group related tables in a Dexterity application together using series categories like Sales, Financial and System. You will use these series groupings when you use the Report Writer and when you set up pathnames.

3. Add member tables.

Select the Tables tab and click the Add button. The Table Lookup window will appear. This window lists the tables available to add to the virtual table. A table can be included in the virtual table only once. The first table is the primary table.

Close the Table Lookup window.

To remove a member table from the list, select the member table on the Tables tab and click the Remove button.

4. Add member fields.

Select the Fields tab in the Virtual Table Definition window. Click the Add button. The Field Lookup window will open and display a drop-down list containing the member tables. The table’s corresponding fields that are available to add are listed in the Fields list. As you add the fields, they are removed from the available list for all tables that contain them. The virtual table can contain only one instance of a field.

Close the Field Lookup window.

To remove a field from the virtual table, select the field in the Fields tab and click the Remove button.

5. Create relationships between member tables.

To create relationships between the member tables, select the Relationships tab.

The Relationships tree view will already contain the primary table. Select the primary table and click the Add button. A drop-down list of the member tables available to relate to the primary table will appear. Select a member table to add. The Relationship Definition window will open. To relate the member table to the primary table, you must complete the fields in the Relationship Definition window.

Specify the relationship key and the join type. Equal joins require that only complete records are included in the virtual table’s result set. Left outer joins do not require all fields to be complete when added to the virtual table. A left outer join will add all of the fields specified by the primary table’s key, whether data exists in the secondary table or not.

The following table lists the icons used to indicate the various types of joins.

Select the Index Segments and Related Field lines. The Index Segment displays all key segments from the secondary table’s key. Click Set. The Related Field Lookup window opens and displays the fields from the primary table that can be joined to the secondary tables’ key. These fields have the same storage size and storage type as the Related Field field. Select the appropriate field and click OK. If there is not a matching field, click Cancel and select another key in the Relationship Key field.

6. Add member keys.

To add member keys, click the Add button on the Keys tab. A drop- down list will appear with available keys to use for the table. All of the keys available to add to the virtual table are keys from the primary table. All of the fields in the keys you select are automatically added to the virtual table if they are not already member fields.

7. Save the virtual table definition.

Click okay to save the virtual table and return to the Resource Explorer. The virtual table will appear with the standard tables.

Documentation Feedback

---