The magazine of the Melbourne PC User Group
Getting Started With Visual FoxPro
Cindy Winegarden

Ever needed to write custom database software or wanted to try database programming? Microsoft Visual FoxPro 7 should head up your list of choices of database tools. This data-centric, object-oriented database and programming language has a lot to offer for the desktop market for those needing a client-server solution, and to those needing a Web-enabled application. In addition, Visual FoxPro's royalty-free distribution offers an economical alternative to SQL licenses and the DBAs who are required to keep them tuned and running. Should a client-server database be required for the back end data store, Visual FoxPro's data-centric language offers the ideal front end to any ODBC or OLE DB compliant database. 

Visual FoxPro's full object-orientation, including full inheritance, and unique interactive development environment, including an interactive Command window and Intellisense, reduce development time considerably over general-purpose languages. 

Articles for beginners usually describe how to use the Wizards to create a basic application, but as a beginner I was frustrated by the "extras" that the Wizards added, and was left wondering what was the minimum necessary code to create an application. In this article, I'll walk through the development of a basic application with the goal of illustrating how the Visual FoxPro development tools are used and how applications are built. I'll be leaving a few loose ends, though, since this is only an introduction.

These are exciting times for Visual FoxPro. As I write this article, Visual FoxPro 7 has just been released to manufacturing, and should be on the store shelves by the time this article is published. While there are many other new features, my favorite is Intellisense, which is perhaps the most useful new feature for those new to VFP and seasoned developers alike. No more hunting for the manual when you can't remember the syntax of a little-used function or the list of appropriate parameter values for a function such as MessageBox(). No more hunting for the last program you opened, since a list of your recent ones pops up as you type MODIFY COMMAND. Even changing directories is a simple matter when a list of your most recent pops up! For most of us in the Visual FoxPro 7 Beta group, the IntelliSense alone was well worth coping with all of the other quirks of Beta software.

The Visual FoxPro 7 Environment

Out of the box, Visual FoxPro 7 opens to a blank screen, bordered on the top by the system menu and standard toolbars. Located somewhere on the screen, as shown in Figure 1, is the Command window. Nearly every task that can be done through the menus and toolbars can also be done by typing commands in the Command window, and with IntelliSense, the Command window is often the fastest way to get things done. Accessible from the Window menu or with the SET command, the Data Session window is the easiest way you can see all tables, cursors, or views that are open, change their order, or modify their structure. The Properties window is the place to see or change properties of whatever component or control is currently selected. Method code is accessible there also. From the Tools menu, the components of the Debugger are accessible, including Trace, Locals, and Watch. In VFP7 these windows are dockable, providing the developer with more flexibility and screen real estate.

Creating a Project

The project file (with a PJX extension, accompanied by its memo file, with a PJT extension) is building block of Visual FoxPro applications. The Project Manager, the visible interface to the project file, allows you to easily access all the files and data that belong to the project, as well as compile programs and "build" the application's executable file. Visual FoxPro allows you to have more than one project open at a time, and files can be shared among projects.


Figure 1. Outside of the Project Manager, the Command, Data Session, and Properties windows are the major application development tools.


Figure 2. To begin the project change the directory to C:\Sandbox, MODIFY PEOJECT Samdbox to create or 
bring up an existing project, and use the Project 
Manager's New... button to create a new Database.

Creating the Project File 

You'll want to store your project files in a location such as the "Visual FoxPro Projects" folder of "My Documents," so you'll need to change to that directory by typing CD ? in the Command window and navigating to that directory. Alternatively, if you have a directory such as C:\Sandbox to play in, all you need is to type CD C:\Sandbox in the Command window, as shown in Figure 2. Next, create your project file by typing MODIFY PROJECT Sandbox, which creates and shows an empty project file.

Creating the Database

Data is the heart of any database application, and you'll need to think about the data you will be entering into your application and the data you intend to retrieve from it. If you don't have any background in relational database design, you'll want to read further on this subject in the Visual FoxPro documentation. I'll be using examples for a Sandbox business and I'll be including tables for Customers, Materials, Orders, and OrderDetails in the database. 

Start by opening the Data tab of the Project Manager and clicking the New button (Figure 2.) Choose New Database and name your database Sandbox. This creates the Visual FoxPro Database Container file (DBC), which houses metadata about the tables, including primary keys, referential integrity rules, triggers, and stored procedures. Right click the Database Designer window and choose New Table., then New Table again, and name your table Customers. You'll see the table designer, shown in Figure 3, where you can enter fields for your table, define indexes, rules, and so on. For the Customers table, add a CustomerID field of type Integer, a Customer field of type Character, width 20, and an Address field of type Character, width 20. Add an ascending index on the Customer field.


Figure 3. Visual FoxPro's Table Designer makes creating 
tables and adding indexes, default values, and validation 
and referential integrity rules quick and easy. Here I'm 
setting up the OrderDetails table and adding an index 
on the OrderDetailID field.


Figure 4. BROWSE the Customer's table and add or change data, as easily as you would in a spreadsheet program.

Alternatively, you can create tables in the Command window or a program file using standard SQL commands like:

CREATE TABLE Customers ; 
  (CustomerID I, ; 
  Customer C(20), ; 
  Address C(20)) 

CREATE TABLE Materials ; 
  (MaterialID I, ; 
  MaterialName C(20), ; 
  SaleUnit C(10), ; 
  UnitPrice Y) 

CREATE TABLE Orders ; 
  (OrderID I, ; 
  CustomerID I, ; 
   OrderDate D, ; 
  PayDate D) 

CREATE TABLE OrderDetails ; 
  (OrderID I, ; 
   OrderDetailID I, ; 
    MaterialID I, ; 
   nitsSold N(10, 2)) 

Indexes and relations on the tables can be set either visually or in code. Typically you would add unique indexes on Customers.CustomerID, Materials.MaterialID, Orders.OrderID, and OrderDetails.OrderDetailID and set relations between them in the tables, but this example won't depend on those indexes or relations. Many developers use a stored procedure to generate unique key values for fields like Customers.CustomerID, Materials.MaterialID, Orders.OrderID, and OrderDetails.OrderDetailID. These stored procedures also become part of the database. 

Referential integrity rules, such as rules to prevent the deletion of a customer that has unpaid invoices, can be added to the database. 

Be sure to add some data to your Customers table so the report you'll create later will have something to show. In Visual FoxPro, tables show in two states, Browse and Edit, and the table designer may leave the table in Edit mode. To see the table in the more customary Browse mode, type BROWSE NORMAL in the Command window. To add a new record in this mode, type APPEND BLANK and then type your data directly into the table as shown in Figure 4.

Creating the Main Program

In every Visual FoxPro application, some program, menu, or form must be designated (through the menu) as the Main file, and that is where the application begins. It is simplest to use a program (.PRG file) file for this purpose, and the program itself need not be complicated, as in this example: 

*!* Call the main form (we'll build this below) 
DO FORM frmMainForm 

*!* Tell VFP to wait for the user to do something 
READ EVENTS 

*!* The form, and the application, will close with 
*!* CLEAR EVENTS in the Quit button 

To create the program, use the New button on the Project manager, save the program as Main.PRG, and start typing. You'll enjoy Visual FoxPro's syntax colouring, shown in Figure 5, which is customizable to your liking.


Figure 5. Visual FoxPro's program editor is convienent to use. The syntax coloring is customizable and a great help 
in creating understandable, error-free code.


Figure 6. It's easy to create a form, add controls such 
as buttons with the Form Controls toolbar, adjust 
properties such as a button's Caption, and add code 
to run when the button is clicked. As shown here, 
clicking the Data button will bring up a form 
named frmData.

Creating Forms and Form Controls

Since you'll want a way to navigate your application we'll create a main form that will offer the user choices of things he can do. Begin your form by changing to the Docs tab of the Project Manager, verifying that Forms is highlighted, and clicking the New button. Save your form as frmMainForm. You should see a blank form and the form controls toolbar. 

Visual FoxPro comes with the standard set of form controls (shown in the toolbar), a set of "one-off" control classes, and a group of ready-to-use Foundation Classes including mover lists and thermometers. The "one-off" control classes serve an important purpose for the inheritance feature of object-oriented programming. To understand the power of inheritance, imagine that your grandmother goes to the beauty parlour to get her hair styled, and your mother, you, and your daughter all immediately have newly-styled hair. In object-oriented programming, Inheritance in programming means that changes to a parent class affect all child classes below it in the hierarchy. Since the Visual FoxPro base classes can't be modified, it's important to use a set of child classes, which you can modify, as the basis for all you do. Visual FoxPro comes with a ready-made set of these child classes in the _Base.VCX class library. For the purpose of this exercise, however, we'll use the form control classes as they come out of the box.

Add a series of buttons to your form by clicking the Button icon on the Form Controls toolbar and then drawing rectangles on the form. You can select easily tidy up the buttons by selecting them all and using the buttons on the Layout toolbar. Alternatively, you can use a button group instead of individual buttons. 

Choose one of the buttons and use the Properties window to change its caption to "Data." (If the Properties window isn't visible, bring it up using the Window menu.) Double click your button to bring up the code editing window and type DO FORM frmData in the CLICK() of the button, as shown in Figure 6. Label another button "Quit" and, as shown in Figure 7, add the following code to its CLICK() method:

CLEAR EVENTS 
THISFORM.RELEASE()

Create frmData using the Project Manager's New button. Right click the frmData and choose Data Environment., select your Customers table from the list, and add it to the data environment. With the table in the form's data environment, you can drag and drop to add controls for the data to the form. Drag the header of the cursor icon to the form to create a grid, or drag the Fields bar of the cursor icon to create labels and text boxes for the individual fields, shown in Figure 8. At runtime, since the Customers table is in the form's data environment it will open automatically when the form opens. Set the Order property of Customers to Customer in the Properties window to prepare for the rest of the example.


Figure 7. Adding a Quit button to the form. Notice how Visual FoxPro's Intellisense gives a list of choices of form methods
 to call, and an explanation of what the Release method does.
A Space, Tab, Comma, Period, or other similar character
completes the choice, and brings up Intellisense for
parameters of the function or method, should there be any.


Figure 8. Drag the Fields Icon of the Customers table to 
the form to create a set of labels and controls on the 
form for that table. Alternatively, drag the titlebar 
of the Customers table to create a grid that is 
bound to the table.

You'll want to add a means to choose a particular record to work with, and one way to do this is to add a combo box, cboCustomers, to the form. Using the Properties window, set the RowSourceType to "3 - SQL Statement" and the RowSource to:

SELECT Customer FROM Customers

Add the following code to the LostFocus() of cboCustomers to search for the appropriate record and refresh the text boxes:

SELECT Customers 
SEEK 
THISFORM.cboCustomers.VALUE 
THISFORM.REFRESH()

In a more robust application you would add something like a button group with Add | Edit | Delete | Save | Cancel | Exit buttons to your form, and use a buffering scheme to work with your data. Returning to your frmMainForm, caption one of the buttons "Report" and put the following in the button's CLICK() method:

DO FORM frmReport 

Creating Reports

Standard wisdom suggests creating a report and adding tables to its data environment and setting relations among them, then dragging the controls onto the report, much as you created your frmData form. While this approach is easy to begin with, it can create a debugging nightmare for multi-table reports. In truth, the simplest way to create a report begins with an SQL SELECT statement, and once you've learned a little of this "second" programming language, it's easier to type the SQL code than to use a Wizard or GUI interface. 

To make best use of the FoxPro report writer, use SQL to create a denormalized view or cursor of the exact data you want in your report. Refer to the fields in the cursor as MyField only and not MyCursor.MyField. The Report Writer will run the report from whatever cursor, table, or view is in the currently selected work area. The extra effort of adding the controls to the report individually is well worth the time in exchange for the ease of getting the rest of the report working properly.


Figure 9. The most foolproof way to create reports in Visual FoxPro is to select with SQL commands, exactly the way
 you want it to appear in your report. This code can be 
located in the Click() method of the button that calls the 
report, and is usually followed by the REPORT command.


Figure 10. Adding fields to a report is as simple as drawing a rectangle, then typing in the name of the field which you want shown in that position. The Visual FoxPro Report Writer easily does group and summary totals, averages, and so on, and 
with a few Print When tricks can produce some very sophisticated reports.

Create a frmReport with a "Run Report" button. Double click on the "Run Report" button on your frmReport and type the following code in the window, as shown in Figure 9: 

SELECT * FROM Customers ; 
    INTO CURSOR TempCustomers ; 
   ORDER BY Customer 

REPORT FORM Customers.FRX TO PRINTER PROMPT PREVIEW

Create a new report by highlighting Reports on the Docs tab of the Project Manager and clicking New. Save your report as "Customers." Using the Report Controls toolbar, click the Field control and draw a rectangle in the detail section of the report as shown in Figure 10. Type "Customer" in the Expression textbox. You can add other fields, and finish off the title band if you want. The Visual FoxPro Report Writer has many other capabilities including group and summary computations that I'll leave for you to discover. Getting the most out of the Report Writer is mostly a matter of assembling the data in the order and way you want it to appear, and learning a few tricks with Print When and formatting. 

Building and Distributing a Project

The simplest way to run your application is to highlight the Main program in the Project Manager and clicking the Run button. This is especially appropriate in the testing phase when you'll want to have the Debugger open to watch what happens and check on the values of variables. You can also use the VFP Project Manager to "build" or compile the project into an APP or application file. The problem with either of these methods is that Visual FoxPro must be installed on the machine where the application is run, and you won't want that requirement if you're distributing your application to others. 

To create an executable file that you can distribute, choose EXE when you build your project. For a simple project, for example one not having ActiveX controls to install, you can simply copy the data, the executable and the two Visual FoxPro runtime files to a network directory or a directory on an individual machine. Though this does not always provide the fastest startup of your application, it's quick and easy when you're new to Visual FoxPro. 

When you're ready to distribute a complete application Visual FoxPro comes with InstallShield Express, which uses the Windows Installer, to package and deploy your application. Many developers who's applications are deployed on a network write custom "loader" applications, installed on the local machine, which check the server for a new version of the main application on each opening. This means that the local setup need only be run once on each machine and after that, updates to the application itself are invisible to the user.

For Further Information

The Microsoft home page for Visual FoxPro is http://msdn.microsoft.com/vfoxpro/. Microsoft makes all Visual FoxPro documentation available online at http://msdn.microsoft.com/library/default.asp in the Visual Tools section.

For a list of peer-to-peer support resources, see my web site at http://cindywinegarden.adsl.duke.edu/. I'll be adding to it from time to time as I find new resources. I've found the Visual FoxPro online community to be a generous, helpful, tightly-knit group, and always willing to help.

About the Author
Cindy Winegarden is the co-author, with Evan Delay, of the Visual FoxPro Certification Exams Study Guide (Hentzenwerke, 2001), a Microsoft Certified Solution Developer, a conference speaker, and a Microsoft Visual FoxPro Most Valuable Professional. Cindy can be reached at cindy.winegarden@mvps.org.
Reprinted from the August 2001 issue of PC Update, the magazine of Melbourne PC User Group, Australia