Chapter 7 -- Working with Tables

Building your database includes creating tables. When you designed your database, you specified the table fields and relationships needed for your application. Now, as you create those tables, you make more detailed choices about the data types, captions, and potential default values for each field, the triggers for each table, as well as the table indexes you build to establish relationships between tables. This chapter describes the process of creating, refining, and relating tables and indexes as you develop an application. It focuses primarily on using the language to work with tables and records, but also explains using the interface to handle common tasks.

This chapter discusses:

• Creating Tables
• Working with Records
• Indexing Tables
• Using Multiple Tables

Creating Tables

You can create a table in a database, or just create a free table not associated with a database. If you put the table in a database, you can create long table and field names for database tables. You can also take advantage of data dictionary capabilities for database tables, long field names, default field values, field- and record-level rules, as well as triggers.

Designing Database vs. Free Tables

A Visual FoxPro table, or .dbf file, can exist in one of two states: either as a database table (a table associated with a database) or as a free table that is not associated with any database. Tables associated with a database have several benefits over free tables. When a table is a part of a database you can create:

• Long names for the table and for each field in the table.
• Captions and comments for each table field.
• Default values, input masks, and format for table fields.
• Default control class for table fields.
• Field-level and record-level rules.
• Primary key indexes and table relationships to support referential integrity rules.
• One trigger for each INSERT, UPDATE, or DELETE event.

Some features apply only to database tables. For information about associating tables with a database, see Chapter 6, Creating Databases.

Database tables have properties that free tables don t.

You can design and create a table interactively with the Table Designer, accessible through the Project Manager or the File menu, or you can create a table programmatically with the language. This section primarily describes building a table programmatically. For information on using the Table Designer to build tables interactively, see Chapter 2, Creating Tables and Indexes, in the User s Guide.

You use the following commands to create and edit a table programmatically:

Commands for Creating and Editing Tables

ALTER TABLE	CLOSE TABLES
CREATE TABLE	DELETE FILE
REMOVE TABLE	RENAME TABLE
DROP TABLE

Creating a Database Table

You can create a new table in a database through the menu system, the Project Manager, or through the language. As you create the table, you can create long table and field names, default field values, field- and record-level rules, as well as triggers.

To create a new database table

• In the Project Manager, select a database, then Tables, and then New to open the Table Designer.

  -or-

• Use the CREATE TABLE command with a database open.

For example, the following code creates the table smalltbl with one column, called name:

OPEN DATABASE Sales CREATE TABLE smalltbl (name c(50)) 

The new table is automatically associated with the database that is open at the time you create it. This association is defined by a backlink stored in the table s header record.

Creating a Free Table

A free table is a table that is not associated with a database. You might want to create a free table, for example, to store lookup information that many databases share.

To create a new free table

• In the Project Manager, select Free Tables, and then New to open the Table Designer.

  -or-

• Use the FREE keyword with the CREATE TABLE command.

For example, the following code creates the free table smalltbl with one column, called name:

CLOSE DATABASES CREATE TABLE smalltbl FREE (name c(50)) 

If no database is open at the time you create the table, you do not need to use the keyword FREE.

Naming a Table

When you issue the CREATE TABLE command, you specify the file name for the .dbf file Visual FoxPro creates to store your new table. The file name is the default table name for both database and free tables. Table names can consist of letters, digits, or underscores and must begin with a letter or underscore.

If your table is in a database, you can also specify a long table name. Long table names can contain up to 128 characters and can be used in place of short file names to identify the table in the database. Visual FoxPro displays long table names, if you ve defined them, whenever the table appears in the interface, such as in the Project Manager, the Database Designer, the Query Designer, and the View Designer, as well as in the title bar of a Browse window.

To give a database table a long name

• In the Table Designer, enter a long name in the Table Name box.

  -or-

• Use the NAME clause of the CREATE TABLE command.

For example, the following code creates the table vendintl and gives the table a more understandable long name of vendors_international:

CREATE TABLE vendintl NAME vendors_international (company C(40)) 

You can also use the Table Designer to rename tables or add long names to tables that were created without long names. For example, when you add a free table to a database, you can use the Table Designer to add a long table name. Long names can contain letters, digits, or underscores, and must begin with a letter or underscore. You can t use spaces in long table names.

Renaming a Table

You can rename database tables through the interface because you are changing the long name. If you remove the table from the database, the file name for the table retains the original name. Free tables do not have a long name and can only be renamed using the language.

To rename a table in a database

1. In the Database Designer, select the table to rename.
   
   
2. From the Database menu, choose Modify.
   
   
3. In the Table Designer, type a new name for the table in the Table Name box on the Table tab.

To rename a free table

• Use the RENAME command.

  Caution   If you use the RENAME command on tables associated with a database, the command does not update the backlink to the database and can cause table access errors.

Deleting a Database Table

If a table is associated with a database, you can delete the table as a by-product of removing the table from its database. Deleting a table is different from removing a table from a database, however. If you just want to remove a table from a database but do not want to physically delete the table from disk, see Removing a Table from a Database in Chapter 6, Creating Databases.

To delete a database table from disk

• In the Project Manager, select the table name, choose Remove, and then choose Delete.

  -or-

• From the Database Designer, select the table, choose Remove from the Database menu, and then choose Delete.

  -or-

• To delete the table plus all primary indexes, default values, and validation rules associated with the table, use the DROP TABLE command.

  -or-

• To delete just the table file (.dbf), use the ERASE command.

  Caution   If you use the ERASE command on tables associated with a database, the command does not update the backlink to the database and can cause table access errors.

The following code opens the database testdata and deletes the table orditems and its indexes, default values, and validation rules:

OPEN DATABASE testdata DROP TABLE orditems 

If you delete a table using the DELETE clause of the REMOVE TABLE command, you also remove the associated .fpt memo file and .cdx structural index file.

Deleting a Free Table

If a table is not associated with a database, you can delete the table file through the Project Manager or with the DELETE FILE command.

To delete a free table

• In the Project Manager, select the free table, choose Remove, and then choose Delete.

  -or-

• Use the DELETE FILE command.

For example, if sample is the current table, the following code closes the table and deletes the file from disk:

USE DELETE FILE sample.dbf 

The file you want to delete cannot be open when DELETE FILE is issued. If you delete a table that has other associated files, such as a memo file (.fpt) or index files (.cdx or .idx), be sure to delete those files as well. For example, if the file sample.dbf also has an associated memo file, you could delete both files with the following commands:

USE DELETE FILE sample.dbf DELETE FILE sample.fpt 

Duplicating a Table

You can make a copy of a tables structure, its stored procedures, trigger expressions, and default field values by using the language. There is no menu option to perform the same function. This procedure does not copy the contents of the table.

To duplicate a table

1. Open the original table.
   
   
2. Use the COPY STRUCTURE command to make a copy of the original table.
   
   
3. Open the empty table created with the COPY STRUCTURE command.
   
   
4. Use the APPEND FROM command to copy the data from the original table.

Copying and Editing Table Structure

To modify the structure of an existing table, you can use the Table Designer or ALTER TABLE. Alternatively, you can create a new table based on the structure of an existing table, then modify the structure of the new table.

To copy and edit a table structure

1. Open the original table.
   
   
2. Use the COPY STRUCTURE EXTENDED command to produce a new table containing the structural information of the old table.
   
   
3. Edit the new table containing the structural information to alter the structure of any new table created from that information.
   
   
4. Create a new table using the CREATE FROM command.

   The new table is empty.

5. Use APPEND FROM or one of the data copying commands to fill the table if necessary.

Saving a Table as HTML

You can use the Save As HTML option on the File menu when you are browsing a table to save the contents of a table as an HTML (Hypertext Markup Language) file.

To save a table as HTML

1. Open the table.
   
   
2. Browse the table by issuing the BROWSE command in the Command window or by choosing Browse from the View menu.
   
   
3. Choose Save As HTML on the File menu.
   
   
4. Enter the name of the HTML file to create and choose Save.

Creating Fields

When you create table fields, you determine how data is identified and stored in the table by specifying a field name, a data type, and a field width. You also can control what data is allowed into the field by specifying whether the field allows null values, has a default value, or must meet validation rules. Setting the display properties, you can specify the type of form control created when the field is added onto a form, the format for the contents of the fields, or the caption that labels the content of field.

Note   Tables in Visual FoxPro can contain up to 255 fields. If one or more fields can contain null values, the maximum number of fields the table can contain is reduced by one, from 255 to 254.

Naming Fields

You specify field names as you build a new table. These field names can be 10 characters long for free tables or 128 characters long for database tables. If you remove a table from a database, the table s long field names are truncated to 10 characters.

To name a table field

• In the Table Designer, enter a field name in the Name box.

  -or-

• Use the CREATE TABLE command or ALTER TABLE command.

For example, to create and open the table customer with three fields, cust_id, company, and contact, you could issue the following command:

CREATE TABLE customer (cust_id C(6), company C(40), contact C(30)) 

In the previous example, the C(6) signifies a field with Character data and a field width of 6. Choosing data types for your table fields is discussed later in this section.

Using the ALTER TABLE command, you add fields, company, and contact to an existing table customer:

ALTER TABLE customer ;    ADD COLUMN (company C(40), contact C(30)) 

Using Short Field Names

When you create a table in a database, Visual FoxPro stores the long name for the table s fields in a record of the .dbc file. The first 10 characters of the long name are also stored in the .dbf file as the field name.

If the first 10 characters of the long field name are not unique to the table, Visual FoxPro generates a name that is the first n characters of the long name with the sequential number value appended to the end so that the field name is 10 characters. For example, these long field names are converted to the following 10-character names:

Long Name	Short Name
customer_contact_name	customer_c
customer_contact_address	customer_2
customer_contact_city	customer_3
...	...
customer_contact_fax	customer11

While a table is associated with a database, you must use the long field names to refer to table fields. It is not possible to use the 10-character field names to refer to fields of a table in a database. If you remove a table from its database, the long names for the fields are lost and you must use the 10-character field names (stored in the .dbf) as the field names.

You can use long field names composed of characters, not numbers, in your index files. However, if you create an index using long field names and then remove the referenced table from the database, your index will not work. In this case, you can either shorten the names in the index and then rebuild the index; or delete the index and re-create it, using short field names. For information on deleting an index, see Deleting an Index later in this chapter.

The rules for creating long field names are the same as those for creating any Visual FoxPro identifier, except that the names can contain up to 128 characters.

For more information about naming Visual FoxPro identifiers, see Creating Visual FoxPro Names.

Choosing Data Types

As you create each table field you also choose a data type for the data the field is to store. When you choose a field s data type, you re deciding:

• What kind of values to allow in the field. For example, you can t store text in a Numeric field.
• How much storage space Visual FoxPro is to set aside for the values stored in that field. For example, any value with the Currency data type uses 8 bytes of storage.
• What types of operations can be performed on the values in that field. For example, Visual FoxPro can find the sum of Numeric or Currency values but not of Character or General values.
• Whether Visual FoxPro can index or sort values in the field . You can t sort or create an index for Memo or General fields.

  Tip   For phone numbers, part numbers, and other numbers you don t intend to use for mathematical calculations, you should select the Character data type, not the Numeric data type.

To choose a data type for a field

• In the Table Designer, choose a data type from the Type list.

  -or-

• Use the CREATE TABLE command.

For example, to create and open the table products with three fields, prod_id, prod_name, and unit_price, you could issue the following command:

CREATE TABLE products (prod_id C(6), prod_name C(40), unit_price Y) 

In the previous example, the Y after the unit_price field name specifies a Currency data type.

For more information about specific data types, see Data and Field Types.

Adding a Regular Index Quickly

As you add a field, you can quickly define a regular index on the field by specifying ascending or descending in the Index column of the Table Designer. The index you create is automatically added to the Index tab and uses the field as the expression. To modify the index, you can switch to the Index tab to change the index name, type, or to add a filter.

Using Null Values

As you build a new table, you can specify whether one or more table fields will accept null values. When you use a null value, you are documenting the fact that information that would normally be stored in a field or record is not currently available. For example, an employee s health benefits or tax status may be undetermined at the time a record is populated. Rather than storing a zero or a blank, which could be interpreted to have meaning, you could store a null value in the field until the information becomes available.

To control entering null values per field

• In the Table Designer, select or clear the Null column for the field.

  When the Null column is selected, you can enter null values in the field.

  -or-

• Use the NULL and NOT NULL clauses of the CREATE TABLE command.

For example, the following command creates and opens a table that does not permit null values for the cust_id and company fields but does permit null values in the contact field:

CREATE TABLE customer (cust_id C(6) NOT NULL, ;    company C(40) NOT NULL, contact C(30) NULL) 

You can also control whether null values are permitted in table fields by using the SET NULL ON command.

To permit null values in all table fields

• In the Table Designer, select the Null column for each table field.

  -or-

• Use the SET NULL ON command before using the CREATE TABLE command.

When you issue the SET NULL ON command, Visual FoxPro automatically checks the NULL column for each table field as you add fields in the Table Designer. If you issue the SET NULL command before issuing CREATE TABLE, you don t have to specify the NULL or NOT NULL clauses. For example, the following code creates a table that allows nulls in every table field:

SET NULL ON CREATE TABLE test (field1 C(6), field2 C(40), field3 Y) 

The presence of null values affects the behavior of tables and indexes. For example, if you use APPEND FROM or INSERT INTO to copy records from a table containing null values to a table that does not permit null values, then appended fields that contained null values would be treated as blank, empty, or zero in the current table.

For more information about how null values interact with Visual FoxPro commands, see Handling Null Values.

Adding Comments to Fields

After you create a table in an open database, you can add a description of each table field to make your tables easier to understand and update. Visual FoxPro displays a field s comment text in the Project Manager when you select the field in the list of fields for the table.

To add a comment to a field in a database table

• In the Table Designer, enter the text for your comment in the Field Comment box.

  -or-

• Use the DBSETPROP( ) function.

For example, you might want to clarify what is stored in the unit_price field in your orditems table by entering Current retail price per unit as comment text for the field:

?DBSETPROP('orditems.price', 'field', 'comment', ;           'Current retail price per unit ) 

For more information on using DBSETPROP( ) to set properties on database table fields, see Chapter 6, Creating Databases.

Creating Default Field Values

If you want Visual FoxPro to fill the contents of a field automatically as you add a new record, you can create a default value for the field. The default value is applied whether you enter data through a form, in a Browse window, a view, or programmatically, and remains in the field until you enter a new value.

You create default values either through the Table Designer or through the language. You can specify default values for any data type except General.

To assign a default value to a database table field

• In the Table Designer, enter the value in the Default value box in the Field validation area.

  -or-

• Use the DEFAULT clause of the CREATE TABLE command.

For example, you might want your application to limit the amount of merchandise a new customer can order until you ve had time to complete a credit check and determine the amount of credit you re willing to extend to that customer. The following example creates a maxordamt field with a default value of 1000:

CREATE TABLE customer (cust_id C(6), company C(40), contact C(30), ;                 maxordamt Y(4) DEFAULT 1000) 

If your customer table already included the maxordamt column, you could add a default value for the column with this command:

ALTER TABLE customer ALTER COLUMN maxordamt SET DEFAULT 1000 

Using Default Values to Speed Data Entry

You can use default values to speed data entry for your application s users, enabling them to skip a field unless they want to enter a different value. For example, if your business primarily deals with domestic customers, you may want the country field in the customer table in a database to be filled with the name of your country automatically. If you re entering a customer record for an international customer, you can then overwrite the name of your country with their country name.

Tip   If one of your application s business rules requires that a field contain an entry, providing a default value helps to ensure that a particular field-level or record-level rule will not be violated.

If you remove or delete a table from a database, all default values bound to that table are deleted from the database. Stored procedures referenced by the removed or deleted default value remain even after the default value has been removed.

When you don t specify a default value, a blank value (as defined for each data type) is inserted unless SET NULL is on. This preserves backward compatibility with any existing FoxPro code you might have.

You can use .NULL. as a default value if you want the field to use null values. Whether SET NULL is on or off, if you use .NULL. as a default value, Visual FoxPro inserts .NULL. for all commands except APPEND BLANK.

Allowable Default Values

You can specify default values that are either scalar values (such as a number ) or expressions that evaluate to a scalar quantity. You can also specify any valid Xbase expression that returns a value consistent with the data type for the field.

Visual FoxPro evaluates expressions for data type when the table structure is closed. If the data type doesn t match the associated field type, Visual FoxPro generates an error. If the expression is a user-defined function (UDF) or contains a UDF, it is not evaluated.

When you create the default value through the language, the CREATE TABLE or ALTER TABLE commands will generate an error if the data types do not match. If the expression is a UDF or contains a UDF, it is not evaluated at CREATE time and no error is returned.

When Default Values are Applied

Default values are evaluated (if necessary) and placed in the appropriate fields when the APPEND, APPEND BLANK, or INSERT commands are issued.

When you assign values with the APPEND FROM or INSERT - SQL commands, Visual FoxPro assigns default values to any fields not explicitly assigned. The APPEND FROM and INSERT - SQL commands also respect default values. However, when either of these commands is issued, defaults will not overwrite existing values in fields. If appended or inserted fields contain values, the existing value is retained as the record is appended or inserted and the default value is not applied.

Using Default Values to Auto-Populate NOT NULL Fields

Default values are particularly useful to automatically populate fields that do not allow null values. When you add a new record, default values are applied first, then each field is checked in definition order for missing information. This ensures fields designated as NOT NULL have the opportunity to be populated with default values before the NOT NULL constraint is applied.

Specifying an Input Mask

By specifying an input mask, you define the punctuation, spacing, and other format attributes of values as they are entered into the field. The values are then stored in a uniform manner which can reduce data entry errors and make processing more efficient. For example, adding a mask to a numeric field storing telephone numbers helps the user to quickly fill out the field because the punctuation and spaces are already provided by the mask.

To provide an input mask

• In the Table Designer, enter the mask in the Input mask box in the Display area.

  -or-

• Use the DBSETPROP( ) function to set the InputMask property.

  For example, the following code specifies an input mask for a date:

  DBSetProp("orders.postalcode","field","InputMask", "99999-9999") 

Controlling Display of a Field

Additional properties for fields allow you to control how a field and its values appear on forms, Browse windows, and reports. You can specify a display format, a default field caption, and a default class and class library.

Defining a Format

A format provides an output mask that determines the manner in which the value of a field is displayed in a form, Browse window, or report. For example:

To provide a format

• In the Table Designer, enter the mask in the Format box in the Display area.

  -or-

• Use the DBSETPROP( ) function to set the Format property.

For example, the following code specifies a display format for a postal code:

DBSetProp("orders.postalcode","field","Format","@R 99999-9999") 

Creating Captions for Fields

You can create a caption for each field in a database table. Visual FoxPro displays a field s caption text as the column header in a Browse window and as the default header name in a form grid.

To add a caption to a field in a database table

• In the Table Designer, enter the text for your caption in the Caption box in the Display area.

  -or-

• Use the DBSETPROP( ) function.

For example, you might want to create a caption for the fax field in your supplier table by entering Supplier_Fax as the caption for the field:

?DBSETPROP('supplier.fax', 'field', 'caption', 'Supplier_Fax') 

For more information on using DBSETPROP( ) to set properties on database table fields, see Chapter 6, Creating Databases.

Setting a Default Class

To save time later when you're creating forms, you can set a default class for a field. Once set, each time you add the field to a form, the control on the form uses the class you specify as the default. For example, character fields automatically appear as text box controls when you add them to a form. If you want to automatically create a combo box control instead when you use the field in a form, you can set that class as the default for this field. You can also use class libraries that you've created.

To set a default class

• In the Table Designer, choose a class and library in the Default Class box and the Default Library box.

If you find you're often changing the library and class for your fields, you can map the fields data types to a library and class in the Options dialog box. For more information about mapping your field data types to classes, see Chapter 3, Configuring Visual FoxPro, in the Installation Guide. For more information about creating classes, see Chapter 3, Object-Oriented Programming, in this book.

Enforcing Business Rules

You can enforce business rules for data entry by creating field-level and record-level rules, called validation rules, to control the data entered into database table fields and records. Field- and record-level rules compare the values entered against the rule expressions that you define. If the entered value does not meet the requirements of the rule, the value is rejected. Validation rules exist only in database tables.

Field- and record-level rules enable you to control the types of information entered into a table, whether the data is accessed through a Browse window, a form, or programmatically through the language. They allow you to consistently enforce the rule for a field with less coding than if you wrote the rule expression as code in a VALID clause on a form, or in a portion of program code. In addition, the rules you establish in a database are enforced for all users of the table, regardless of the requirements of the application.

You can also create candidate or primary indexes that prevent duplicate entries in a field, and triggers to enforce referential integrity or perform other actions when the data in your database is changed.

Knowing When Constraints Are Enforced

You choose database constraints based on the level at which you want to enforce a business or referential integrity rule, as well as the action that causes the constraint to be activated. The following table lists the data validation constraints in the order in which they are enforced by the Visual FoxPro engine, the level at which they are applied, and when the engine activates the validation.

Enforcement Mechanism	Level	Activated
NULL validation	Field or column	When you move out of the field/column in a browse, or when the field value changes during an INSERT or REPLACE.
Field-level rules	Field or column	When you move out of the field/column in a browse, or when the field value changes during an INSERT or REPLACE.
Record-level rules	Record	When the record update occurs.
Candidate/primary index	Record	When the record update occurs.
VALID clause	Form	When you move off the record.
Triggers	Table	When table values change during an INSERT, UPDATE, or DELETE event.

Constraints are activated in the order in which they appear in the table. The first violation of any validation test stops the command.

Candidate and primary indexes are explained later in this chapter in the section Controlling Duplicate Values.

Limiting Values in a Field

When you want to control the type of information a user can enter into a field, and you can validate the data in a field independently of any other entry in the record, you use a field-level validation rule. For example, you might use a field-level validation rule to ensure that the user doesn t enter a negative number in a field that should contain only positive values. You can also use a field-level rule to compare the values entered in a field against the values in another table.

You should not create field- or record-level rules that are application-specific. Use field- and record-level validation rules to enforce data integrity and business rules that always apply to the data in your database, regardless of who may access the data. For example, you might create a rule that compares the entry in the postal_code field of a table against a lookup table that contains the postal abbreviation codes for your country, and rejects any value that is not already present as a valid postal code abbreviation.

To create a field-level rule

• In the Table Designer, enter the rule expression in the Rule box in the Field validation area.

  -or-

• Use the CHECK clause of the CREATE TABLE command.

  -or-

• Use the SET CHECK clause of the ALTER TABLE command.

For example, the following code adds a field-level validation rule to the orditems table requiring that numbers entered into the quantity field be 1 or greater:

ALTER TABLE orditems      ALTER COLUMN quantity SET CHECK quantity >= 1 

When the user attempts to enter a value less than 1, Visual FoxPro displays an error and the value is rejected.

You can customize the message displayed when the rule is violated by adding validation text to the field. The text you enter is displayed instead of the default error message.

To add a custom error message to a field-level rule

• In the Table Designer, enter the error message you want in the Message box in the Field validation area.

  -or-

• Use the optional ERROR clause with the CHECK clause of the CREATE TABLE or ALTER TABLE commands.

For example, the following code adds both a field-level validation rule for the orditems table requiring that numbers entered into the quantity column must be 1 or greater, as well as a custom error message:

ALTER TABLE orditems ;    ALTER COLUMN quantity SET CHECK quantity >= 1 ;    ERROR "Quantities must be greater than or equal to 1" 

When the user attempts to enter a value less than 1, Visual FoxPro displays an error with the custom error message you defined, and rejects the failed value. You can also use the SET CHECK clause of the ALTER TABLE command with the optional ERROR clause to create a custom error message.

Knowing When Field-Level Rules are Checked

Field-level rules are checked when the field s value changes. Unlike triggers, field-level rules fire even if data is buffered. When you work with data in a Browse window, form, or other window, Visual FoxPro checks field-level rules as you move away from the field. If a field value has not been changed, the rule is not checked. This means that you are free to tab through fields without the system validating any of the data.

Field-level Rule Checking

Data entry method	Window or command	Field-level rule checked
User interface	Browse window Form Other window	As you move away from the field, if the field value has changed. (If the field value has not been changed, the rule is not checked.)
Commands that do not specify fields	APPEND APPEND GENERAL APPEND MEMO BROWSE CHANGE DELETE EDIT GATHER	As field value changes, in field definition order.
	APPEND BLANK INSERT INSERT - SQL	As the record is appended or inserted.
Commands that specify fields	UPDATE UPDATE - SQL REPLACE	In the order in which fields are specified in the command.

Validating Record-Level Values

You use record-level validation rules to control the type of information a user can enter into a record. Record-level validation rules typically compare the values of two or more fields in the same record to make sure they follow the business rules established for the database. For example, you can use a record-level validation rule to ensure that one field s value is always greater than that of another in the same record.

To create a record-level validation rule and custom error message

• In the Table tab of the Table Designer, enter the rule and error message you want in the Rule and Message boxes.

  -or-

• Use the CHECK clause of the CREATE TABLE or ALTER TABLE commands.

For example, you might want to ensure employees are 18 years or older when hired. The following code adds a record-level validation rule and error text for the employee table requiring that the date of hire entered into the hire_date column is greater than or equal to their birth date plus 18 years:

ALTER TABLE employee SET CHECK ;    hire_date >= birth_date + (18 * 365.25) ;    ERROR "Employees must be 18 years or older by date of hire" 

If the user enters an employee record with an invalid date, Visual FoxPro displays an error with the custom error message you defined, and does not update the record.

You can also use the SET CHECK clause of the ALTER TABLE command to create a record-level validation rule. You should ensure that any rules specified for fields do not conflict semantically with the rules you define for the table. Visual FoxPro makes no attempt to compare the field-level and record-level expressions for consistency.

Knowing When Record-Level Rules are Checked

Record-level rules, like field-level rules, activate when the record value changes. No matter how you work with data, whether in a Browse window, form, or other user interface window, or through commands that alter data, Visual FoxPro checks record-level rules as you move the record pointer off the record. If no values within the record have changed, the record-level rule is not checked when you move the record pointer. You are free to move through records without the system validating any of the data.

If you modify a record, but don't move the record pointer, and then close the Browse window, the rule is still checked. You're warned of any errors that occur, and the Browse window is closed.

Caution   Do not include any commands or functions in your validation rules that attempt to move the record pointer in the current work area (that is, in the work area whose rules are being checked). Including commands or functions such as SEEK, LOCATE, SKIP, APPEND, APPEND BLANK, INSERT, or AVERAGE, COUNT, BROWSE, and REPLACE FOR in validation rules may cause them to trigger recursively, creating an error condition.

Unlike triggers, record-level rules fire even if data is buffered. When a record-level rule fires during a running application, you need to include error handling code. Typically, this will mean not allowing the application to leave the form (or change the active environment, to be more generic) until the user either corrects the reported error or cancels the update.

Removing a Table with Associated Rules from a Database

If you remove or delete a table from a database, all field-level and record-level rules bound to that table are deleted from the database. This is because the rules are stored in the .dbc file, and removing a table from the database breaks the link between the .dbf file and its .dbc file. However, stored procedures referenced by the removed or deleted rule are not deleted. They are not automatically removed, because they may be used by rules in other tables that remain in the database.

Using Triggers

A trigger is an expression that is bound to a table and is invoked when any of the table s records are modified by one of the specified data-modification commands. Triggers can be used to perform any side-effect operations that a database application requires when data is modified. For example, you can use triggers to:

• Log database modifications.
• Enforce referential integrity.
• Create an automatic reorder for a product that is low on stock.

Triggers are created and stored as properties on a specific table. If you remove a table from a database, the triggers associated with that table are deleted. Triggers fire after all other checks, such as validation rules, primary key enforcement, and null value enforcement, are performed. And unlike field- and record-level validation rules, triggers don t fire on buffered data.

Creating Triggers

You create triggers using the Table Designer or the CREATE TRIGGER command. For each table, you can create one trigger for each of the three events: INSERT, UPDATE, and DELETE. A table can have a maximum of three triggers at any one time. A trigger must return a true (.T.) or false (.F.) value.

To create a trigger

• In the Table tab of the Table Designer, enter the trigger expression or the name of a stored procedure containing the trigger expression in the Insert trigger, Update trigger, or Delete trigger box.

  -or-

• Use the CREATE TRIGGER command.

For example, perhaps each time Tasmanian Traders sells an item, they want to compare the remaining Units_in_stock against the Reorder_level and be notified if they need to reorder that item. You can create an Update trigger on the products table to accomplish this. Every time a product is sold, the Update trigger will fire and the Units_in_stock field will be updated to reflect the remaining items in stock.

To create the trigger, you can specify updProductsTrigger( ) as your Update trigger for the products table. You can add a field to products, named reorder_amount, which stores the amount you want to order each time you reorder the item, and create a reorder table with the fields: product_id and reorder_amount. You can then add this code to your stored procedure:

PROCEDURE updProductsTrigger    IF (units_in_stock+units_on_order) <= reorder_level    INSERT INTO Reorder VALUES(Products.product_id, ;     Products.reorder_amount)    ENDIF    ENDPROC 

You can create similar triggers for an insert or delete event by using the FOR INSERT or FOR DELETE clause, respectively, instead of the FOR UPDATE clause. If you attempt to create a trigger that already exists for a particular event and table while SET SAFETY is on, Visual FoxPro asks you if you want to overwrite the existing trigger.

Removing or Deleting Triggers

You can remove a trigger from a database table through the interface or with the DELETE TRIGGER command.

To delete a trigger

• In the Table tab of the Table Designer, select the trigger expression in the Insert trigger, Update trigger, or Delete trigger box and delete it.

  -or-

• Use the DELETE TRIGGER command.

The following example removes the update trigger for the customer table:

DELETE TRIGGER ON customer FOR UPDATE 

If you remove or delete a table from a database, all triggers bound to that table are deleted from the database. However, stored procedures referenced by the removed or deleted trigger are not deleted.

Modifying Triggers

You can modify triggers through the Table Designer or through the language.

To modify a trigger

• In the Table tab of the Table Designer, enter the new trigger expression in the Insert trigger, Update trigger, or Delete trigger box.

  -or-

• Issue the SET SAFETY OFF command, and then use the CREATE TRIGGER command.

When you modify a trigger by first issuing the SET SAFETY OFF command and then re-creating the trigger, the old trigger expression is automatically deleted and replaced by the re-created trigger expression.

Using Triggers to Build Referential Integrity

Visual FoxPro provides a Referential Integrity Builder to generate triggers and stored procedures that will enforce Referential Integrity (RI) for your database. For more information on using the RI Builder, see Chapter 6, Creating Databases.

Modifying the Table Structure

After you ve built a table you can always modify the table structure and properties. You may want to add, change or delete field names, widths, data types, change default values or rules, or add comments or captions.

You can open the Table Designer to modify your table s structure, or you can make changes programmatically using the ALTER TABLE command. Make sure you have exclusive access to the table before modifying its structure.

To modify the structure of a table with the Table Designer

• In the Project Manager, select the table name and then choose Modify.

  -or-

• In the Database Designer, select the table in the schema and choose Modify from the Database menu.

  -or-

• Use the MODIFY STRUCTURE command.

For example, you can modify the structure of the database table employee with the following commands:

OPEN DATABASE testdata USE employee EXCLUSIVE MODIFY STRUCTURE 

Each of the previous options opens the Table Designer.

To modify the structure of a table programmatically

• Use the ALTER TABLE command.

The ALTER TABLE command offers extensive clauses that enable you to add or drop table fields, create or drop primary or unique keys or foreign key tags, and rename existing fields. Some clauses apply only to tables associated with a database. A few specific examples are included in this section.

Adding Fields

You can add a new field to a table with the Table Designer or with the language.

To add a field to a table

• In the Table Designer, choose Insert.

  -or-

• Use the ADD COLUMN clause of the ALTER TABLE command.

For example, the following command adds a field called fax to the customer table and allows the field to have null values:

ALTER TABLE customer ADD COLUMN fax c(20) NULL 

Deleting Fields

You can delete an existing field from a table with the Table Designer or with the language.

To delete a field from a table

• In the Table Designer, select the field and choose Delete.

  -or-

• Use the DROP COLUMN clause of the ALTER TABLE command.

For example, the following command drops the field called fax from the customer table:

ALTER TABLE customer DROP COLUMN fax  

Removing a field from a table also removes the field s default value setting, rule definitions, and caption. If index key or trigger expressions reference the field, the expressions become invalid when the field is removed. The invalid index key or trigger expression will not generate an error until run time.

Renaming Fields

You can rename existing table fields in two ways.

To rename a table field

• In the Table Designer, enter a new field name in the Name box for the existing field.

  -or-

• Use the RENAME COLUMN clause of the ALTER TABLE command.

For example, to rename the column company in the customer table, you could issue the following command:

ALTER TABLE customer RENAME COLUMN company TO company_long_new_name 

In the previous example, the new field name takes advantage of the ability to create long field names in database tables.

Setting or Changing Field-Level or Table Rules

You can set new field-level or table rule expressions and rule text, as well as alter rules and text you established with CREATE TABLE or ALTER TABLE commands.

To change an existing rule

• In the Table Designer, select the Table tab and enter the new rule expression or rule text in the Rule and Message boxes in the Record validation section.

  -or-

• Use the ALTER TABLE command.

To view the current rule expression and associated text, you can use the DBGETPROP( ) function; these values are read-only for tables and can only be changed using the ALTER TABLE command.

Setting or Changing Default Values

You can set or change default values for table fields after you ve built your table.

To change an existing default value

• In the Table Designer, enter the new value in the Default value box of the Fields tab.

  -or-

• Use the ALTER TABLE command.

To view the current default value for a field, use the DBGETPROP( ) function; these values are read-only for tables and can only be changed using the ALTER TABLE command.

Working with Records

Once you ve designed and created the structure for a table, you can store data in the table by adding new records. Later, you ll change and delete existing records. Each of these tasks can be accomplished either through the interface or by using commands. This section focuses primarily on working with records programmatically. For more information about working with records through the interface, see Chapter 2, Creating Tables and Indexes, in the User s Guide.

Adding Records

When you first create a Visual FoxPro table, it is open but empty. If you try to store data to a table without first creating a record in the table, nothing happens. The first step in adding records to a new table is to add rows to store the new data.

To add records to a table

• Use the INSERT - SQL command.

The INSERT - SQL command can be used to insert values specified with the command or to insert values from an array or variable. For example, to insert a new record into the TasTrade database customer table, you could issue the following command:

INSERT INTO customer (cust_id, company, contact) ;    VALUES ("SMI007", "Smith's Delicatessen", "Sarah Smith") 

The INSERT - SQL command is useful with remote data, as it uses ANSI-compliant SQL syntax.

You can also use the APPEND BLANK command followed by the REPLACE command to add a blank record to a table and then store data in a field. The APPEND BLANK appends a new, blank record to a table. The REPLACE command replaces the current value of a field, even an empty field, with a new value.

The REPLACE command requires:

• An open table.
• An existing record.
• The name of the field in which to store the value.
• A value for each field that is valid for the field s data type.

The following example uses the APPEND BLANK command to create one record in which you can store data using the REPLACE command:

APPEND BLANK                     && record now available REPLACE lastname WITH "SMITH"   && store character value to the field  

You can use the UPDATE - SQL command instead of the REPLACE command to update records in a table.

Appending Records from Another Table

Another way to store data in records is to copy them from other tables or files. For example, you can append records from another table or file.

To append records from another file

• Use the APPEND FROM command.

  -or-

• Use the IMPORT command.

Records can accept data directly, as in the previous example, where the INSERT command specified the text to be inserted into specific fields in the customer table, as well as from constants, variables, arrays, objects, and other data sources. For more information about other ways to import data, see Chapter 9, Importing and Exporting Data, in the User s Guide.

Adding Records in Browse Mode

If you want to add a new record while viewing a table in browse mode, you can choose Append Record from the Table menu. Conversely, if you want to prevent users from being able to append a new record while in browse mode, you can use the NOAPPEND clause of the BROWSE command.

Entering Data in a Table

You can enter data in a table interactively, through a Browse window, or programmatically, with the REPLACE or UPDATE - SQL commands. When you use the REPLACE or UPDATE - SQL in a multi-user application, you can turn on record or table buffering, which enables you to edit data without locking the record until you want to commit changes. For more information on record and table buffering, see Chapter 17, Programming for Shared Access.

Editing Records in a Table

You can display and edit existing records in a table through the interface or programmatically.

To display records for editing

• Use the EDIT command.

  -or-

• Use the CHANGE command.

For example, the following code displays the customer table in a Browse window in edit mode:

USE customer EDIT 

If you want to use a form to edit a record, create a text box in your form and set its DataSource property to the name of the table you want to edit. For more information about forms, see Chapter 9, Creating Forms.

You can also use the CHANGE and EDIT commands to make changes to specific fields in a table.

Adding Graphics to a Table

You can store graphics in a Visual FoxPro table by creating a General field and importing or pasting OLE objects, such as bitmaps or charts, into the field. The APPEND GENERAL command places an OLE object into a General field. The following example stores a Microsoft Excel chart file from the default Visual FoxPro directory into a General field named Chart:

APPEND GENERAL Chart FROM "CHART1.CLX" CLASS EXCELCHART 

For more information about working with OLE objects in Visual FoxPro tables, see Chapter 16, Adding OLE.

Entering Null Values in Fields

You can enter a null value in a field through the language with the NULL token, or through the interface with a key combination if the field accepts null values.

To store a null value in a field

• In a Browse window or form control, press CTRL+0 (zero).

  -or-

• Use the NULL token.

For example, the following code replaces the existing value in the field automobile with a null value:

REPLACE automobile WITH NULL  

Note   Use the SET NULLDISPLAY command to specify the text displayed for null values.

Deleting Records

You delete records by marking them for deletion, then removing the deleted records. Until you remove the records that are flagged for deletion, they are still on disk and can be unmarked and restored. This section describes how to mark, unmark, and remove records from your table.

Marking Records for Deletion

You can mark records for deletion through the interface or with the DELETE - SQL command.

To mark a record for deletion

• In a Browse window, click the deletion marker to flag the record.

  -or-

• From the Table menu, choose Delete Records.

  -or-

• Use the DELETE - SQL command.

You can use the DELETE - SQL command to specify a range of records, as well as a condition based on a logical expression that records must meet to be marked for deletion. For example, the following code marks for deletion all product records with T in the Discontinu field:

USE products DELETE FROM products WHERE discontinu = .T. BROWSE 

Records you mark for deletion are not physically removed from the table until you issue a PACK command. When you view the table in the Browse window, you ll see that the deletion marker is flagged for each deleted record, but the record is still visible in the table, if SET DELETED is set to off. If SET DELETED is set to on, the records marked for deletion are excluded from the Browse window.

The setting of the SET DELETED command also affects whether records marked for deletion are accessible by commands that operate on records.

Retrieving Records Marked for Deletion

You can unmark records that were marked for deletion with the RECALL command. The RECALL command can recover records only if you have not issued a PACK or ZAP command, which physically deletes the records from the table.

To unmark a record marked for deletion

• In a Browse window, click the deletion marker to unmark the record.

  -or-

• From the Table menu, choose Recall Records.

  -or-

• Use the RECALL command.

You can use the RECALL command to specify a range of records, as well as a condition based on a logical expression that records must meet to be unmarked for deletion. For example, the following code unmarks for deletion all product records with T in the discontinu field:

USE products  RECALL FOR discontinu = .T. BROWSE 

When you view the table in the Browse window, you ll see that the deletion marker is not flagged for the records.

Removing Records Marked for Deletion

After you ve marked records for deletion, you can permanently remove them from disk through the interface or the language.

To remove records marked for deletion from disk

• In a Browse window, choose Remove Deleted Records from the Table menu.

  -or-

• Use the PACK command.

The PACK command has two clauses: MEMO and DBF. When you issue PACK without the MEMO or DBF clauses, records in both the table file and the associated memo file are removed. Make sure you have exclusive access to the table. For example, the following code removes records marked for deletion:

USE customer EXCLUSIVE PACK 

To delete records in the table file only and leave the memo file untouched, use PACK DBF.

Conserving Space

Information in table memo fields is stored in an associated memo file with the same name as the table and an .fpt extension. If you want to remove unused space from the memo file without removing records that are marked for deletion, issue the PACK command with the MEMO clause. Make sure you have exclusive access to the table.

Removing All Records from a Table

If you want to remove all records from a table, and leave just the table structure, you can use the ZAP command. Issuing ZAP is equivalent to issuing DELETE ALL followed by PACK, but ZAP is much faster. Make sure you have exclusive access to the table.

Caution   Records zapped from the current table cannot be recalled.

Indexing Tables

When you want to navigate, view, or manipulate table records in a particular order, you use an index. Visual FoxPro uses indexes as ordering mechanisms to provide you with flexibility and power as you develop your application. You have the flexibility to create and use many different index keys for the same table, enabling you to work with records in different orders, according to your application s requirements. You have the power to create custom relationships between tables based on their indexes, enabling you to access exactly the records you want.

A Visual FoxPro index is a file that contains pointers that are logically ordered by the values of an index key. The index file is separate from the table s .dbf file, and does not change the physical order of the records in the table. Rather, when you create an index, you create a file that maintains pointers to the records in the .dbf file. When you want to work with table records in a particular order, you choose an index to control the order and increase the speed in which the table is viewed and accessed.

Creating One Index

When you first create a table, Visual FoxPro creates the table s .dbf file and, if your table includes Memo or General fields, the associated .fpt file. You can choose to quickly add an index to a field as you define the field; otherwise, no index files are generated at that time. Records you enter into the new table are stored in the order you entered them; when you browse the new table, they appear in the order they were entered.

Typically, you ll want to be able to view and access the records in your new table in a specific order. For example, you may want to view the records in your customer table alphabetically by company name. When you want to control the order in which records are displayed and accessed, you create an index file for your table by creating the first ordering scenario, or index key, for your table. You can then set the order of the table to the new index key, and access the table s records in the new order.

To create an index key for a table

• In the Table Designer, choose the Index tab and enter the information for one index key. Choose Regular as the index type.

  -or-

• Use the INDEX command.

For example, the following code uses the table customer and creates an index key on the city field. The keyword TAG and the word city afterward specifies a name, or tag, for the new index key on the city field.

USE customer INDEX ON city TAG city 

In the previous example, the tag for the index key uses the same name as the field you re indexing. The names don t have to match you could also choose to give the index key a different name.

When you first create an index using the INDEX command, Visual FoxPro automatically uses the new index to set the order of the records in the table. For example, if you entered some data into the sample table created in the previous example, and then browsed the table, the records would appear in order by city.

Creating an Index File

As you created the first index key for your table in the previous example, Visual FoxPro automatically created a new file, Customer.cdx, to store the new index key. The .cdx index file, called a structural compound index, is the most common and important type of index file you ll create in Visual FoxPro. The structural .cdx file:

• Opens automatically when you open a table.
• Can contain multiple ordering scenarios, or index keys, in the same index file.
• Is automatically maintained as you add, change, or delete table records.

If a Visual FoxPro table has any index file associated with it at all, it is typically a structural .cdx file. The term structural refers to the fact that Visual FoxPro treats the file as an intrinsic part of the table and opens it automatically when you use a table. Whether you use the Table Designer or the simplest form of the INDEX command, as shown in the previous example, Visual FoxPro creates the .cdx file with the same base name as the current table, and stores the index information for the new key, or tag, inside. You use the structural .cdx file for frequently used index keys, such as those used for ordering records for daily viewing, data entry, SET RELATION linkages, Rushmore optimization on viewing records, or frequently printed reports.

Visual FoxPro offers you two additional types of index files: the non-structural .cdx file and the single-key .idx file. Because the .cdx (or structural compound compact index) is the most important index type, most of the examples in this section will discuss using index keys in the .cdx file to order table records. The other two index file types are less frequently used and are discussed at the end of this section.

Viewing Index Information

You can see how many records are indexed during the indexing process by setting TALK to ON. The record interval displayed during indexing can be specified with SET ODOMETER. For more information about open index files, use the DISPLAY STATUS command. This command lists the names of all open index files, their types (structural, .cdx, .idx), their index expressions, and the name of the master index file or master tag.

The number of index files (.idx or .cdx) you can open is limited only by memory and system resources.

Controlling Duplicate Values

Visual FoxPro supports four types of indexes: primary, candidate, unique, and regular. These index types control whether duplicate values are permitted or prohibited in table fields and records.

Preventing Duplicate Values

A primary index is an index that never permits duplicate values on the specified fields or expression. Primary indexes are principally used within the primary or referenced table for establishing referential integrity in a persistent relationship. You can create only one primary index for a table. Visual FoxPro returns an error if you specify a primary index on any field that already contains duplicate data.

A candidate index is an index that never permits duplicate values on the specified fields or expression. The name Candidate refers to the status of the index; since candidate indexes prohibit duplicate values, they qualify as candidates to be selected as the primary index on a table.

You can create multiple candidate indexes for a table. You use candidate indexes as either the referenced or referencing index in a persistent relationship for establishing referential integrity.

Visual FoxPro returns an error if you specify a candidate index on any field that already contains duplicate data.

Setting a Primary or Candidate Index

You create primary and candidate indexes with the CREATE TABLE or ALTER TABLE commands. You can use both candidate and primary indexes in defining the one side of a one-to-many or a one-to-one persistent relationship.

To create a primary or candidate index

• In the Table Designer, select the Index tab and create an index, selecting Primary or Candidate as the index type.

  -or-

• Use the ALTER TABLE command.

For example, either of the following commands make cust_id the primary key of the customer table:

ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY 

Primary and candidate indexes are stored in the structural .cdx file for a given table. The database stores the names of the indexes in the .cdx file and whether or not the indexes are Primary or Candidate. It is not possible to store these types of indexes in other .cdx files, nor is it possible to use .idx files for these types of indexes. The principal reason is that the index file that contains these indexes should always be open whenever their associated table is opened.

Primary keys are part of a table within a database. If you free a table from a database, the primary key is removed.

If you use a user-defined function in an index expression associated with a database, Visual FoxPro handles the expression in the same way it handles rule and trigger expressions that contain UDFs.

Permitting Duplicate Values

In Visual FoxPro, a unique index does not prevent duplicate values from being created; rather, a unique index stores only the first occurrence of the value in the index file. In this sense, the word unique refers to the entries in the index file, which contains only unique values, because it doesn't store a particular key more than once, and ignores the second or later occurrence of a non-unique value. The table indexed by a unique index could contain duplicate values. Unique index types are provided primarily for backward compatibility.

A regular index is simply an index that is not unique, primary, or candidate. You use a regular index for ordering and seeking records, but not to enforce the uniqueness of the data in those records. You also use a regular index as the many side of a one-to-many persistent relationship.

To create a regular index

• In the Table Designer, select the Index tab and create an index, by selecting Regular as the index type.

  -or-

• Use the INDEX command.

For example, the following commands make city a regular key for the customer table:

USE customer  INDEX ON city TAG city 

Creating Multiple Indexes

As you work with the records in your table, you ll discover the need for accessing table records in several different sequences. For example, you might want to order the customer table by contact to quickly find a name you re looking for, or by postal code to generate mailing labels that are presorted for more efficient mailing.

You can create and store many different ordering scenarios for your table by creating multiple index keys for the same table. This enables you to order table records at different times according to different purposes.

To create additional index keys for a table

• In the Table Designer choose the Index tab and enter the information for additional index keys.

  -or-

• Use the INDEX command.

For example, the following code creates two new index keys on the employee table: one on the last_name field and another on the country field:

USE employee INDEX ON last_name TAG last_name INDEX ON country TAG country 

When you create an index tag without specifying the name of an index file, the tag is added automatically to the table s structural .cdx index file. The following diagram shows a .cdx index file with three index tags.

.cdx index contains multiple tags representing multiple record-ordering scenarios.

Two of the tags in the diagram, emp_id and last_name, represent indexes based on single fields. The cntry_last index orders records using a simple two-field index expression. For more information on building an index based on multiple fields, see Indexing on Expressions later in this chapter.

Controlling the Order in which Records Are Accessed

After you create index keys for the customer table on the company, city, and country fields, you can access and display the table in different orders, simply by choosing the index key you prefer. You use the SET ORDER command to choose a specific index key as the ordering key for the table.

For example, the following code opens a Browse window displaying the records in the customer table in order by country:

SET ORDER TO country BROWSE 

Setting Record Order at Run-Time

Using the SET ORDER command, you can designate the controlling index file or tag. A table can have many index files open simultaneously. However, you determine the order in which the records in a table are displayed or accessed by setting one single-index (.idx) file (the controlling index file) or tag from a compound index (.cdx) file (the controlling tag) as the controlling index. Certain commands, such as SEEK, use the controlling index tag to search for records. You do not need to SET ORDER for running queries.

Setting Record Order Interactively in a Form

You can use SET ORDER at run time to change the order of records in a form. For example, you might want to enable your application s users to reorder the records in a grid by clicking on the header of the column they want to order by.

To sort the records in a grid by columns

1. Create a form with a Grid control.
   
   
2. Set the ColumnCount property of the grid to the number of fields you want displayed in the grid.
   
   
3. In the Click event for the header of each column in the grid, insert code that:
   • Sets the record order to an index key based on the column.
     
     
   • Refreshes the form.

For example, if you created a form based on the Customer table in the Testdata database with a grid containing four columns company, contact, postal code, and phone the grid would first appear sorted alphabetically because the records in that table were entered alphabetically.

Customer table in grid, ordered alphabetically by company name

You could then enable the user to view the grid in contact or postal_code order by inserting the following code in the Click event of each column header:

Sample event code to order records in a Grid by clicking on the column header

Code	Comment
SET ORDER TO company GO TOP THISFORM.Refresh	In the Company header Click event code, reorder the grid by the company index key and refresh the form to display records in order by company.
SET ORDER TO contact GO TOP THISFORM.Refresh	In the Contact header Click event code, reorder the grid by the contact index key and refresh the form to display records in order by contact name.
SET ORDER TO postalcode GO TOP THISFORM.Refresh	In the Postal_Code header Click event code, reorder the grid by the postalcode index key and refresh the form to display records in order by postal code.
	Because sorting by phone number is not relevant to this application, leave the Phone header Click event code blank.

In this example, when the form is first displayed, the grid appears in alphabetical order by company. When the user clicks the header of the Contact column, Visual FoxPro displays the records in the grid in alphabetical order by contact name.

Customer table in grid, reordered alphabetically by contact name

If the user clicks on the Postal_code column header, the grid is resorted and displayed in order by postal code.

Customer table in grid, reordered by postal code

Since there isn't a pressing need in our example application to sort contacts by phone numbers, no SET ORDER code is inserted into the Click event for the phone column header. When the user clicks the Phone column header, the grid display does not change.

Using Other Index Types

In addition to the most common index the compact compound structural .cdx index Visual FoxPro supports two other types of index files: the non-structural .cdx, and the stand-alone .idx index. Non-structural .cdx indexes are used for less frequently used multiple-key tags. Stand-alone, or .idx indexes are used for temporary or infrequently used single-key indexes, and are available primarily for backward compatibility.

The following table is a summary of the three index types, how they are named, the number of keys they can contain, and the character limitations for each.

Visual FoxPro Index Types

Index type	Description	Number of keys	Limits
Structural .cdx	Uses same base name as the table file name; opens with table automatically	Multiple-key expressions, called tags	240-character limit on evaluated expression
Non-structural .cdx	Must be opened explicitly; uses a different name from the base table name	Multiple-key expressions, called tags	240-character limit on evaluated expression
Stand-alone .idx	Must be opened explicitly; base name of .idx file is user-defined	Single key expression	100-character limit on evaluated expression

Using Non-Structural .cdx Indexes

A non-structural .cdx index is useful when you want to create multiple index tags for a special purpose, but don t want to burden your application with maintaining these indexes on an ongoing basis. For example, your application may have a special set of reports that analyzes data based on fields not normally indexed. Your application program can create a non-structural .cdx index with the necessary index tags, run the special reports, then delete the non-structural .cdx file.

To create a non-structural .cdx index tag

• Use the TAG and OF clauses with the INDEX command.

You use the OF clause with the INDEX command to direct Visual FoxPro to store the tag in a file other than the structural .cdx index file for the table. For example, the following command creates tags called title and hire_date on the employee table and stores them in a non-structural .cdx file named QRTLYRPT.CDX:

USE employee INDEX ON title TO TAG title OF QRTLYRPT INDEX ON hire_date TO TAG hiredate OF QRTLYRPT 

Using Stand-Alone Indexes

The stand-alone index file, based on a single key expression, is stored as an .idx file. In contrast to .cdx indexes, which can store multiple key expressions, the .idx index stores only a single key expression.

You typically use stand-alone indexes as temporary indexes, creating or re-indexing them right before you need them. For example, you may have an index that you use only for a quarterly or annual summary report. Rather than include this infrequently used index in the structural .cdx, where it would be maintained every time you use the table, you can create a stand-alone .idx index. You can create as many .idx files as you want for a particular table.

To create a stand-alone .idx index

• Use the COMPACT clause of the INDEX command.

  -or-

• Use the COPY TAG command.

Using the INDEX command with the COMPACT clause creates a new stand-alone index in a small, quickly accessed index file. You can omit the COMPACT clause if you want to create a non-compact stand-alone .idx file for compatibility with the older FoxBASE+ and FoxPro version 1.0 index formats.

The following code creates a stand-alone .idx file on order_date in the orders table, sets the order to the new index, then opens a Browse window showing the orders in order_date sequence:

USE ORDERS INDEX ON order_date TO orddate COMPACT SET ORDER TO orddate BROWSE 

You can use the COPY TAG command to generate a stand-alone index file from an index tag in an existing .cdx file. For example, you may find that one of the indexes you currently maintain in the structural .cdx is used only for quarterly or annual reports. The following code creates a stand-alone index from a tag birth_date in the employee table:

COPY TAG birth_date to birthdt COMPACT 

After you ve created a stand-alone index from a tag in a .cdx file, you ll typically delete this now unneeded tag from the .cdx file. The next section describes deleting an index.

Deleting an Index

You can delete indexes you re no longer using by deleting the tag within the .cdx file, or by deleting the .idx file itself for stand-alone indexes. Deleting unused index tags improves performance by removing the need for Visual FoxPro to update unused tags to reflect changes in a table s data.

Deleting a Tag from the Structural .cdx File

You can remove a tag from the structural .cdx file using the Table Designer or the language.

To delete an index tag in the structural .cdx

• In the Table Designer, use the Index tab to select and delete the index.

  -or-

• Use the DELETE TAG command.

  -or-

• Use the DROP PRIMARY KEY or DROP UNIQUE TAG clauses of the ALTER TABLE command.

For example, if your employee table contained a tag called title, you could delete it using the following code:

USE employee DELETE TAG title 

If the tag you wanted to delete was the primary key for the employee table, you could use the ALTER TABLE command:

USE employee ALTER TABLE DROP PRIMARY KEY 

Deleting a Tag from a Non-Structural .cdx File

A non-structural .cdx index and its tags are not visible in the Table Designer. You use the language to delete a tag from a non-structural .cdx file.

To delete an index in a non-structural .cdx file

• Use the OF clause of the DELETE TAG command.

You use the OF clause with the DELETE TAG command to direct Visual FoxPro to delete a tag from a .cdx other than the structural .cdx file. For example, if you have a non-structural .cdx file named QRTLYRPT.CDX with a tag called title, you could delete the title tag with the following command:

DELETE TAG title OF qtrlyrpt 

You delete all the tags in a structural or non-structural .cdx file with the ALL clause of the DELETE TAG command.

Deleting a Stand-Alone .idx Index File

Because a stand-alone index file contains only a single index key expression, you delete the expression by deleting the .idx file from disk.

To delete a stand-alone .idx file

• Use the DELETE FILE command.

For example, the following code deletes the stand-alone .idx index file Orddate.idx:

DELETE FILE orddate.idx 

You could also use a utility, such as Windows Explorer, to delete an unneeded stand-alone .idx file.

Indexing on Expressions

You can increase the power of your applications by creating indexes based on expressions. These expressions can be simple or complex, depending upon what you want to accomplish.

Indexing on Simple Expressions

Simple index expressions are indexes based on single fields, or on the concatenation of two or more character fields to form a multifield key. For example, you might want to create an index for the Customer table in the TasTrade database based on the expression:

country + region + cust_id 

When you browse the Customer table sorted by this index tag, you see the customers ordered by country, then region, then customer ID.

Preventing Duplicates in a Combination of Fields

If you want to prevent duplicating values across multiple fields, you can create a primary or candidate index based on an expression combining multiple fields.

For example, you may have a table that stores the area code and phone number in two columns:

Area Code	Phone Number
206	444-nnnn
206	555-nnnn
313	444-nnnn

Both the area code field and the phone number field contain values that duplicate the values in other rows. However, no phone numbers are duplicated, because it is the combination of the two fields that makes up the value. Yet, if the primary or candidate index specified both columns in the index expression, the rows in the example would not be considered duplicates. If you attempted to enter a value that was exactly the same area code and phone number as one of the existing rows, Visual FoxPro would reject the entry as a duplicate.

Using Null Values in Index Expressions

You can create indexes on fields that contain null values. Index expressions that evaluate to .NULL. are inserted into the .cdx or .idx file before non-null entries. All null values are placed at the beginning of the index.

The following example demonstrates one effect of indexing null values. This is the state of the table before the index is applied:

Null values appear in the SocSec field for two records.

The value .NULL. in two records represents the fact that Social Security numbers for Anne Dunn and Alan Carter are either unknown or unavailable. You then create an index with the Social Security number using the following example:

INDEX ON SocSec + LastName + FirstName TAG MyIndex 

When you view the table sorted by this index, you see the sort order as shown in the following figure.

After indexing on SocSec, records containing null SocSec values appear first.

When the index expression contains null values, the records whose SocSec values are .NULL. are sorted first (by LastName), followed by the records whose SocSec values are non-null. Notice that there are two entries for Alan Carter. Because record 5 contains a null value, record 5 is indexed before record 2.

Indexing on Complex Expressions

You can also create indexes based on more complex expressions. Visual FoxPro index key expressions can include Visual FoxPro functions, constants, or user-defined functions.

The expression you create must evaluate to no more than 100 characters for a stand-alone (.idx) index or 240 characters for a .cdx index tag. You can use fields of different data types together in a single tag by converting the individual components of the expression to character data.

To take advantage of Rushmore optimization, the index expression must exactly match the criteria.

Using Visual FoxPro Functions in an Index Tag

You can use Visual FoxPro functions in an index tag. For example, you can use the STR( ) function to convert a numeric value into a character string. If you wanted to create an index tag for the customer table that combined the cust_id field with the maxordamt field, you could convert the maxordamt field from a Currency field with a width of 8 to an 8-character field with 2 decimal places using the following code:

INDEX ON cust_id + STR(maxordamt, 8, 2) TAG custmaxord 

If you want to reduce the size of indexes for fields with integer values, you can convert the integer values to a binary character representation using the BINTOC( ) function. You can also convert the binary values to integer values using the CTOBIN( ) function.

If you want to create an index to sort a table in chronological order, you can use the DTOS( ) function to convert a date field to a character string. To access the employee table by hire_date and emp_id, you can create this index key expression:

INDEX ON DTOS(hire_date) + emp_id TAG id_hired 

Including Stored Procedures or User-Defined Functions

You can increase the power of your index by referencing a stored procedure or a user-defined function in your index expression. For example, you can use a stored procedure or a UDF to extract the street name from a single field that includes both the street number and street name. If the street number is always numeric, the stored procedure or UDF can return the character portion of the field and pad the field with spaces as needed to create a constant-length index key. You can then use this index key to access records in the table in street-name order.

You may prefer to use a stored procedure rather than a UDF in your index tag, if your table is associated with a database. Because a UDF is stored in a file that is  separate from the database, it is possible to move or delete the UDF file, which then causes the index tag referencing the UDF to become invalid. In contrast, stored procedure code is stored in the .dbc file and can always be located by Visual FoxPro.

Another benefit to using a stored procedure in an index tag is that referencing a stored procedure guarantees that the index is based on the exact code you specify. If you use a UDF in your index expression, any UDF that is in scope at the time of indexing and has the same name as the UDF referenced in your index will be used.

Note   Exercise care when referencing a stored procedure or UDF in an index expression, as it increases the time required to create or update the index.

Using Data in a Field in Another Table

You can create an index tag that refers to a table open in another work area. It s wise to use a stand-alone index (.idx) for any tag that refers to more than one table. This is because if you were to include a tag referring to another table in a structural .cdx file, Visual FoxPro wouldn t allow you to open the table until you opened the table referenced in the index tag.

Accessing Records in Descending Order

You can view records in descending order by creating a descending index, or by reading an existing index in descending order.

To create a descending index

• In the Index tab of the Table Designer, choose the arrow button to the left of the Name box so that the arrow points down.

  -or-

• Use the DESCENDING clause with the INDEX ON command to create a descending index.

To create a compound structural index file, you can use either method. To create other types of index files, you can use the second method. For example, you could create a new descending index ordering your product table from highest to lowest unit_price and browse the table in the new order with the following code:

USE products INDEX ON unit_price TAG unit_price DESCENDING BROWSE 

To read an existing index in descending order

• Use the DESCENDING clause of the SET ORDER command to read an existing index in descending order.

Reading an existing index in descending order enables you to leverage an existing index rather than create a new one. For example, you may have already created an index ordering your product table by unit_price with the following code:

USE products INDEX ON unit_price TAG unit_price 

By default, the order is ascending. You could browse the table in descending order with the following code:

USE products SET ORDER TO unit_price DESCENDING BROWSE 

The previous examples focus on accessing information in descending order. Both the SET ORDER and INDEX commands also offer an ASCENDING clause. You can combine these two commands to gain tremendous flexibility in your application. For example, if you use the ASCENDING or DESCENDING clause to create an index in the most frequently used order, you can then use the opposite clause with the SET ORDER command to view or access the information in the opposite order, when that order is more convenient.

Filtering Data

You can limit the records you access to only the data you want by using a filtered index. When you create a filtered index, only records that match the filter expression are available for display and access.

To filter data using a filtered index

• In the Table Designer, select the Index tab and enter a filter expression in the Filter box for the index you want to filter.

  -or-

• Use the optional FOR clause with the INDEX command.

If you include the optional FOR clause with the INDEX command, the index file acts as a filter on the table. Index keys are created in the index file for just those records that match the filter expression. For example, if you were preparing a mailing to go to the sales representatives in your company, and you wanted to sort the mailing by country, you could create an index that filtered the employee table so that only the records for sales representatives appeared, ordered by their country and their last name. The following code creates a filtered index and displays the filtered data in a Browse window:

USE employee INDEX ON country+last_name FOR title = "Sales Representative" ; TAG reps_cntry BROWSE 

When you view the Browse window, only the sales representatives are shown; the records for other employees do not appear at all in the Browse window.

A filtered index builds an index only for records that match the filter expression.

Filtering Data Temporarily

You can use the SET FILTER command to filter data temporarily, without building a special filtered index. This command is particularly useful when you want to specify a temporary condition that records in a table must meet in order to be accessible. To turn off the filter for the current table, you can issue SET FILTER TO without an expression. For example, you could issue the following command to filter the customer table to show only the customers in Germany:

USE customer SET FILTER TO country = "Germany" BROWSE 

The SET FILTER command accepts any valid Visual FoxPro logical expression as the filter condition. Once you issue the SET FILTER command, only the records that satisfy the filter condition are available in the table. All commands that access the table respect the SET FILTER condition. You can set a separate filter for every open table.

Using Indexes Efficiently

You can improve the performance of indexed tables by keeping indexes current and using optimizable expressions in your indexes.

Rebuilding an Active Index File

Index files become outdated when you open a table without opening its corresponding index files and make changes to the key fields in the table. Index files can also become invalid as a result of a system crash, or potentially by accessing and updating a table from a program other than Visual FoxPro. When index files become outdated, you can update them by re-indexing with the REINDEX command.

To rebuild an active index file

• From the Table menu, choose Rebuild Indexes.

  -or-

• Use the REINDEX command.

For example, the following code updates the index file for the Customer table:

USE customer REINDEX 

REINDEX updates all index files open in the selected work area. Visual FoxPro recognizes each index file type (compound index .cdx files, structural .cdx files, and single index .idx files) and re-indexes accordingly. It updates all tags in .cdx files, and updates structural .cdx files, which open automatically with the table.

You can also update outdated index files with the REINDEX command.

Re-Indexing at Run Time

Re-indexing takes time, particularly when you re re-indexing large tables. You should re-index only when necessary. You can enhance performance by re-indexing during the initialization or termination portion of your program, rather than performing indexing maintenance during the main portion of an application.

Using Indexes to Optimize Queries

You can use indexes to speed queries and other operations. For information on creating Rushmore-optimizable index expressions, see Chapter 15, Optimizing Applications.

Using Multiple Tables

To use multiple tables, you use data sessions to control the tables that are available to forms, and work areas to set which tables are open. A work area is a numbered region that identifies an open table. You can open and manipulate Visual FoxPro tables in 32,767 work areas. Work areas are normally identified in your application by using the table alias of the table open in the work area. A table alias is a name that refers to a table open in a work area.

Using Data Sessions

In addition to the work areas visible in the Data Session window, Visual FoxPro automatically provides a separate environment for each instance of a form or form set through data sessions. A data session is a representation of the current dynamic work environment used by a form, form set, or report. Each data session contains its own set of work areas. These work areas contain the tables open in the work areas, their indexes, and relationships. For information on using data sessions, see Chapter 17, Programming for Shared Access.

Viewing Work Areas

You can see the list of tables open in a Visual FoxPro session by opening the Data Session window.

To open the Data Session window

• From the Window menu, choose Data Session.

  -or-

• Use the SET command.

When you enter SET in the Command window, Visual FoxPro opens the Data Session window and displays the work area aliases for the tables open in the current data session.

Data Session window with the Employees table open

Opening a Table in a Work Area

You can open a table in a work area with the Data Session window or with the USE command.

To open a table in a work area

• In the Data Session window, choose Open.

  -or-

• Type USE in the Command window.

To open a table in the lowest available work area, use the IN clause of the USE command with work area 0. For example, if tables are open in work areas 1 through 10, the following command opens the customer table in work area 11.

USE customer IN 0 

You can also choose Open from the File menu to open a table in a work area.

Closing a Table in a Work Area

You can close a table in a work area through the Data Session window or by using the language.

To close a table in a work area

• From the Data Session window, select the alias of the table, and then choose Close.

  -or-

• Type USE without a table name.

  -or-

• Use the IN clause of the USE command to reference the work area of the table you want to close.

When you issue the USE command without a table name and a table file is open in the currently selected work area, the table is closed. For example, the following code opens the customer table, displays a Browse window, and then closes the table:

USE customer  BROWSE USE 

You also close a table automatically when you open another table in the same work area, or issue the USE command with the IN clause and reference the current work area. The following code opens, displays, and then closes the customer table by issuing USE IN and the table alias customer:

USE customer  BROWSE USE IN customer 

You can t have more than one table open in a work area at one time.

Referencing a Work Area

You can reference the next available work area before you open a table by using the work area number as shown below:

SELECT 0 

Using Table Aliases

A table alias is the name that Visual FoxPro uses to refer to a table open in a work area. Visual FoxPro automatically uses the file name for the default alias when you open a table. For example, if you open the file Customer.dbf in work area 0 with the following commands, the default alias customer is automatically assigned to the table:

SELECT 0 USE customer 

You can then use the alias customer to identify the table in a command or function. You can also create your own alias.

Creating a User-Defined Alias

You can assign your own user-defined alias to a table when you open it.

To open a table with a user-defined alias

• Type USE with a table alias name.

For example, to open the file Customer.dbf in work area 0 and assign it an alias of people, use the following command:

SELECT 0 USE customer ALIAS people 

You must then use the alias people to refer to the open table. An alias can consist of up to 254 letters, digits, or underscores and it must begin with a letter or an underscore. Visual FoxPro automatically creates an alias if the alias you provide contains an unsupported alias character.

Using a Visual FoxPro-Assigned Alias

Visual FoxPro automatically assigns an alias to a table in certain instances:

• If you open a single table simultaneously in multiple work areas by including the AGAIN clause with the USE command and you don't specify an alias when opening the table in each work area.
• If a conflict occurs with aliases.

The default aliases assigned in the first 10 work areas are the work area letters A through J ; the aliases assigned in work areas 11 through 32767 are W11 through W32767. You can use these Visual FoxPro-assigned aliases just as you would any default or user-defined alias to refer to a table open in a work area.

Selecting a Work Area Using an Alias

You can move to a work area from another work area with the SELECT command. For example, if Customer.dbf is open in a work area and the default alias of CUSTOMER is assigned, you can move to this work area with the following SELECT command:

SELECT customer 

Referring to Tables Open in Other Work Areas

You can also refer to fields in other work areas by prefacing the field name with the alias name and a period, or the > operator. For example, if you re in a work area and you want to access the field contact from the Customer table open in a different work area, you could use the following to reference the field:

customer.contact 

If the table you want to reference is opened with an alias, you can use the alias name. For example, if the Customer table is opened with the alias people, you can reference the lastname field with the following to refer to the field:

people.lastname 

Using the table name or table alias specifically identifies the desired table independently from the work area in which the table is open.

Setting Temporary Relationships Between Tables

When you establish a temporary relationship between tables, you cause the record pointer of one table (the child table) to automatically follow the movements of the record pointer in the other, or parent, table. This allows you to select a record on the one or parent side of a relationship and automatically access the related records on the many or child side of the table relationship.

For example, you may want to relate the customer and orders tables so that when you move the record pointer in the customer table to a particular customer, the record pointer in the orders table moves to the record with the same customer number.

You can use table work areas and table aliases to establish relationships between two open tables with the SET RELATION command. If you re using a form to work with tables, you can store these relationships as part of the data environment for the form.

Temporarily Relating Tables

You can use the Data Session window or the language to create temporary relationships between tables.

To temporarily relate tables

• In the Data Session window, select tables and use the Relations button to create relationships.

  -or-

• Use the SET RELATION command.

You use the SET RELATION command to establish a relationship between a table open in the currently selected work area, and another table open in another work area. You typically relate tables that have a common field, and the expression you use to establish the relationship is usually the index expression of the controlling index of the child table.

For example, a customer may have many orders. If you create a relationship between the field that is common to both customer and order tables, you can easily see all the orders for any customer. The following program uses a field, cust_id, that is common to both tables and creates a relationship between the two tables based on the field cust_id in the customer table and the cust_id index tag in the orders table.

Using SET RELATION to Establish Relationship Between Two Tables

Code	Comments
USE customer IN 1	Open the customer table (parent table) in work area 1.
USE orders IN 2	Open the orders table (child table) in work area 2.
SELECT orders	Select the child work area.
SET ORDER TO TAG cust_id	Specify the table order for the child table using the index tag cust_id.
SELECT customer	Select the parent work area.
SET RELATION TO cust_id INTO orders	Create the relationship between the parent table and the controlling index in the child table.
SELECT orders BROWSE NOWAIT SELECT customer BROWSE NOWAIT	Open two Browse windows; notice that moving the record pointer in the parent table changes the set of data viewed in the child table.

The Data Session window displays the two open tables, Orders and Customer, and the relationship established by the SET RELATION command.

The Data Session window displays open table aliases and temporary relationships.

You created an index on the child table, orders, to organize records in the orders table into groups, according to the customer who placed the order. When you create a relationship between the parent table and the index of the child table, Visual FoxPro selects only those child table records whose index key matches the index key of the parent record you ve selected.

The previous example established a single relationship between two tables. You can also use the SET RELATION command to establish multiple relationships between a single parent table and various child tables.

Saving Table Relationships in a Data Environment

If you are creating a form that uses more than one table, you can use the data environment to create table relationships and store them with the form. Relationships you establish in the data environment are opened automatically when you run the form. For information on creating a data environment, see Chapter 9, Creating Forms.

Relating Records in a Single Table

You can also create a relationship between records in a single table. This relationship, known as a self-referential relation, can be useful in situations where you have all the information you need stored in a single table. For example, you may want to move through the managers in the Employees table and have the employees who report to each manager automatically change as you move the record pointer from manager to manager.

To temporarily relate records in a single table

• In the Data Session window, select tables and use the Relations button to create relationships.

  -or-

• Use the SET RELATION command.

To create a self-referential relation, open the same table twice: once in one work area and then a second time, with the USE AGAIN command, in another work area. Then use an index to relate the records. For example, the following code establishes and browses a self-referential relationship by creating an index tag named mgr_id that orders the Employee table by the reports_to field:

SELECT 0 USE employee ALIAS managers SELECT 0 USE employee AGAIN ALIAS employees INDEX ON reports_to TAG mgr_id SET ORDER TO mgr_id SELECT managers SET RELATION TO emp_id INTO employees BROWSE SELECT employees BROWSE 

When you move the record pointer in the managers Browse window, the employees Browse window is refreshed to show only those employees who report to the selected manager.

Setting Persistent Relationships With Indexes

Indexes are used to establish persistent relationships between tables in a database. Persistent relationships are relationships between database tables that are stored in the database file and are automatically used as default join conditions in the Query and View Designers. Persistent relationships are also displayed in the Database Designer as lines joining table indexes, and as default relationships when you use the tables in the data environment.

Unlike temporary relationships set with the SET RELATION command, persistent relationships do not need to be re-created each time you use tables. However, because persistent relationships do not control the relationship between record pointers in tables, you will use both temporary SET RELATION relationships as well as persistent relationships in developing Visual FoxPro applications. For more information on setting persistent relationships, see Chapter 6, Creating Databases.