Special Edition Using Visual FoxPro 6

Chapter 3

Defining Databases, Tables, and Indexes

• Creating Tables for an Application
  • Determining the Data Categories Needed
  • Naming Each Data Fact
  • Assigning Data Types
  • Using the Table Designer
  • Using Nulls
• Modifying Table Structures
  • Adding Fields
  • Deleting Fields
  • Renaming Fields
  • Redefining Fields
  • Defining Order in a Table
  • Examining Standalone Versus Structural and Nonstructural Indexes
  • Defining Normal and Unique Indexes
  • Defining Candidate and Primary Keys
  • Indexing on Complex Expressions
  • Selecting an Active Index at Runtime
• Appending Records
  • Adding Records Through BROWSE or EDIT Screens
  • Adding Records Programmatically
  • Appending Data from Other Tables
• Creating a Database
  • Adding Existing Tables to the Database
  • Using Primary and Candidate Keys
  • Changing Table Properties Programmatically
• Examining Compatibility Issues
  • Sharing Tables with FoxPro 2.x
  • Removing a Table from the Database

Creating Tables for an Application

Suppose that you want to track orders for a small business. After thinking about what information you collect when taking an order, you might create the following initial information list:

• Order date
• Customer name
• Customer address
• List of items purchased
• Quantity of each item purchased
• Unit price of each item purchased
• Tax on each item purchased
• Extended order amount
• Method of payment

This list uses at least four obvious data categories to store information about an order. The first category pertains to customer information. The second category tracks information common to orders but not specific to individual items ordered, such as the order date, the total order amount, the method of payment, and so on. The third category contains the order details. In this category, you can envision one record for each item purchased in each order. Finally, the fourth category stores details on each product, such as product identification, description, and price.

Determining the Data Categories Needed

A logical question to ask at this point is exactly what information about the customer you need. Could you use more than a name and address? What happens if you need to back-order an item? Can you telephone the customer to pick up the order when you receive it? Does the customer have multiple shipping addresses but a central billing location? If so, which address do you enter? What are the customer's sales to date? Do you want to offer discounts to frequent customers? Have all the customer's outstanding bills been paid? Should you mail new orders to customers who have not paid their bills?

There might be even more questions, but the point is that now-during system and table design-is the time to ask these questions, not after the program has been created.

Suppose that after asking these questions (and for the sake of keeping this example fairly simple), you decide to track the following customer information:

• Company name
• Contact name
• Address
• Telephone number
• Fax number
• Outstanding bills total
• Purchases YTD (year to date)
• Standard payment method
• Credit card number
• Credit card expiration date
• Preferred shipper
• Date of last purchase

A review of the customer information, using the preceding list, could reveal several problems.

You have several orders from large corporate customers that have different shipping and billing locations. You further realize that simply storing the customer contact name might not be the easiest way to search the file if you know only a last name. Therefore, you need to break the name into first- and last-name fields. Similarly, an address consists of several components, including one or more street address lines, city, state or province, and postal code. Each of these components needs a separate field. Even a telephone number might not be sufficient detail for customers that have extension numbers.

What you are accomplishing with this task is atomizing the customer's information. Each atom defines a single element that further defines the customer. The following list shows the results:

• ID (a unique identifier for a customer)
• Company name
• Contact first name
• Contact last name
• Billing street address
• Billing city
• Billing state/province
• Billing postal code
• Shipping street address
• Shipping city
• Shipping state/province
• Shipping postal code
• Telephone number
• Telephone extension number
• Fax number
• Outstanding bills total
• Purchases YTD
• Standard payment method
• Credit card number
• Credit card expiration date
• Preferred shipper
• Date of last purchase

In your customer system, you might require even more fields. However, the preceding fields serve to show you how to create a table.

Naming Each Data Fact

Now you need to define a field name for each data element. Traditionally, FoxPro limited field names to 10 characters. The first character was restricted to an alphabetic character; thereafter, FoxPro accepted any characters with the exception of a space. With Visual FoxPro, however, you can define field names with up to 128 characters, but only if the field is in a table defined in a database. If you initially define a standalone table, called a free table, you must follow the 10-character limit.

Many field-naming conventions have been devised in previous versions of FoxPro to help make programs more readable. The conventions differentiate variables by type, by scope, and by whether they are memory or table variables. One common method defines fields in a table beginning with a two-character prefix followed by an underscore. This prefix identifies the table and is unique in the application. Using table identifiers, however, can become unacceptable in a couple of cases:

• If you plan to implement your own data dictionary, you will have fields that have common data descriptions and definitions between tables. In this case, you want to keep the same field name in each table.
• If you plan to transfer data between tables using memory variables created with SCATTER, you cannot have table identifier prefixes in the field names for GATHER to successfully replace the proper values. In the examples for this chapter, you will not use the table identifiers for these reasons.

Today, the generally recommended convention is to start each field name with a single character that identifies its field type.

In this naming convention, memory variables also begin with two characters: the first represents its scope and the second represents its type. The third character could be an underscore; however, the use of underscores is no longer encouraged. Table 3.1 shows possible character prefixes.

Assigning Data Types

Deciding what information to save in a table is only half the battle in planning a new table structure. Next, you need to examine each data item and determine whether you should store it as Character, Numeric, Date, or some other data type. Furthermore, for Character and Numeric fields, you need to determine the number of required characters.

You can begin by looking at the available data types provided by Visual FoxPro.

Character  Character is the most common data type in most tables. Character fields store 1 to 254 characters, consisting of printable characters such as letters, numbers, spaces, and punctuation marks. Certain characters, such as CHR(0), cannot appear in a regular Character field. You must define fields that require more characters as memos.

Character fields have a fixed size. If you define a field such as Address with 35 characters, it consumes 35 characters in every record, even if Address equals 15 Main Street. Although this might sound trivial, the difference of 5 characters in a 300,000-record file is more than 1.4MB. On the other hand, if Address requires more than 35 characters, Visual FoxPro stores only the first 35 and truncates the rest.

You can even use Character fields that consist entirely of numbers. For example, you should store ZIP codes, telephone numbers, and even customer IDs as character fields, for several reasons. First, numeric fields truncate leading zeros. Therefore, if you save a ZIP code such as 01995, Visual FoxPro would store it as 1995. Second, you might want to format a telephone number field as (215)084-1988. Finally, you might need to combine a field such as a customer ID with another field to form an index. Usually, you can combine fields into a single index expression only by concatenating character strings.

Perhaps a better way to determine whether to make a field Character or Numeric is to ask, "Will I ever perform calculations on this field?" If you answer yes, you might want to store it as Numeric; otherwise, store it as a Character. An exception is a numeric ID field. Even though you might need to increase the size of the ID field incrementally for new records, it works best as a right-justified character field with blank or zero padding to the left to fill the field. For example, you would zero-pad ZIP codes in a five-character field. You might also zero-pad customer ID numbers. Listing 3.1 shows one method of enlarging a zero-padded character customer ID.

Listing 3.1  03CODE01.PRG-Enlarging a Zero-Padded Character Customer ID

SELECT CUSTOMER

APPEND BLANK

REPLACE cCustomerId WITH INCR_ID()



FUNCTION INCR_ID

*********************************************************

*

* FUNCTION INCR_ID Increments a character ID that contains

*                  only digits

*

* Designed specifically for CUSTOMER..cCustomerId

*

**********************************************************

LOCAL pnCurDec, pnCurRec, pcCurTag, pcNewId

* Capture current position in file, # of decimals, and tag

  pnCurDec = SYS(2001, 'DECIMAL')

  pnCurRec = RECNO()

  pcCurTag = TAG()

  SET DECIMALS TO 0



* Get last customer id used

  SET ORDER TO TAG CUSTID

  GOTO BOTTOM



* Calculate the next available ID

  pcNewId = PADL(VAL(cCustomerId)+1, 6, '0')



* Reset file position and tag, return next available id

  SET ORDER TO TAG (m.pcCurTag)

  SET DECIMAL TO EVAL(m.pnCurDec)

  GOTO pnCurRec

RETURN m.pcNewId

Enlarging an alphanumeric ID is more difficult, but Listing 3.2 finds the numeric portion of a field and expands it incrementally.

Listing 3.2  03CODE02.PRG-This File Locates the Numeric Portion of a Field and Expands It Incrementally

USE CUSTOMER

APPEND BLANK

REPLACE cCustomerId WITH INCR_ID2()



FUNCTION INCR_ID2

*********************************************************

*

* FUNCTION INCR_ID2 finds the numeric portion of an id

*                   embedded in an alphanumeric field

*

* Designed specifically for CUSTOMER.cCustomerId

*

**********************************************************

LOCAL pnCurDec, pnCurRec, pnStartNum, pnEndNum, ;

      pnIdNum,  pcCurTag, pcNewId

* Capture current position in file and current tag

  pnCurDec = SYS(2001, 'DECIMAL')

  pnCurRec = RECNO()

  pcCurTag = TAG()

  SET DECIMALS TO 0

  STORE 0 TO pnStartNum, pnEndNum



* Get last customer id used

  SET ORDER TO TAG custid

  GOTO BOTTOM



* Find start and end of numeric portion of field

  FOR i = 1 TO LEN(cCustomerId)

    IF ISDIGIT(SUBSTR(cCustomerId, i, 1)) AND ;

               pnStartNum = 0

pnStartNum = i

    ENDIF

    IF NOT ISDIGIT(SUBSTR(cust_id, i, 1)) AND ;

      pnStartNum>0 AND ;

      pnEndNum = 0

      pnEndNum = i

    ENDIF

  ENDFOR



* Check if there is a numeric portion

  IF m.pnStartNum = 0

    = MESSAGEBOX('There is no numeric portion to this id')

    RETURN cCustId

  ELSE

  * If no alpha suffix, fix end of number position

    IF m.pnEndNum = 0

      pnEndNum = LEN(cCustomerId) + 1

    ENDIF

  ENDIF



* Extract numeric portion of last id

  pnIdNum = SUBSTR(cCustomerId, m.pnStartNum, ;

             m.pnEndNum - m.pnStartNum)



* Calculate the next available customer id

  pcNewId = PADL(VAL(m.pnIdNum) + 1, ;

             m.pnEndNum - m.pnStartNum, '0')



* Reconstruct entire id

* Add alpha prefix

  IF m.pnStartNum = 1

    pcNewId = m.pcNewId

  ELSE

    pcNewId = SUBSTR(cCustomerId, 1, m.pnStartNum - 1) + ;

              m.pcNewId

  ENDIF

* Add alpha suffix

  IF m.pn_endnum <= LEN(cCustomerId)

    pcNewId = m.pcNewId + SUBSTR(cCustomerId, m.pnEndNum, ;

              LEN(cCustomerId) - m.pnEndNum + 1)

  ENDIF



* Reset file position and tag, return next available id

  SET ORDER TO TAG (m.pcCurTag)

  SET DECIMALS TO EVAL(m.pnCurDec)

  GOTO m.pnCurRec

RETURN m.pcNewId

Currency  To store dollar amounts, consider using a special numeric type called Currency. As a maximum amount, Currency can store a little more than $922 trillion. Currency defaults to a maximum of four decimal places and requires a fixed eight bytes of storage in a table.

There has been a lot of publicity in the media about the so-called year 2000 bug. Supposedly, when the clock turns over to the year 2000, some or all computers in the world that are not prepared will resort back to the year 1900 or other strange happenings will occur, causing chaos all over the world. Most of the problems associated with this factor will be rectified by the fact that most computers being built at present will work when we reach that spectacular date. Other companies and developers have been working on the problem and by the time the problem occurs, all should be solved. If you own an older personal computer, the safest solution is to upgrade or purchase a new one before that year. Most developers will already have the solution and most normal users will not have to worry about it.

Unlike Double fields, Float and Numeric fields enable you to specify the number of bytes required, because FoxPro stores the ASCII code for each digit in a separate byte. Therefore, if a field value always uses integer values less than 100,000, a field width of 6 with zero decimal places will suffice. To optimally size a Numeric field, try to determine the largest and smallest values possible. Sizing a field too small for the values that are being stored forces FoxPro to store asterisks in the field.

Logical  Logical fields store binary information in the form of .T. or .F.. Logical fields store information with only two states, such as taxable versus nontaxable, male versus female, and shipped versus back-ordered. Forms often use Logical fields as the source for check boxes.

Memo  Memo fields not only store large character strings (greater than 254 characters), but they also provide a variable amount of storage per record based on block size. A block is a fixed number of characters that FoxPro reserves for a Memo. By default, FoxPro uses 64 bytes per block. This means that each group of 64 characters in a text string requires an additional block. If you have a string of 72 bytes, the extra eight bytes require a second block of 64 characters.

You must use SET BLOCKSIZE before adding the first record with a memo. When you add the first memo, FoxPro embeds the current block size in the memo file. To change the block size of an existing memo file, you must rewrite each Memo field. However, regardless of the block size, remember that the first block reserves eight bytes for the pointers.

Why should you worry about the block size? The larger the block size, the more wasted space memos consume if they vary greatly in length. On the other hand, the more blocks FoxPro needs to store a memo, the less efficiently it retrieves the memo. The practice sounds more like art than science, but in general, you want to define the block size as the most likely memo length.

FoxPro stores memos in a file, separate from the .DBF, that has the extension .FPT. Whether you have one Memo field or several in a table, FoxPro stores all memos in this one .FPT file. In fact, if you have General fields, FoxPro stores them in the same file as memos. Pointers from the .DBF file keep track of what information belongs to each record and field.

Because memo pointers point only one way-from the .DBF to the .FPT-you need to ensure that .DBF and .FPT files never get separated. How can this happen? Perhaps you have a table that contains a Memo field on two or more machines. If you copy (by accident, it is assumed) the .DBF from one machine to another without also copying the .FPT file, the copy could be out of synchronization with the current .FPT. If this happens and you add records before realizing the problem, you might find that the memo text no longer corresponds with the proper records. It is almost impossible to fix this problem without manually resetting the pointers from the .DBF to the .FPT. Third-party tools are available to perform this very complex task.

The following are typical uses for Memo fields:

• Character fields that only occasionally contain text
• Character fields that vary greatly in length or whose length cannot be predicted
• Text files, such as resumés, letters, and historical archiving of program versions

Using the Table Designer

Suppose that today is the start of a new project and you want to use the Project Manager to organize your files. You need to create a project first. As is true of most functions, FoxPro provides several methods of performing this task, such as using VFP commands, menu-driven options, or the Project Wizard.

To start, choose File, New from the main system menu. The New dialog box appears (see Figure 3.1).

Figure 3.1 : Create a new project using the New dialog box.

Choose Project as the file type, and click New File. Because FoxPro requires a name to identify any file, it first opens the Create dialog box to request a project filename (see Figure 3.2). FoxPro uses the same dialog box for all filename requests.

Figure 3.2 : Name a new project file using the Create dialog box.

The Create dialog box displays the current or default directory. If you already have projects defined in this directory, they appear dimmed in the list box so that you cannot select them. They serve as a reminder not to use the same project name twice. You can switch to another directory or drive to store the project, but after you select a directory, you must enter the new project filename manually.

If you prefer a more direct method when creating tables and don't mind typing, enter the filename in the Command window as follows:

     CREATE PROJECT PTOFSALE

Using a command is quicker, but you must learn basic command syntax and you must already have a valid name and directory under which to store the project.

Figure 3.3 : Create a new free table by selecting the Data page in Project Manager and clicking New.

VFP next prompts you for a filename, this time for the table. For this example, call the table CUST.DBF.

Finally, FoxPro displays the Table Designer dialog box. This form has a page-frame object with three pages. The first page defines the table structure, the second one defines the indexes, and the third shows the status of the table. The table structure page should appear by default. If not, you need only click the Table page, as shown in Figure 3.4.

Figure 3.4 : The Table Designer displays completed field definitions for CUST.DBF.

To define the table structure, enter the field information shown in Table 3.2, which includes field types, widths, and decimals. Notice that each customer is identified by a unique customer ID. Type the customer ID in the first field.

Pressing Tab moves you to the Type field, which defaults to Character. This field uses a drop-down list. If you click the down arrow to the right of the field, a drop-down list appears showing possible values. For this example, simply accept the default type: Character.

Specify how many characters to reserve for this field. Character fields default to 10, but you can use the spinner to change that setting to any value from 1 to 254. Alternatively, you can edit the number directly. A nice feature of the spinner is that it starts slowly and picks up speed as you continue to click the arrow.

The next column displays the index direction, if an index exists, for the field.

The last column in the structure determines whether the current field allows null values. Null use is discussed later in this chapter, so you can skip this column for now.

After you enter the structure of the CUST file, the Table Designer dialog box should match the one shown in Figure 3.4. You can see only nine fields at a time, but by using the vertical scrollbar, you can move through all the table fields.

The buttons to the left of the field names enable you to rearrange the default field order when issuing a Browse or List command for the table. To make cFirst appear before cLast, click the button to the left of cFirst and drag the field up. While you hold down the mouse button, FoxPro displays a dotted box at the proposed new position of the field. When the field is in the desired position, simply release the mouse button; all subsequent fields adjust their positions.

When you have entered the complete table structure, click OK to exit the Table Designer and save the table. Because this table is new, FoxPro asks whether you want to input data records now. FoxPro assumes that because you created a table, you probably want to store data in it, but that is not always true. You might want to append data into a new table from an existing one, or you might want to add data by using a form. For now, click No and return to the Project Manager.

The name of the table that you just created now appears in Free Tables in Project Manager. To see it, click the plus sign to the left of the Free Tables line. The table has two symbols before it: a plus sign and a circle with a slash through it. In the Project Manager, the plus sign indicates additional levels that are currently hidden or rolled into the current line. If you click the plus sign next to a table name, Project Manager rolls out the table's fields. Notice that the plus sign changes to a minus sign. This change indicates that all levels are currently open. FoxPro uses the second symbol (the circle with the slash through it) during compilation to indicate that it should not include this file in the compilation. Project Manager should now look like Figure 3.5.

Figure 3.5 :The Project Manager shows the fields in CUST listed below the table name.

Using Nulls

In earlier versions of FoxPro, you could not determine whether a user intentionally left a field blank or merely forgot it. FoxPro interprets an empty field as being an empty character string, a numeric zero, or a logical False, depending on the field type. Any of these values could be legitimate entries for the field. If an employee file contains a field for the employee's middle initial, for example, does a blank mean that the employee did not enter his middle initial or that he does not have one? With numeric data, would a year-to-date field with a value of zero indicate that the customer had no transactions or that the sum of purchases and returns exactly canceled each other? Finally, with Logical fields, you cannot determine whether the user actually selected a false value or merely skipped the field. Suppose that John Smith missed the question "Are you married?" for which False represents NO? Mrs. Smith might have a question or two about that.

To use null tokens in a field (any field type), you must do two things. First, you must specify SET NULL ON in the Command window or in the program. Second, you must modify the structure and click the Null column button for each field that allows nulls. If you do not do this, FoxPro displays an error when you append a record with APPEND FROM or INSERT SQL and do not include a value for this field. FoxPro places the token .NULL. in fields that allow nulls and contain no values.

• By default, APPEND BLANK does not trigger a null token in all fields of a new table record.
• When you are changing a Character field from a non-null field to allow nulls, blank fields remain blank.
• When you are changing a Numeric field from a non-null field to allow nulls, fields with values of zero remain zero.
• When you are changing a Character field from a null field to disallow nulls, a blank string is placed in the field.
• When you are changing a Numeric field from a null field to disallow nulls, a zero is placed in the field.

Modifying Table Structures

At some point in every project's life, you can expect to modify a table's structure. The severity of a modification can be classified by how much it changes the table and index files.

Adding a field, for example, is a small change because it has no impact on existing fields, although it does require rewriting the entire .DBF. Renaming a field also requires only a minimal change; in fact, it usually does nothing more than modify the .DBF header. However, if you rename a field that appears in an index, that index or tag must also be updated. Deleting fields, as long as they are not part of an index or tag, requires rewriting the entire .DBF but little else. On the other hand, modifying field sizes or the number of decimal places forces FoxPro to rewrite the entire .DBF, and can result in the loss of data. When you are changing the field type, FoxPro attempts to automatically convert the data to the new type, but it can also automatically trash the data if it does not know what to do or if the conversion does not make sense.

The following section examines various changes that you can make to table structures and explains the effects of those changes.

Adding Fields

Adding new fields to an existing table is one of the safest changes you can make. In fact, problems occur only if you attempt to use the same field name twice. Even in that situation, FoxPro responds automatically.

FoxPro will not enable you to exit the name column if it is a duplicate of an existing field name. Rather, FoxPro displays an information box with the message Invalid or duplicate field name and enables you to edit it.

Deleting Fields

At some point, one or more fields in a table might become obsolete. Rather than waste space, you might decide to remove them from the table. To delete a field, simply display the table in the Table Designer dialog box (refer to Figure 3.4), highlight the obsolete field, and click the Delete button.

Renaming Fields

Renaming fields might require only rewriting the .DBF header with the new field name. To change a field name, simply open the Table Designer dialog box, highlight the name, edit the name, and then save the structure.

As long as an open index does not reference the field, FoxPro renames the field when you save the structure. If the renamed field appears in an open index, FoxPro displays the warning shown in Figure 3.6.

Figure 3.6 : This alert box appears when you are saving the modified structure and renaming a field used in an index.

When you click OK in this alert box, Visual FoxPro returns you to the Index page of the Table Designer to correct the index expression. You can redefine the expression by renaming the field name in the index expression. VFP does not automatically rename the field in the index expression when you rename a field used in it. Why isn't FoxPro smart enough to simply substitute the new field name for the old one in all the tags? FoxPro probably doesn't know whether you really mean to rename a field or to replace it with a new field.

Redefining Fields

Redefining field types, widths, or decimal places can be simple or complex depending on the change. For example, you can open the Table Designer dialog box, highlight a field, and increase the size of the field with no problem at all; FoxPro rewrites the .DBF to expand the field size. However, Character fields merely get blanks appended to them, and Numeric fields have more available digits to use. You can even change the size of an indexed field without any problems. Visual FoxPro regenerates the index when you close the Table Designer.

On the other hand, decreasing the size of the field or number of decimals can cause data loss. FoxPro accepts the change and asks whether you want to update the table structure when you leave the Table Designer dialog box. If you continue, FoxPro resizes the fields as requested. Character fields are shortened by truncating existing text to the new field size. Numeric data can lose decimal places or digits when it is shortened. Reducing the number of digits in the integer portion of the number can cause some values to be replaced by asterisks, thereby causing you to lose the entire number. On the other hand, VFP happily truncates decimal places.

Some changes in field types are more likely to cause disaster than others are. Changing a Numeric field to a Character field simply converts the number to a string, as though FoxPro used the STR function when it rewrote the table. Similarly, changing a String to a Numeric field appears to use the VAL function. Converting strings with leading numbers results in the numbers being saved to a Numeric field. FoxPro converts strings that begin with alpha characters (other than blanks) to zero when they are converted to numeric values.

Similarly, you can convert Date fields to Character strings by changing their type. FoxPro appears to transform the data by using the DTOC function. You can even change character-formatted dates back into true Date fields.

Most other conversions result in a loss of data. Again, the need to make a backup copy of a table before making any structural change cannot be emphasized enough.

Defining Order in a Table

No one would expect users to enter data in a sorted order (unless they key the entries in from a telephone book). Certainly, customers don't arrive at a business in alphabetical order or buy products sequentially by product ID. Wouldn't it be nice if they did? Because they don't, you must add records in random order, although you probably will want to view them sorted by one or more fields.

You can use the SORT command to reorder the records in a table. SORT takes an existing table and creates a new one sorted by a field or a combination of fields. The following command creates a new table called CUSTLIST that is sorted by the customer's last name:

SORT TO CUSTLIST ON Last

A more complex SORT creates a new table called CURCUST, which contains customers (sorted in descending customer ID order) who have made purchases this year. The following is the appropriate command to create the new table.

SORT TO CURCUST ON cCustId /D FOR Goods_Ytd>0

This method has two flaws. First, every new sort order duplicates the entire original table or filtered portion thereof. If you need several sort orders for a large table, you can quickly run out of disk space. Second (and more important), having more than one copy of a table inevitably leads to data inconsistencies. If you do not update all tables simultaneously, you soon will have several tables, each of which has some, but not all, of the recent updates.

Sorting does have its place. If you have a rarely changed table that has one preferred sort order, you might want to keep a sorted version of it. However, indexes provide a more effective way to enable users to view and retrieve data from a table in an orderly manner. Because a table can have more than one index, you can define different indexes for different views or reports.

Examining Standalone Versus Structural and Nonstructural Indexes

When indexes were first developed for database systems, they required a separate index for each index definition. To index the CUST table on both the customer number and their last name, for example, you would create two indexes, as shown in the following example:

USE CUST

INDEX ON cCustId TO CUSTID

INDEX ON cLast TO CUSTNAME

These statements would create two index files: CUSTID.IDX and CUSTNAME.IDX. These files are now referred to as standalone indexes, because each index file contains a single index entry and is independent of the others. You can have any number of standalone indexes defined for a given table, limited only by the FILES statement in CONFIG.SYS. When you open the table, you might open all indexes, as in the following example:

USE CUST INDEX CUSTID, CUSTNAME

Alternatively, you can open the table with only a single index:

USE CUST INDEX CUSTID

In both cases, the first index after the keyword INDEX controls the order in which FoxPro accesses the table records. In the first example, FoxPro updates and maintains both indexes if you add, delete, or modify records. In the second case, FoxPro maintains only CUSTID.IDX; in this case, FoxPro has no knowledge of CUSTNAME. If you make changes to CUST.DBF, CUSTNAME can lose synchronization with the table. In other words, the index might no longer point to the right records.

Finally, you can open each index separately by using the SET INDEX statement as follows:

USE CUST

SET INDEX TO CUSTID

SET INDEX TO CUSTNAME ADDITIVE

Now FoxPro opens both indexes, and CUSTID controls the access order. Notice the keyword ADDITIVE in the second SET INDEX statement. If you did not include that keyword, FoxPro would close CUSTID before opening CUSTNAME.

The problems with standalone indexes should be obvious. Because the names usually have no relation to their .DBF, you can easily forget which indexes belong to each table. In fact, your directories can soon become littered with obsolete and forgotten indexes that you no longer need, and no one remembers to which tables the indexes belong.

Furthermore, if you do not open all the indexes when you edit the table, FoxPro does not update the missing indexes. The indexes might point to the wrong records or even beyond the end of the table after you pack deleted records.

With the introduction of FoxPro, you now have structural and nonstructural indexes, also called compound indexes. These files are special index files that can contain several index definitions in one physical file. Now you can store all index definitions for one .DBF in a single file. You no longer need to worry about forgetting to open an index file or encountering nonsynchronized index pointers.

You define a compound index as shown in the following example:

USE CUST

INDEX ON cCustId TAG CUSTID OF CUSTSORT

INDEX ON cLast TAG CUSTNAME OF CUSTSORT

USE CUST INDEX CUSTSORT

The USE statement opens the CUST table, along with a nonstructural index called CUSTSORT. FoxPro calls a compound index nonstructural when its base name differs from the .DBF-in this case, CUSTSORT versus CUST. You can make the index structural by giving it the same base name as the .DBF, as in the following example:

USE CUST

INDEX ON cCustId TAG CUSTID OF CUST

INDEX ON cLast TAG CUSTNAME OF CUST

USE CUST

In this case, you did not include the INDEX clause of the USE statement, but the index-CUST.CDX-opens anyway. When the structural index exists, FoxPro automatically opens it when you open the table. There is no way to forget to open the indexes if you store index expressions as tags in a structural index. Structural indexes should never get out of synchronization, although that is possible: one way would be to accidentally copy a different version of the .DBF or .CDX to the current directory.

Defining Normal and Unique Indexes

To create indexes for CUST.DBF, return to the Table Designer dialog box. Click the Indexes page to switch to the index definition page (see Figure 3.7).

Figure 3.7 : The index definition screen displays four index definitions in the Table Designer.

Four indexes are already defined. Index definitions begin with the tag name on the left, followed by the index type (accept the default, Regular), the tag expression, and a filter. Notice that FoxPro automatically assumes that you want to create a structural index. If you want to create a standalone index or a nonstructural index, you need to enter the syntax described in the preceding section into the Command window.

The arrows to the left of the names indicate whether the index is in ascending (up arrow) or descending (down arrow) order. To change the direction, select the row and click the button that appears with the arrow to toggle between ascending and descending.

A regular index type means that FoxPro stores the value generated by the index expression for each table record in the index. If more than one record has the same expression, FoxPro stores the expression multiple times with separate pointers to each record.

In the CUST table, the last name might not uniquely identify every record in the table. You might have customers Bill Jones and Kevin Jones. Therefore, an index on the last name has repeated values, but you can use it as a regular index.

By clicking the down arrow next to Type, you can see another index type called Unique. Unique includes only unique expressions in the index. If more than one record generates the same index expression value, Unique stores only the first one encountered. If you define a unique index on Last in the CUST table, you cannot include every record in the index. Therefore, either Bill Jones or Kevin Jones would appear, but not both.

Defining Candidate and Primary Keys

The third index type, called candidate, creates a unique index, but the index includes every record in the table. Candidate indexes prohibit duplicate expression values for any two records in the table. What if you decide to change an existing regular index to candidate? After making the change, VFP prompts you to save the structure modification. Also appearing in the dialog box is a check box for checking data against the index. Whether you choose to or not, if you currently have duplicate data corresponding to the index definition, VFP warns you of this uniqueness error and changes the index back to regular. Your data will have to be modified before making this change.

Indexing on Complex Expressions

FoxPro does not restrict index expressions to single fields. In fact, any combination of fields can serve as an index expression. Beware of making an expression overly complex just to make it a candidate index. You might include Last_Name and First_Name to make a candidate index. But what if you have customers Jim T. Kirk and Jim C. Kirk? You might want to add another field to the index for the customer's middle initial. But such an index does not guarantee uniqueness. Some programs attempt to combine portions of a customer's last name with the last four digits of their phone number plus their ZIP code (maybe even age, sex, and title of their favorite Star Trek movie). It's easier to just assign a sequential customer ID to a new customer.

To build a complex expression, click the button to the right of the Expression text box in the Table Designer dialog box. FoxPro displays the Expression Builder dialog box (see Figure 3.8).

Figure 3.8 : Use the Expression Builder dialog box to create complex index expressions.

FoxPro uses the Expression Builder dialog box with many operations to help build complex expressions. The Functions section provides access to FoxPro's built-in functions, which are divided into four groups: Date, Logical, Math, and String. Click the arrow button to the right of a function field to display a drop-down list of functions. To select a function, highlight it and then press Enter or simply click it. FoxPro displays the selected function in the function list box and the expression text area. For a function, FoxPro automatically places the insert cursor inside the parentheses. Now you can enter the parameters.

Below the Expression list box, FoxPro lists the fields in the current table. You can choose a field to be added to the insert position of the expression; just highlight the field and press Enter. To select a field from a different table, click the arrow to the right of the From Table text box. Only open tables that appear in this list. If you need to reference a table that is not currently open, exit the Expression Builder dialog box, open the table in a new work area, and then reenter the Expression Builder dialog box. You can create indexes on fields from other tables, although that practice is not recommended. However, you do need the capability to access other tables in other situations that use the Expression Builder.

Finally, the Variables list box contains the current memory and system variables. This feature generally does not apply to defining indexes, but remember that FoxPro uses the Expression Builder dialog box in many places.

Figure 3.8 shows an index that alphabetically displays companies that have outstanding bills using cCompanyName and nOutstandingBillsTotal. Because cCompanyName is a Character field and nOutstandingBillsTotal is Numeric, you cannot simply combine them with a plus sign. To combine two or more fields of different data types, you must convert one or more of them to a common type-usually, Character. Use the STR function to convert nOutstandingBillsTotal to a string before concatenating it to cCompanyName.

Finally, before you click OK and accept an expression, FoxPro provides a utility that verifies the syntax. The Verify button checks the syntax and displays an error message if it cannot interpret the expression. Common reasons for invalid expressions include mismatched parentheses or a missing comma. For valid expressions, a message appears in the status bar telling you that the expression is valid.

Including User-Defined Functions ot only can you use FoxPro's predefined functions, you can also define your own. A user-defined function is any group of statements stored as a separate file or as a separate procedure or function in a program.

Suppose that you want to create an index based on sales regions. First, you need a separate table that relates ZIP codes to regions. Then you create a small function, similar to the one shown in Listing 3.3, to find the region.

Listing 3.3  03CODE03.PRG-This Function Locates the Region of the Country that a ZIP Code Refers To

FUNCTION GETREGION

LPARAMETER lcZipCode

**********************************************

*

* This function uses file ZIPREGN with the

* following structure:

*

*    StartZip    C(10)

*    EndZip      C(10)

*    Region      C(10)

*

* All zip codes that fall in a zip code range

* defined by a record are assigned to that

* region. Otherwise, the region is left blank.

*

**********************************************

LOCAL lcCurNear, lcCurArea, lcRtnRegion, lcA



* Use an inexact search

  lcCurNear = SYS(2001, 'NEAR')

  SET NEAR ON



* Store current work area - VFP supports 32767 work areas

  lcCurArea = SELECT()  &&Retrieves current work area number



* Check if ZIPREGN IS OPEN

  IF !USED('ZIPREGN.DBF')

     lcA = ('\VFP5BOOK\DATA\ZIPREGN.DBF')

     USE (lcA)

  ELSE

     SELECT ZIPREGN  &&selects work area where ZIPERGN is open

  ENDIF



* Check if controlling index is on STARTZIP

  IF !TAG() = 'STARTZIP'

     SET ORDER TO TAG STARTZIP

  ENDIF



  = SEEK(lcZipCode)

* Check if an exact match was found

  IF FOUND()

    lcRtnRegion = Region

  ELSE

  * Check if on last record

    IF EOF()

      GOTO BOTTOM

      lcRtnRegion = Region

    ELSE

      SKIP -1

      lcRtnRegion = Region

    ENDIF

  ENDIF.



* Check if beyond last zip code in range

  IF lc_ZipCode > ZIPREGN.EndZip

    lcRtnRegion = SPACE(10)

  ENDIF



* RESET  environment and area

  SELECT (lcCurArea)

  SET NEAR &lcCurNear



RETURN lcRtnRegion



* END OF FUNCTION GETREGION

GETREGION(CUSTOMER.cBillingPostalCode)

Using Stored Procedures  The major disadvantage of using a user-defined function in an index expression is that FoxPro must be able to find the function to modify the index. Because you cannot store the function with the index, it can be easily misplaced, deleted, or forgotten when you transfer the table to another system. Thanks to the database container, you do have an alternative.

If the table is bound to a database, user-defined functions can be stored in the database, thus eliminating the search for a procedure or function.

For a standalone index, the index expression can consist of up to 220 characters. FoxPro limits the resulting index value to 100 characters. If you define the standalone index as a compact standalone index (a special case of standalone indexes that requires less total disk space), the index expression s hares space with the FOR expression. The combined length of both expression strings cannot be more than 512 characters. The individual index value cannot exceed 240 characters. A similar requirement applies to compound indexes. Compound indexes are nothing more than compact indexes with repeated tags; thus, they have the same limitations.

Selecting an Active Index at Runtime

In applications that have standalone indexes, you can easily determine the active index. If a USE statement opens more than one index, FoxPro makes the first one in the list the active one by default. To change to a different index, use SET ORDER to change the controlling order. The following statements open CUSTOMER with two standalone indexes (CUSTID and CUSTNAME), the SET ORDER statement changes the controlling index from CUSTID to CUSTNAME:

USE CUSTOMER INDEX CUSTID, CUSTNAME

SET ORDER TO 2

To be clearer, you can also specify the index name rather than its position number in the list.

If the USE statement does not open the index files, issue the SET INDEX command to both define the index list and set the controlling index, as follows:

USE CUSTOMER

SET INDEX TO CUSTID, CUSTNAME ORDER 2

These options continue to work in Visual FoxPro. However, with the introduction of compound indexes, in which each index has a tag name, you need to add a TAG argument such as the following:

USE CUSTOMER

SET ORDER TO TAG CUSTNAME

In this example, assume that CUSTOMER has a structural index with tags named CUSTID and CUSTNAME. Although FoxPro automatically opens a structural index, it does not automatically set a controlling index. You need to use a command such as SET ORDER to select a tag.

USE CUSTOMER INDEX CUSTZIP

Notice that you did not specify the structural index; FoxPro opens it automatically. Furthermore, CUSTZIP-not the structural index-has precedence in the index order over a standalone index. Therefore, CUSTZIP controls the record-viewing order in this case.

Appending Records

You create tables to add records to them. In fact, FoxPro is so sure that you want to populate a table with records as soon as possible that it prompts you to add records immediately on closing the table structure. Of course, this time might not always be the best time to add records. You might not even be the person to do the adding. But somewhere, sometime, someone (or some program) will add records to the table.

You can add records to a table in several ways. The following sections explore these options.

Adding Records Through BROWSE or EDIT Screens

Many casual FoxPro users begin by opening an Append window. To do so yourself, simply open the table and type APPEND in the Command window as shown in the following:

USE CUSTOMER

APPEND

These commands open an edit window, add a blank record, and place the cursor in the first field, as shown in Figure 3.9. After you enter data in the fields of the first record, FoxPro automatically adds another blank record and places the cursor in it. This process continues until you close the window.

Figure 3.9 : APPEND opens this simple edit window when you add records.

You also can open the Edit window directly with the EDIT command or open a Browse window with BROWSE. In these windows, FoxPro assumes that you want to edit or view existing records. You cannot simply move the cursor to a record beyond the last one to start entering a new record. Rather, you must press Ctrl+Y or open the Table pull-down menu and choose Append New Record.

Adding Records Programmatically

You can append records to a table from within a program in several ways depending on the source of the records. The code that follows enables users to add a new table record and update the fields directly.

* Code appends record to table and updates fields directly

  SELECT <table>

  APPEND BLANK



* Call a previously created form that allows the user

* to enter fields

  DO FillForm

However, you might not always want to modify the table directly. Many programmers prefer to modify memory variables that represent each of the table's fields (although buffering techniques in VFP are preferred). The sample code shown next creates a set of memory variables from a table and, after entering values for the memory variables, saves them to the table on request.

* Code creates a set of memory variables from table

  SELECT <table>

  SCATTER MEMVAR MEMO



* Call a previously created form that allows the user

* to enter fields

* Function returns .T. if user clicks the SAVE button to exit

  SaveIt = FillForm()



* If user clicks SAVE, append a blank record and

* gather the memory variable fields

  IF SaveIt

    APPEND BLANK

    GATHER MEMVAR MEMO

  ENDIF

The second example improves on the first because it does not add a new record to the table until the user decides to save the data.

Appending Data from Other Tables

Suppose that you want to append records to the current table from a second table. You could read through one record at a time, store the fields in memory variables, and then append these values to a record in the second table. The code in Listing 3.4 shows one implementation.

Listing 3.4  03CODE04.PRG-One Method of Appending Records to the Current Table from a Second Table

SELECT EMPLOYEE

SCAN

  SCATTER MEMVAR

  SELECT EMPL9

  APPEND BLANK

  GATHER MEMVAR

  SELECT EMPLOYEE

ENDSCAN

Rather than scatter values to memory variables, you can use an array to store the field values from a single record. The code in Listing 3.5 illustrates this method. (Remember that all the code listings are available on the Macmillan Web site at http://www.mcp.com/info.)

Listing 3.5  03CODE05.PRG-Another Method Using Arrays to Store Values from a Single Record

SELECT EMPLOYEE

SCAN

  SCATTER TO EMPLOYEE

  SELECT EMPL9

  APPEND BLANK

  GATHER FROM EMPLOYEE

  SELECT EMPLOYEE

ENDSCAN

An alternative method enables you to copy all records from the first table into a two-dimensional array with a single command, thereby eliminating the loop. This method works only if the table does not have too many records. How many is too many records? The number of records times the number of fields per record cannot exceed 65,000. Listing 3.6 illustrates this method.

Listing 3.6  03CODE06.PRG-Yet a Third Method Using a Two-Dimensional Array

SELECT EMPLOYEE

IF RECCOUNT() * FCOUNT() < 65000

  COPY TO ARRAY aEmployee

  SELECT EMPL9

  APPEND FROM ARRAY aEmployee

ELSE

  SCAN

    SCATTER TO aEmployee

    SELECT EMPL9

    APPEND BLANK

    GATHER FROM aEmployee

    SELECT EMPLOYEE

  ENDSCAN

ENDIF

You might want to get even more sophisticated and copy blocks of 65,000 elements from one table to another, as shown in Listing 3.7.

Listing 3.7  03CODE07.PRG-A More Sophisticated Method-Copying Blocks of Data

SELECT EMPL10

GO TOP

IF RECCOUNT() * FCOUNT() < 65000



* Records can be copied in a single block

  COPY TO ARRAY aEmployee

  SELECT EMPL9

  APPEND FROM ARRAY aEmployee

ELSE



* Determine the maximum number of records to copy at a time

  nRecBlk = INT(RECCOUNT()/FCOUNT())

  nRemain = RECCOUNT()



* Loop until all records are copied

  DO WHILE nRemain > 0



  * Copy next block of records

    COPY TO ARRAY aEmployee NEXT nRecBlk

    SELECT EMPL9

    APPEND FROM ARRAY aEmployee

    SELECT EMPL10



  * Check if on last block

    nRemain = nRemain - nRecBlk

    nRecBlk = IIF(nRecBlk < nRemain, nRecBlk, nRemain)

  ENDDO

ENDIF

SELECT PRODHIST

APPEND FROM CURPROD

If you want to append records from CURPROD only where field lInProduction equals .F., use the following program lines:

SELECT PRODHIST

APPEND FROM CURPROD FOR NOT lInProduction

You can even specify, with the FIELDS clause, which fields to append.

Creating a Database

In Visual FoxPro, a database is a collection of tables. You can continue working with tables individually, as you would in the old FoxPro style. However, Visual FoxPro also provides several powerful enhancements for storing tables in databases. The balance of this chapter and the next chapter examine these enhanced properties.

First, create a database container to store the tables. The following command creates and names a new database in one step:

CREATE DATABASE SALES

You also can choose File, New, Database from the system menu, but like it does when you create tables, this method requires stepping through a series of dialog boxes.

One way to tell whether you have a database open is to look at the Database list box in the toolbar. Normally, this box is empty. If one or more databases are open, the current one is displayed in the box and the drop-down list enables you to change to another. In a program, you can return the name and path of the current database with DBC(), and you can change the current database with SET DATABASE. To determine the names and paths of all open databases, use the ADATABASES() function. The function creates a two-dimensional array with the database name as one element and its path as another.

To modify the contents of the current database interactively, use this command:

MODIFY DATABASE

This command opens the Database Designer. Figure 3.10 shows the Database Designer for the Tastrade application that comes with Visual FoxPro.

Figure 3.10: The Database Designer shows tables and relations for database TASTRADE.DBC.

The Database Designer has its own toolbar. Figure 3.11 defines the buttons that it uses.

Figure 3.11: This figure shows the database toolbar buttons that are available from within Database Designer.

As you add tables to a database, they appear in the Database Designer window. Scrollbars appear as you add more tables. Each table lists the fields, followed by their indexes. A small key before an index name identifies the primary index. Relations between tables are shown with connecting lines. To browse a table, simply double-click it.

Adding Existing Tables to the Database

To add an existing table (such as CUSTOMER.DBF) to the current database, click the Add Table button in the Database Designer toolbar or choose Database, Add Table. When the table appears in the design window, it can overlap or cover existing tables. You treat table definitions as you do any other window, and you can drag and resize them as you please.

Long Field Names  One of the first changes you might want to make is to rename the fields that use long field names. In a database, you can have up to 128 characters per field name. To change a field name, simply highlight it and enter a new name. Spaces are not allowed in field names (FoxPro would not know when a field ended). One solution uses the underscore where a blank would be used to create clearer, more descriptive field names. However, the new recommended naming convention frowns on underscores. Rather, it recommends that you can make the first letter of each significant word in the field name uppercase and the rest lowercase. Unfortunately, the Table Designer in Visual FoxPro does not support case. In fact, Visual FoxPro displays field names in different case in different parts of the system. Browse window column headers, for example, are all initial capital letters.

Figure 3.12: This figure shows the enhanced features that are available after you add tables to a database.

Field-Level Validations  You also can add field-level validation rules in the Table Designer dialog box (refer to Figure 3.12). To add a rule, enter it directly in the Field Properties Validation Rule text box, or click the Expression Builder button (the button to the immediate right of the field, with the ellipsis on it). You have seen the Expression Builder before, so you already know how to use it. For the date-of-last-purchase field, you want to ensure that the date never exceeds the current system date. If it does, you want to display the error message Last Purchase Date Cannot Be a Future Date.

You can use any logical expression when you define a validation rule. You can even call a function for tests that are too complex to express as a single statement. The only restriction is that the function must return .T. or .F.. Field validation rules trigger when you attempt to move off the field or when the value changes due to an INSERT or REPLACE statement.

Field Default Values  Although the date-of-last-purchase field in Figure 3.12 does not contain a default value, you can add a default value for any table field. To default to the current system date, for example, place DATE() in the Default Value text box. The expression added for the default value must result in the same data type as the field itself; otherwise, FoxPro generates an error. FoxPro places the default value in the table field whenever you add a new record with a command such as APPEND or interactively in a Browse or Edit window.

Caption Values  FoxPro uses caption values as column headings when you browse or edit the table. By default, FoxPro uses the field name as the caption value. However, the field name might not always be the best choice. After all, if you use table prefixes or any other naming convention in the field names, you would not want to display them as field headings in Browse or Edit windows. You can define the caption based on the contents of another field or other variable, but the most common choice is to enter a simple text string in the Caption text box. This value is also used to create a label for a control when you are using the drag-and-drop method of creating field controls in a form or when you use the Form Wizard.

Field Comment  A field comment is a note to help document the meaning or purpose of a field. FoxPro stores this element as a Memo field; therefore, it can be as long you want. Figure 3.12 uses it as a reminder to use the current date to validate the input field. For other fields, a field comment could list possible values, tables used to validate an entry, and other information. You determine the use of a field comment; you can even ignore it. But the more information you store in the table definition, the easier it is to maintain it later.

This text appears after the label Description: when you highlight the field name in the Project Manager. This value is also used for the Comment property of a control when you are using the drag-and-drop method of creating field controls in a form.

Using Primary and Candidate Keys

Click the Index page of the Table Designer and open the Type drop-down list. From the earlier discussion about indexes, you remember that all four index types appear in this list. Remember that only an index that qualifies as a candidate index can be named primary. This means that the index expression references every record in the table with a unique value. You use the primary index to form relations with other tables. An Order table, for example, includes a customer ID with every order. FoxPro can relate the customer ID in that table to the customer ID in the CUSTOMER table to retrieve any or all of the customer's information.

Primary and candidate tests occur when VFP updates the record. Therefore, you might not get an immediate error if the key value is not unique until you move off the record.

Changing Table Properties Programmatically

In the past, changing table properties while you were in a program was difficult. The task basically required the program to re-create a new copy of the table with the changes and then copy the data from the old table to the new one. With Visual FoxPro, this task has become easier due to the addition of two commands: ALTER TABLE and ALTER COLUMN.

Suppose that you want to change the default value for a company name during data entry. You could use the following expression:

ALTER TABLE customer ALTER COLUMN company SET DEFAULT 'Documation'

In fact, you can do almost anything you want, as the ALTER TABLE syntax following this paragraph shows. Be aware, however, that this capability applies only to tables that are part of a database.

 [NULL|NOT NULL]

    [CHECK Iexpression1 [ERROR cMessageText1]]

    [DEFAULT eExpression1]

    [PRIMARY KEY|UNIQUE]

    [REFERENCES TableName2 [TAG TagName1]]

    [NOCPTRANS]

-Or-

ALTER TABLE TableName1

  ALTER [COLUMN] FieldName2

    [SET DEFAULT eExpression2]

    [SET CHECK Iexpression2 [ERROR cMessageText2]]

    [DROP DEFAULT]

    [DROP CHECK]

-Or-

ALTER TABLE TableName1

  [DROP [COLUMN] FieldName3]

  [SET CHECK Iexpression3 [ERROR cMessageText3]]

  [DROP CHECK]

  [ADD PRIMARY KEY eExpression3 TAG TagName2]

  [DROP PRIMARY KEY]

  [ADD UNIQUE eExpression4 [TAG TagName3]]

  [DROP UNIQUE TAG TagName4]

  [ADD FOREIGN KEY [eExpression5] TAG TagName4

    REFERENCES TableName2 [TAG TagName5]]

  [DROP FOREIGN KEY TAG TagName6 [SAVE]]

  [RENAME COLUMN FieldName4 TO FieldName5]

  [NOVALIDATE]

Examining Compatibility Issues

After the release of any major product upgrade, there is always a transition period in which some users are using the old version while others use the new one. You might need to share data between these versions. Eventually, you will need to convert the old system to the new version. This section describes some of the issues to consider during this transition.

Sharing Tables with FoxPro 2.x

In general, free tables can be shared between FoxPro 2.x and Visual FoxPro. However, FoxPro 2.x cannot share tables that have been included in a Visual FoxPro database. Attempting to use such a table in FoxPro 2.x results in the warning message Not a table/DBF. This behavior results from Visual FoxPro's changing the first byte of the .DBF file that identifies it.

Another problem occurs when you create a table in VFP that uses a Memo field. In VFP, the Memo field pointer is stored in four bytes rather than 10, as it was in previous versions of FoxPro.

If you remove a table from your system, a reference to it still exists in the database. You can remove such references by opening the database like a table and deleting the appropriate records. Similarly, if a database is accidentally deleted from disk, references to the database remain in the tables that were formerly contained in the database. The FREE TABLE command removes the database reference from a table. Thereafter, you can add the table to a different database or use it from FoxPro 2.x.

If you need to share tables with applications written in FoxPro 2.x, you cannot take advantage of the features provided by databases. In addition to being restricted to using free tables, you cannot define fields with the types Currency, DateTime, Double, Character (binary), or Memo (binary). These field types do not exist in FoxPro 2.x. If you can live with these limitations, you can share tables and their indexes with FoxPro 2.x. If you cannot, you might need to use a remote connection to the table using ODBC.

Conversion from Visual FoxPro 3.0

When you open a project file created in VFP 3 or 5, VFP 6 has a built-in converter that converts all your files in that project. Actually, what FoxPro does at this point is rebuild your project, so if files have been moved, FoxPro prompts you to locate them.

Also, when you try to open a database container created in VFP 3 or 5, VFP prompts you that the database container was compiled in a previous version if the database container has stored procedures. If you attempt to open a database created with Visual FoxPro 6, you will not need to be concerned. The only time you will get a prompt here is if the database was created with an earlier version. To solve this problem, type the following command in the Command window:

COMPILE DATABASE DatabaseName

Removing a Table from the Database

When it adds a table to a database, Visual FoxPro changes the first byte in the .DBF header and adds a relative reference to the .DBC, which prohibits you from using the table as a free table or from adding it to another database. However, you can run FREE TABLE as shown in the following example to reset the first byte in the .DBF file and clear the back link:

FREE TABLE CUSTOMER

Current values for this first byte in the .DBF include

0 x 02	FoxBASE0x03 FoxPro, FoxBASE+, dBASE III PLUS, dBASE IV (no memo)
0 x 30	Visual FoxPro
0 x 43	dBASE IV SQL table file, no memo
0 x 63	dBASE IV SQL system file, no memo
0 x 83	FoxBASE+, dBASE III PLUS (with memo)
0 x 8B	dBASE IV (with memo)
0 x CB	dBASE IV ASQL table file, with memo
0 x F5	FoxPro 2.x (or earlier) (with memo)
0 x FB	FoxBASE

OPEN DATABASE databas1

OPEN DATABASE databas2 ADDITIVE

OPEN DATABASE databas3 ADDITIVE