Chapter 2: Creating Tables and Indexes

Tables are the basic units you use for working with data and creating relational databases and applications. You might need to work with information currently stored in tables, customize existing tables, or create your own custom tables to store data. To order your data and speed up processing, you can use indexes.

This chapter shows how to work with and modify existing tables, and how to create new tables with Visual FoxPro, either with assistance from the Table Wizard, or using the Table Designer directly. For information about importing table information from other applications, see Chapter 9, Importing and Exporting Data. For information about the additional properties of tables available when they are incorporated into a database, see Chapter 3, Collecting Tables into a Database. For more information about indexes, see Chapter 7, Working with Tables, in the Programmer’s Guide.

This chapter discusses:

Creating a New Table

When you need to create a new table, you can get assistance from the Table Wizard, or use the Table Designer and set up a table with the fields you need.

Remember these main points when you set up a table:

Choosing a Data Type

Each field in your table holds a particular type of data. You can set the data type of a field to any of the types in the following table.

Data Type Description Example
Character Alphanumeric text A customer’s address
Currency Monetary units Purchase price
Numeric Integer or decimal numbers Quantity of items ordered
Float Same as Numeric  
Date Month, day, and year Date an order was placed
DateTime Month, day, year, hours, minutes, and seconds Date and time an employee arrived at work
Double Double-precision number Data from experiments that require a high degree of precision
Integer Non-decimal numeric values (whole numbers) Line number in an order
Logical True or False Whether or not an order has been filled
Memo Alphanumeric text of an indeterminate length Notes about phone calls in a phone log
General OLE Microsoft Excel worksheet
Character (Binary) Same as Character but values aren’t translated when the code page changes User passwords stored in a table and used in different countries
Memo (Binary) Same as Memo but values aren’t translated when the code page changes A logon script used in different countries

Using the Table Wizard

Whenever you want to create a new table, you can use the Table Wizard to help you set it up. The wizard will ask you a series of questions and build a table for you based on your answers.

To create a table with a wizard

  1. In the Project Manager, select the Data tab and select Free Tables.

  2. Choose New.

  3. Choose the Table Wizard button.

  4. Follow the instructions on the wizard screens.

You can also access the Table Wizard from the menu by choosing New from the File menu, selecting Table, and selecting the Table Wizard option.

If you base your table on a database, you can use styles, field mappings, or a primary key, and establish or use relationships in the database tables.

Starting the Table Designer

If you want to create your own table without using a wizard, use Tables in the Project Manager Data tab or choose New from the File menu, select Table, and choose New File.

Starting a new table in the Table Designer

If you want to create a table to add to a database, open a database before creating a new table, and the new table will automatically become part of the database. You can also add it later. For more information about database tables, see Chapter 3, Collecting Tables into a Database.

To create a new table

  1. In the Project Manager, choose Tables under Databases, or choose Free Tables.

  2. Choose New, and then choose New Table.

  3. In the Create dialog box, enter a name for the table and choose Save.

  4. On the Fields tab of the Table Designer, type the name for the first field in the Name field.

  5. In the Type field, select a field type from the list.

  6. In the Width column, set the width of the column in characters.

  7. If Type is Numeric or Float, set the number of decimal places to display in the Decimal box.

  8. If you want to add an index to the field, select a sort order in the Index column.

  9. If you want the field to accept the input of null values, select NULL.

You can choose to begin entering records immediately, or open the table later when ready to enter records.

To add records to a table

  1. In the Project Manager, select the name of the table.

  2. Choose Browse.

  3. From the View menu, choose Append Mode.

  4. Enter new record changes in the Browse window.

    If you prefer to view each field on a separate line, switch to Edit mode by choosing Edit from the View menu.

Storing and Viewing Information in Tables

Tables store your data in a row and column format, similar to a spreadsheet. Each row represents a record, and each column represents the fields in the records.

Rows and columns in tables

You can create two types of tables in Visual FoxPro: database tables, which have been incorporated as part of a database, and free tables, which are independent of any database.

Viewing the Contents of a Table

The quickest way to see the contents of a table is to look at it in a Browse window. The Browse window displays the contents of the table as a series of rows and columns you can scroll through. To familiarize yourself with some typical tables, you can browse the sample tables in the Visual Studio …\Samples\Vfp98\Data directory.

To browse a table

  1. From the File menu, choose Open and select the name of the table you want to view.

  2. From the View menu, choose Browse.

You can also select the name of a table in the Project Manager, and choose the Browse button.

For example, open the Customer table in the Visual Studio …\Samples\Vfp98\Data directory.

Table in a Browse window

For easier data entry, you can place a Browse window into Edit mode. In Edit mode, the column names are displayed on the left side of the window.

To change the Browse window to Edit mode

In either mode, you can scroll through records, search for specific records, and make changes directly to the contents of the table.

Moving Around in a Table

Use the scroll bars to move through the table and display different fields and records. You can also use the arrow keys and the TAB key to move around.

To see different records

  1. From the Table menu, choose Go to Record.

  2. In the submenu, choose Top, Bottom, Next, Previous, or Record #.

  3. If you choose Record #, enter the record number you want to see in the Go to Record dialog box, and choose Go To.

Editing Fields

To change the current information in a Character, Numeric, Logical, Date, or DateTime field, either place the cursor in the field and edit the information, or select the entire field and begin typing the new information.

To edit the information in a Memo field, double-click the field in the Browse window or press CTRL+PGDN. An editing window opens with the contents of the memo field.

A General field contains an embedded or linked OLE object. You can edit this object by double-clicking the General field in the Browse window and either editing the document directly (as with a Microsoft Word document or a Microsoft Excel worksheet) or double-clicking on the object to open the parent application (as with a Microsoft Paint object).

Adding New Records to a Table

To quickly add new records to a table, you can place the Browse or Edit windows into Append mode. In Append mode, a blank set of fields appears at the bottom of the file, so you can fill them in to create a new record.

To add a new blank record

Fill in the fields in the new record, tabbing from field to field. A new record is created at the bottom of the file as you complete each record.

Append Mode for a Browse window

Deleting Records

Deleting records from a table is a two-step process in Visual FoxPro. First, you mark the records for deletion by clicking the box to the left of each record.

Records marked for deletion

Marking the records does not delete them. To actually remove the records, you choose Remove Deleted Records from the Table menu. This process deletes the records you marked, and consolidates the remaining records in the table. Removing the deleted records closes the table, so you must re-open it to continue working.

To delete records from a table

  1. Mark the records you want to delete by clicking in the left column.

  2. From the Table menu, choose Remove Deleted Records.

  3. Choose Yes in response to the prompt that asks if you want to pack the table.

You can also select a group of records for deletion by setting conditions in the Delete dialog box. Choose Delete Records from the Table menu to enter deletion criteria.

Delete dialog box

Choose the Scope button to set the range of records to be deleted.

If you can describe a set of records you want to delete, you can build an expression based on that description. Choose the FOR button to bring up the Expression Builder so you can build an expression. For example, using the expression FOR Country = 'UK' selects all the records from the UK and marks them for deletion.

Customizing a Browse Window

To customize the Browse window to fit your needs, you can rearrange or change the width of columns, turn the grid lines on or off, and split the Browse window into two panes.

Rearranging Columns

You can rearrange the columns of the Browse window into the order you want. This does not affect the actual structure of the table.

To rearrange the columns in a Browse window

Changing Column Widths

You can also change the width of a column in a Browse window. This resizing does not affect the field length or the structure of the table.

If you want to change the actual length of the fields, modify the structure of the table using the Table Designer (see Modifying Tables later in this chapter).

To change the width of a column

Turning Grid Lines On and Off

You can also turn off the grid lines in a Browse window.

To turn grid lines on or off

Splitting a Browse Window

You may find it useful to split the Browse window so that you can look at two different areas in the table, or look at the same records in Browse and Edit modes simultaneously.

Split Browse window

To split a Browse window

  1. Place the pointer on the split bar at the bottom left corner of the view.

  2. Drag the split bar to the right to divide the Browse window into two panes.

    -or-

  3. From the Table menu, choose Resize Partitions.

  4. Press the RIGHT ARROW key to move the split bar.

  5. Press ENTER.

To resize the panes of a split Browse window

  1. Place the pointer on the split bar.

  2. Drag the split bar to the right or left to change the relative size of the panes.

    -or-

  3. From the Table menu, choose Resize Partitions.

  4. Press the LEFT ARROW or RIGHT ARROW key to move the split bar.

  5. Press ENTER.

By default, the two panes of the browse window are linked; that is, as you select different records in one pane, the change is reflected in the other. If you want to detach the two panes so that they function independently, (that is, so that scrolling one pane does not affect the other) clear the Link Partitions command on the Table menu.

Modifying Tables

When you need to make changes to the structure of an existing table, use the Table Designer. With the Table Designer, you can add and delete fields, set data types and widths for fields, and view and set the indexes which sort the contents of the table.

If the table you are modifying is part of a database, additional database-specific field and table properties are available. Use of these properties is covered in Chapter 3, Collecting Tables into a Database.

To modify a table

The structure of the table is displayed in the Table Designer.

Table Designer shows current table structure

To add a field to a table

  1. In the Table Designer, choose Insert.

  2. In the Name column, type the name of the new field.

  3. In the Type column, select a data type for the field.

  4. In the Width column, set or enter a width for the field.

  5. If the field has a type of Numeric or Float, set the number of decimal places in the Decimal column.

  6. If you want the table to accept null values, select the NULL column.

  7. Choose OK.

  8. Choose Yes to make the table structure changes.

To delete a field from a table

Customizing Tables

You can customize your tables so that only certain records are displayed by setting a filter on the table. You can also restrict access to certain fields in a table by setting a field filter, which allows you to select which fields to display.

Filtering a Table

If you want to see only records of a certain type, you can set a filter to limit the records shown in the Browse window. Filtering is useful when, for example, you want to restrict the browse to show only sales above a certain amount, or employees in a range of hire dates.

To set a filter, use the Work Area Properties dialog box, available through the Properties command on the Table menu.

Work Area Properties dialog box

To set a filter on a table

  1. Browse the table you want to filter.

  2. From the Table menu, choose Properties.

  3. In the Work Area Properties dialog box, enter your filter expression directly in the Data filter box.

    -or-

When you browse the table, you see only records that are selected by the filter expression.

For example, using the Customers table from the Visual Studio …\Samples\Vfp98\Data directory, you could focus on customers residing in the United States by entering the following expression in the Data Filter box:

Customer.Country = "USA"

Results of filtering a table

Restricting Access to Fields

If you want to keep certain fields from displaying when tables are browsed or used in a form, you can set a field filter to restrict access to those fields. You select the fields you want to display, which leaves the remaining fields inaccessible.

To set a field filter

  1. From the Table menu, choose Properties.

  2. In the Work Area Properties dialog box, under Allow access to, select Only fields specified by 'field filter' and choose Field Filter.

  3. In the Field Picker dialog box, add the fields you want to the Selected fields column and choose OK.

    Field filter and its effect on a Browse window

When you browse the table, only fields you selected in the field filter are displayed.

For more information about customizing and working with tables, see Chapter 7, Working with Tables, in the Programmer’s Guide.

Ordering Data Using Indexes

Once you create a table, you can order the data to speed up data retrieval by using indexes. With indexes, you can quickly process your records for displaying, querying, or printing. You can also select records, control whether duplicate values are entered in a field, and support relationships between tables.

You can use indexes to increase the speed that records are sorted or searched for display or printing. Indexes are also important for creating relationships between tables in a database.

Indexes in Visual FoxPro are like book indexes: a list of page numbers that points the reader to specific pages in a book. Likewise, a table index is a list of record numbers that points to specific records to process and determines the processing order for the records.

A table index stores a list of pointers to records.

For example, you can look at the records in the Customer table in alphabetical order by company name, prepare them for a mailing list in order by postal code, or organize them to speed up searches. The index does not change the order that data is stored in your table; it changes the order that Visual FoxPro uses to read each record.

You can create more than one index for a table, each representing a different order for processing your records. The indexes you create are stored in a compound structural index file that is opened and updated whenever the table is used. The name for the index file is the same as its associated table and has a .cdx file extension.

Indexes are so easy to create, you might be tempted to define one for every field. However, indexes that you use only rarely can slow performance. Other types of index files are better for storing indexes you don’t use regularly. For more information about the other kinds of index files available to you, see Chapter 7, Working with Tables, in the Programmer’s Guide.

Creating an Index

Once you have a table, you can create an index on a field or on an expression. To use indexes efficiently, create indexes on fields you often use in filters on tables, in queries, in views, or for reports. If you place an index on a field that isn't useful for filtering or querying, such as a street address, your filter might slow down processing.

To create an index

  1. In the Project Manager, select the table you want to add an index to, then choose Modify.

  2. In the Table Designer, select the Indexes tab.

    The Indexes tab in the Table Designer

  3. In the Name box, type a name for the index.

  4. From the Type list, select an index type.

    For more information about index types, see the following section, Choosing an Index Type.

  5. In the Expression box, type the name of the field you want to order the records by.

    -or-

    Build an expression by choosing the dialog button at the end of the box to display the Expression Builder.

    For information about adding multiple fields to your index expression, see the following section, Ordering by Multiple Fields.

  6. If you want to select records, enter a filter expression in the Filter box or select the button at the end of the box to build an expression.

    For more information about using a filtered index, see the section, Filtering Records, later in this chapter.

  7. Choose OK.

Once you create an index for the table, you can use it to order your records.

To order records using an index

  1. In the Project Manager, select the table with the index you created.

  2. Choose Browse.

  3. From the Table menu, choose Properties.

  4. In the Index order box, select the index you want to use.

  5. Choose OK.

The table appears in the Browse window, displaying the records in the order your index specifies. You can also order the results of queries or reports by running them while the index is selected.

Choosing an Index Type

You can choose one of four types of indexes:

Primary indexes ensure only unique values are entered into a field and determine the order in which the records are processed. You can create one primary index for each table if the table is included in a database. If the table already has a primary index, add a candidate index.

Candidate indexes also enforce unique values and determine the order in which the records are processed, like a primary index. You can have multiple candidate indexes per table in both database and free tables.

Regular indexes determine the order in which records are processed but allow duplicate values to be entered into a field. You can add more than one regular index to a table.

For compatibility with previous versions, you can also create a unique index that selects and orders a subset of records based on the first occurrence of a value in the field you specify. If you want to select records this way, you might want to create a query or view instead. For more information about queries or views, see Chapter 4, Retrieving Data, or Chapter 5, Updating Data with Views.

Ordering by Multiple Fields

To increase the speed of queries or views that have filters on more than one field, you can order your records by specifying multiple fields in the index expression. The fields are evaluated in the same order they appear in the expression. If you create an index expression using numeric fields, the index orders the records by the sum of the fields, not the fields themselves.

To order records using multiple fields

  1. In the Project Manager, select the table you want to add an index to and choose Modify.

  2. In the Indexes tab, enter a name and type for the index.

  3. In the Expression box, enter an expression that lists the fields you want to order by.

    For example, you might want to order the records by country, postal code within the country, and the company name within the postal code. You can use a plus sign (+) to create the index expression from character fields.

    customer.country + customer.postalcode + customer.company
    

    Notice that the field that changes the least is the first one in the list.

  4. Choose OK.

If you want to use fields of different data types, you can convert the non-character fields to characters by enclosing the field with STR( ). For example, you can try ordering the records by the Maximum Order Amount field, then by the company name. In this expression, maxordamt is a Currency field and company is a Character field.

STR(customer.maxordamt,20,4) + customer.company

Filtering Records

You can control which records are included in the index by adding a filter expression.

To filter records

  1. In the Project Manager, select the table you want to add an index to and choose Modify.

  2. In the Indexes tab, create or select an index.

  3. In the Filter box, enter a filter expression.

    For example, try creating a filter that selects only the records for customers in Canada:

    customer.country = "Canada"
    
  4. Choose OK.

Using Indexes

You can improve performance by creating and using indexes for recurring tasks such as ordering your records or supporting table relationships. You can accomplish several tasks depending on the type of index you create.

If you want to Use
Order the records to increase the speed they are displayed, queried, or printed A regular, candidate, or primary index.
Control entry of duplicate values in a field and order the records A primary or candidate index for a database table or a candidate index for a free table.

Ordering Your Records

You can order your records by a field name or another index expression. The index evaluates the expression to determine the order that the records should appear, then stores a list of pointers to the records for processing in that order.

To create an index for ordering records

  1. In the Project Manager, select the table you want to add an index to and choose Modify.

  2. In the Indexes tab, enter a name and type for the index.

  3. In the Expression column, type the field name you want to use to order the records.

    -or-

    Type an expression you want to use to order the records or build an expression by choosing the button at the end of the box to display the Expression Builder.

  4. If you want the records to appear in descending order, select the arrow button to the left of the index name.

    The arrow on the button points down for descending order and up for ascending.

  5. Choose OK.

Controlling Entry of Duplicate Values in a Field

You might want to control entry of duplicate values in a field. For example, each customer should have a unique value in the Cust_ID field of the Customer table.

An index can enforce entering unique values into a field.

You can use the field as a key field that uniquely identifies a record. If your table is part of a database, you can use either a primary or candidate index. If your table is a free table or already has a primary index, you must use a candidate index.

To create an index that enforces unique values in a field

  1. In the Project Manager, select the table you want to add an index to and choose Modify.

  2. In the Indexes tab, create an index.

  3. In the Type column, select Primary or Candidate.

  4. Choose OK.