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.

An Introduction

My name is Tom Meeks. I've been a Foxbase. FoxPro and Visual FoxPro developer for more than 20 years. I was fortunate to have worked with some of the finest developers in the country on the Mobil Mariner Project, one of the largest FoxPro projects ever undertaken.

When I first started using Foxbase it was just before Foxbase+ was released. In those days it was a relatively easy language to learn. There were very few commands and functions. As the product grew so did the community that used it. So, help was always available through books, magazines and the venerable Compuserve forums... the forunner of web chat rooms.

When Microsoft bought FoxPro, it exploded in popularity. User groups sprang up having hundreds of members. New users were entering the VFP community and could immediately find help at every level. But, then a paradoxical thing happened. Just as Visual FoxPro was becoming its most powerful and useful, Microsoft seemed to be content to let the public think it was a doomed product as they turned their attention to the web and browser based computing.

The books stopped coming out, the user groups became smaller or disappeared and it was harder to find basic beginning VFP information. Most of the traffic on VFP forums involves higher level issues. Training companies quit giving live training classes. The resources for beginners has very nearly dried up.

The primary problem facing anyone that seeks to learn VFP now is that there are few new resources available for starting from the ground up. Most of the VFP books were written prior to or at the time of the first release of VFP 3.0 and they primarily focused on the new object oriented features. In other words, these books assume that you already know the basics of the language and the data engine. And, they do not include some of the more recent basic enhancements to the language... new ways to do old operations.

In spite of general opinion, Microsoft keeps making VFP better and better. Visual FoxPro 9.0 is the best version they've ever released, with a long needed update of the reporting system. While I doubt that many new development projects are being written in VFP these days it doesn't mean that VFP is a dead or useless product. Quite the contrary! Its real power is data manipulation. Nothing else comes close. As an analytical tool it has few, if any, equals. Paradoxically, the power of VFP is still it's greatest at the point where today's new users need it most... comprehending data.

This blog was begun because one of my clients has an ongoing need for analysts to learn the basics of VFP and have had a tough time coming up with good, solid ways to train people. These users do not need to become full-fledged programmers. They simply need to manipulate and compare data on a daily basis and VFP is THE best tool for that job. So, this will not be a place where you can learn the nuances of COM or callbacks. It will be a place where we cover basic data operations. We will, however, cover a bit about forms and objects because this is the best way to perform repeated tasks.

So, this blog is dedicated to helping those that need to parse, analyze, understand and manipulate data. It was born out of a real need in a real site for people that probably will not ever be full-time programmers; but, who can benefit from knowing the VFP language well enough to quickly and effectively understand what the data is telling them in the real world.