Creating Indexes for Tables

Select a table in the treeview on the Data Builder List page and click the Designer button to create or modify indexes for the table. VPM Enterprise displays the Visual FoxPro Table Designer. Click the Index tab of the Table Designer to create indexes.

 

VPM Enterprise requires that you create the indexes described in the “Required Indexes for Tables” section below. After you have created the indexes you need, click OK to close the Table Designer and return to the Data Builder.

Required Indexes for Tables

Before we describe the indexes that you must create for each table in your application, let’s review these definitions:

Primary Key

The field or combination of fields that uniquely identifies each record in a table.

Foreign Key

A field or combination of fields in one table whose values are required to match those of the primary key of another table.

For each table you create or add to the Data Builder, you must create the following indexes:

1. Primary Key Index:

Create an index for the table’s primary key. You may want to call the tag  “PrimaryKey”. If you’ve added a table, a primary key index may already exist. 

·       TIP - Compound Primary Keys: If the primary key consists of more than one field, create another index for just the first field in the primary key. This index optimizes the speed at which ProMatrix checks for duplicate primary keys when a new or edited record is saved.

·       TIP - Primary Type Requires Filter: You do not have to select the Primary Type in the Table Designer for the primary key index you create unless you need to use the table outside your VPM Enterprise application. VPM Enterprise will take care of preventing duplicate primary keys when records are added to tables and local views on forms based on the VPM Enterprise Form_Toolbar class. However, if you do create an index with a primary key type in the Table Designer, you need to do the following two things:

·       Enter a filter expression of “NOT DELETED()” to prevent Visual FoxPro from including deleted records in its duplicate primary key check.

·       You must also create a second regular index tag with the same primary key expression but no filter.

2. Child Table / Foreign Key Indexes: 

This section on child table indexes may make more sense to you after you’ve read the Related Forms chapter (see Creating Forms: Related Forms. It’s included here to emphasize that child table indexes require special construction. If you’re not familiar with the term “child table”, see What Is a Parent? What is a Child?.

·       Foreign Key Tag: For every child table, create an index for the foreign key that links child records to the primary key in the child’s parent table. 

Tip: The foreign key tag expression must not contain any other fields but the foreign key fields. You will need the foreign key tag when you set up Referential Validation for a foreign key. See Referential Validation.

·       If Compound Foreign Key: If the foreign key consists of more than one field, also create an index for only the first field in the foreign key.

·       Child Table Indexes Used in Toolbar Order Control: The VPM Enterprise Form Toolbar provides an Order control (see Order) that lets users change the order of the records displayed on a form. The Order control lets users change the record order by selecting from a list of indexes that you choose to display on the Order dialog. See Index Tags and Toolbar Order Control.

    Tip: The indexes you choose to display on the Order dialog for child tables must begin with the foreign key field(s) in the child table that links to the primary key field(s) in the parent table, view or CursorAdapter. The additional fields you include in the index determine the order when the index is selected on the Order dialog.

   For example, let’s say your parent table is a customer table with a customer number field (Cust_No) as its primary key. Let’s say the child table is an invoice table that contains the customer number field (Cust_No) as the foreign key field linking invoice records to customer records. Then, every index that you want to display in the Order dialog for the invoice table must have the Cust_No field as the first field in the index. The remaining fields you include in the index determine the record order when the index is selected on the Order dialog.

Explanation: This requirement for the Order dialog indexes is due to the way VPM Enterprise’s Related Forms engine works. The Related Forms engine uses a child table’s current Order index in a SEEK to link parent records to child records. The Related Forms engine needs the child’s foreign key field(s) at the beginning of the Order indexes to make the SEEK work.

3. DELETED() Function Index: 

By default VPM Enterprise recycles deleted records in tables. That means that when a record is added to a table, VPM Enterprise attempts to find a deleted record to reuse for the new record.  Recycling deleted records helps keep the number of deleted records in a table to a minimum and avoids the need to frequently PACK a table.

For large tables with many thousands of records, it can take some time for VPM Enterprise to find a deleted record after a user clicks the New button on the toolbar.  However, if you create an index for a table based on the DELETED() function, you will optimize the speed at which VPM Enterprise finds deleted records.

Good News: VPM Enterprise automatically creates a DELETED() index tag for you when you create a new table in the Data Builder.

TIP: You should also create an index based on the DELETED() function for tables you add to a project.

4. RECORD_NO Index:

VPME also automatically creates a RECORD_NO index for new tables. This index improves performance when selecting the “Last” (go bottom) Toolbar control for a large table when no index tag is in use (i.e., no order is set).

Tip: You may want to create a RECORD_NO index for your large existing tables. The index expression is “A“.

Update Index Info - Tables

Select a table in the treeview on the Data Builder List Page and click the Edit page. At the bottom of the table’s Edit page you will see an “Update Index Info” button.

If you modify table indexes outside of VPM Enterprise, you need to run the Update Index Info utility to include your modifications in the Data Builder. The Update Index Info utility is the tool to use to update VPM’s index information. This utility pulls information from the Visual FoxPro index files to update the VPME index information table for a selected table or all tables in the Data Builder. See Update Index Info - Tables for more information about this important utility.

More:

Index Tags and Toolbar Order Control