Lesson 1 - Free Tables

Visual FoxPro (VFP) has been around for a long time. It's a wonderful product especially designed for data storage, manipulation and presentation. There are really two primary parts to VFP, the data engine that includes tables and databases and the front end interface. They do not have to be used together. The data engine can be used by .NET and other front end systems and the VFP front end is an excellent interface to SQL Server and Oracle.

The Data Engine – Free Tables

The VFP data engine is available to users at several levels. In the beginning, all data was contained in free standing tables. We can still store data in these tables, now known as ‘FREE’ tables. Data was and is stored in columns (fields in VFP) and rows (called records in VFP).

Records

If you are familiar with Excel, then you already know the concept of Rows and Columns. As in Excel, each row contains a group of data pertaining to some unit. It might, for instance, contain the name, address and phone number of a person. Within a row, the information is stored in columns or ‘fields’. The primary difference between a VFP table and an Excel spreadsheet is that VFP requires that fields (columns) be defined with specific lengths.

Fields

Each field in VFP must be defined to contain a specific type of data at a specific length. The only exception, from a practical point of view for a beginner, is the Memo field type which can hold textual content of variable lengths. For the average user, the basic field types are:

Character (Maximum of 254 Characters)
Date
DateTime
Integer
Float
Logical
Memo
Numeric

Table Designer

VFP provides us with a visual tool to create and maintain tables. We can bring up this tool from the Command Window by typing “Modify Structure”. We can also select this tool through the menu system using the following sequence.

File -> New -> Table

This will bring up a dialog that lets you name the table and determine where it will be created. When you finish naming the table, you’ll see a dialog that lets you add fields to the table. For each field we define the name, the type and the maximum size. For Numeric fields we further define the number of positions within the total size that we want to assign to decimals. The latter can be a bit tricky at first because when defining the total size of a numeric field you must take into consideration that the number of digits to the left of the decimal point will be reduced by the number of decimals + 1, to account for the decimal itself.

Numeric: Size = 5, Decimals = 1 results in a maximum of 999.9

Character, Float and Numeric field types must have specific size designations. But, you will notice that for Date, DateTime, Integer, Logical and Memo you cannot alter the sizes of the field. That’s due to the way these data types are stored in VFP’s tables. The numbers 1 and 64,923 take exactly the same number of bytes internally in VFP. Date and DateTime are really stored as numbers so even though the DateTime field looks longer, the actual space required to save each type is exactly the same.