When Visual Foxpro 3.0 came onto the scene in July of 1995, it was a radically different product from its predecessor, Foxpro 2.6. In fact, most people thought of Visual Foxpro 3.0 to be a 1.0 release, instead of a 3.0 release. This being the case, it has put in place a tremendous learning curve on the part of the development community. As you will see however, this is a good thing! Visual Foxpro 3.0 is a extremely powerful development environment and is worth the extra time it will take to learn about its new features and capabilities. We are now in release 5.0a of Visual Foxpro. In the latest release, Microsoft has made VFP even faster (much faster form loads) along with adding some great new features. Some of which are a color coded editer, class mapping, and outer join support. As of this writing, MS is already working on the next release of Visual FoxPro, code named "Tahoe", for the next release of Visual Studio. All of the features that are shown on the screen shots may not be fully described in this manual.

What this course will try to accomplish, is to provide a hands- on approach to learning VFP. It will walk you through the process of creating a small VFP application, from laying out the tables and setting relationships, to creating the forms and menus to tie everything together. The disk that accompanies this manual contains a finished version of the application that we will create as well as a directory that contains only the DBF files needed to get things going. It would be best if the contents of the disk are copied to your hard drive. Keep the diskette handy in case you need to restore the original setup.

After the class is over, you can take this course back home or to the office to further practice and build your own application using the techniques discussed here.

With a little practice, this course will help you become much more proficient at developing VFP applications.

We will be using the project manager in VFP to manage all of our project components.

First create a VFPCLASS directory on your C drive. Then create a START subdirectory and copy the contents of the OPTIMIZED_DATA\EXAMPLES\START directory on the CD. There are subdirectories for data, classes, forms, and progs.

Make VFPCLASS\START the default directory by typing the following in the command window:

The CD DOS command is now available in VFP. This is handier than typing the SET DEFAULT TO command!

The Project Manager (PM) in VFP is the starting point in designing a VFP application. The PM stores all the references to all the various components that make up an application. The PM file on disk is just a regular Fox table with a PJX extension. It contains fields that hold the location on disk of each of the components, as well as file types and timestamp information. The PM is VFP has been greatly improved over the PM in Foxpro 2.x. In Foxpro 2.x, the PM was just one long list of project components sorted alphabetically. It would have been much better if the various components could have been grouped according to type. In VFP, that is what was done. The PM has been laid out as a tabbed form and looks like this:

The first tab, "All" shows all of the project components in one tab. Even here though, they are categorized in the list by type. Any project category that has files associated with it, will have a "+" sign just before the category listing. By clicking on this plus sign, you can drill down into that particular category. There are five other tabs to hold each of the project categories. There is one for Data, Documents, Classes, Code, and Other. Within each one of these, there may be a further breakdown of categories. For instance, within the Data tab, there is a section for Tables, Local Views, Remote Views, Connections, Stored Procedures, Free Tables, and Queries. There are similar sections for the other tabs as well. These tabbed sections make organizing a VFP application much easier than before. The column of buttons on the right hand-side of the PM, allow:

• New components to be added
• Existing components to be added,
• The currently selected file to be Modified
• The currently selected file can be removed.
• And, the last button allows for the project to be built into an application file (APP) or an EXE file.

A convenient way to work with the PM is to drag it to the top of the screen and dock it. When the PM is docked, all that is visible, are the tabs for each of the project components. To open up any of the tabs, simply click on it, and the tab will open up to show you all of the items inside. When the PM is docked, the buttons to add a new component or modify one are gone. But if you select the file you want to open, and then right-click, you will get a menu that has the choices relevant to that item. This is a very efficient way to make use of screen real estate. Also, if you want to have any one of the tabs open all the time, what you can do is "tear off" a tab. You can do this simply by clicking on the tab, and then dragging it away from the docked position. It will change to an outline of the tab, which you can then drop anywhere on the screen. Finally, to get at the build options, simply right-click anywhere on the docked PM and a menu will pop-up with the Build option visible.

Let’s get started using the data on disk to create our first VFP project file.

To create the PM file, let’s perform the following steps:

1. In the command window, type MODI PROJECT FRSTPROJ and press <ENTER>. This will bring up an empty project manager like the following:

Figure 1 shows the design surface for the VFP data dictionary. The data dictionary is one of the most important additions to VFP. This is where all the information about your data tables, views, indexes and business rules are kept. This is what is called an active data dictionary. The information in it is available at all times. No matter where you are located in your application, the rules that are placed in this dictionary are always active.. Whether you dragged and dropped a field onto a form, or your are using just a plain BROWSE window, the validation rules will always fire. This has the tremendous advantage of protecting your data from ad-hoc changes no matter where you might be.. In the past, a BROWSE window was a good way to changed your data without going through the coded validation rules that would be present in an application.

The data dictionary, also referred to as the Database Container File, is under the surface, just a normal DBF file with a different extension. It has the extension of DBC, its index file has an extension of DBX, and its memo field file has an extension of DCT. The database designer as shown in Fig 1, is a visual representation of the records that are in the DBC file. When a DBC file is opened in the database designer, VFP reads the DBC to determine:

• What tables are in the DBC.
• What fields are associated with each table.
• What the relationships between these tables are
• What views have been defined

It then draws these elements on the screen that allows for a very intuitive way of viewing this important data. An important point to keep in mind is that the database container file does not contain the actual tables that are listed in the DBC file. It merely contains data ABOUT the tables, not the data contained within side of the tables. The tables that are referenced reside on disk as separate DBF files. To create the Database Container File, lets perform the following steps…

1. Click on the second tab labeled DATA. This will bring that tab forward. You will notice that there are 3 categories listed here, databases, free tables, and queries. We want to create a NEW database, so we need to click on the NEW button that is along the right hand side of the project manager.
2. You will be presented with a dialog box that is used to name the new DBC file and to save it in the proper directory. We want to store it in the DATA subdirectory. So double-click on the DATA directory and give it a name of MAINDATA.
3. Click on the SAVE button to create the DBC and to go into the Database Designer.
4. You are now in a blank database designer . At this point, there is no information in the DBC file. In order to add a table to the DBC, we need to right-click in order to bring up the menu for the database designer.

Figure 3 shows the Database Designer with the right-click menu displayed. We first want to start adding existing tables to the database container file.

5. Off of this menu, choose Add Table
6. From the Open Dialog, choose the DATA directory. From here select the PROPMAST file. Click on OK
7. Since the first time that this is done the table is a free table, you will see a prompt warning you that VFP will change the header of the table to the new format required by VFP’s DBC file. When this step is done, there will be a BAK file on disk of the original file. This new table header section will now contain a pointer to the database container file that it is associated with. After this step, the table can no longer be read by Foxpro 2.X. It can however be exported back out to Foxpro 2.X format.
8. After this conversion process is done, the table structure will appear in the Database Designer. It will contain all of the fields and any indexes in a scrolling list.
9. Repeat steps 4 through 7 and add the tables PROPTRAN, STYLE, USAGE, and MATOWNS.

Next, we want to add indexes to the files in order that we may set relationships and also so we can write Rushmore Optimized queries in the future. In order to create indexes on PROPMAST, let’s do the following:

1. Select the PROPMAST table by clicking on it with the mouse.
2. To bring up the context-sensitive menu for tables, right-click the mouse button. This will bring up a menu that will allow you to BROWSE, DELETE, MODIFY, COLLAPSE, and get HELP.

   

   figure 4

3. Choose MODIFY off this menu. This will bring you into the TABLE DESIGNER. This is where the structure is defined, indexes are created, and table rules are entered. The TABLE DESIGNER is shown in Figure 4.

1. PRIMARY: Enforces uniqueness of index value. A table can have only one PRIMARY index. If the user tries to insert a record that has a duplicate value, VFP will generate an error.
2. CANDIDATE: Has same properties as a PRIMARY index. Can be used to enforce uniqueness on another field.
3. UNIQUE: This filters out repeating key values but DOES ALLOW duplicate values. There is no error checking for repeating key values!
4. REGULAR: This is a just a plain index with no filtering, no error checking. It is the one used most often.

1. In the third column, type in the index expression. All we need to supply here is the name of the field, PROPID.
2. This completes the process of adding indexes. We will add indexes for the fields CCODE, TOWN. Repeat steps5 through 7 to create them.
3. We also want to add indexes to the other tables in our project. Use the following table to create indexes on the remaining tables:

Note how the PRIMARY index was named PRIMARY. I have found this to be an easy and consistent way to name the primary index. When opening up a table, you just need specify for example:

Whatever the table, whatever the primary index is, it can always be referred to in the same manner. Also all primary indexes are denoted in the Database Designer with a key symbol to the left of the index name. This is the "key" index for this table.

Now that there are indexes in place, we can visually define the relationships between the tables. This step requires that primary indexes are created first on the parent table. Let’s use the two main tables in this application to illustrate the point, PROPMAST and PROPTRAN. The index that uniquely identifies each record in the PROPMAST table is PRIMARY. This index has as a key the field PROPID. There is a unique value of PROPID for each record in the table. In the table PROPTRAN, there are two indexes. One is PRIMARY which is a combination of PROPID and TRANDATE. These two fields uniquely identify a record in PROPTRAN. The other index is on just PROPID. This tag is the one we want to use in our relationship between PROPMAST and PROPTRAN. Why? Because we know that the business rule here is that for each individual piece of property, there can be many transactions. A house can be sold multiple times, there can be refinance transactions on the property. For the purpose of this first relation, we want to be able to show for each property record, all of the transactions that have occurred on the property. In order for us to do this, we need to link the two tables on PROPID. This relation will show only the matching records in PROPTRAN for the current record in PROPMAST. In order to define this relationship, let us do the following:

1. With the Database Designer open, click on the PROPMAST table to select it.
2. Using the scroll bars, scroll to the bottom of the PROPMAST list until the indexes are visible.
3. Next, scroll to the bottom of the PROPTRAN list until its indexes are visible.
4. Click on the PRIMARY index label in the PROPMAST listbox and drag it down over the PROPID index that is displayed in the PROPTRAN listbox. Note that while you were dragging the index from PROPMAST to PROPTRAN, the icon was a circle with a line through it. This is an indicator as to where the valid drop zones are. When the icon is over the PROPID index however, the icon changes to indicate that it is a valid drop zone.
5. Release the mouse button when the index is on top of the PROPID index in PROPTRAN. .As soon as you do this, a dialog box (Fig 5) appears showing you what the relationship is.

Since the index PROPID in PROPTRAN is not a primary key, the relationship type is One to Many as shown in Fig 5. If you relate a table on two primary index keys, the relationship would be one to one since each index would uniquely identify records in each table. The dialog box in Fig 5 also shows you what tables are being related and on what fields. Since this is the correct relationship, click the OK button. You will then see this relationship represented visually in the Database Designer by a line connecting the two tables together using the PRIMARY index in PROPMAST and the PROPID index in PROPTRAN. Note also how the line appears on the PROPTRAN side of the relationship. It is shown as having a three pointed tip to represent that this is the many side of the relationship.

6. Using this same technique, we need to create relationships between the other code tables as well. With the following table as a guide, create the rest of the relationships:

When you are completed, the Database Designer should like the following figure:

You may arrange the tables in the Database Designer in what ever way allows you to view the relationships the best. These relationships that we just defined are what are called Persistent Relationships. This means that when these tables are used in the data environment of a form, or in the query or view designer, the relationships will be read out of the database container file and used as the default join conditions between the tables. We will see an example of this later on.

Think of the Database Designer as the data entry screen to the .DBC file. It is the primary design surface for Visual Foxpro database engine. From here you can add tables, create tables, define relationships, and as we’ll see next, define field validations, default values, field captions, record level validations, and triggers.

Field level validations are rules that can be associated with a field at the data engine level. These rules will always be in effect no matter where the field is edited. Field level rules are entered on the MODI STRUCTURE screen that we saw earlier. As you scroll down through the list of fields that are in the table, the expression box labeled Field Validation, is where the expression goes to validate the data for the current field. Since this is an expression, it can be as simple as "fdsaledate < date()" or it can represent a function call to a stored procedure that performs a more elaborate validation check. For instance, a validation check might involve doing a SQL SELECT against a customers purchases to see if they have exceeded their credit limit. Or with the case of real-estate app, the price of a sales transaction must be greater than 0. In this case we could specify the following rule for the field LSALPRICE: LSALPRICE > 0. If the validation rule is violated, the text for the error message comes from the next expression, "Validation Text". This also is an expression and can supply any message you want.

The default value expression on the MODIFY STRUCTURE screen, allows a field to be primed with a value when a new record is added to that table. For example, the PROPID field in the PROPMAST table is a unique number for each property record. Every time a new PROPMAST record is added, this field needs to be filled in with the next new PROPID value. If the Default Value expression contained a call to a stored procedure function called "NEXTPROPID" it would be executed and would return the next sequential ID value.

The caption expression is where you may fill in a more complete description of the field. This caption information is used in defining the column headers in a BROWSE window. They are also used as the labels for textbox objects when either the Form Wizard or the From Builder are invoked.

Record level validations are accessed by clicking on the Table Properties button on the MODIFY STRUCTURE dialog. The Validation Rule expression box is the rule that is fired whenever a record update occurs. Record level rules can be used to ensure that different fields obey certain business rules. For example, in our PROPTRAN file, a record level rule that checks to make sure the mortgage is less than or equal to the sale price could be represented as: mortgage <= tranprice. If the business rule fails, the error message defined by the Validation Text will be displayed.

In order for these events to be available from anywhere in the application, we need a place to store the procedures that will be run when any of these triggers are fired. These procedures need to be always available. The place to store these procedures is in the Stored Procedure file inside of the Database Container file. The stored procedure file is just a memo field inside of the DBC file which is attached to a record whose OBJECTNAME field contains "StoredProceduresSource". If you were to open up the DBC file and browse it, you would see that in the memo field CODE is the source code. There is another record where the field OBJECTNAME field contains "StoredProceduresObject". This record holds the compiled version of the source code from the prior record. Since the code is stored in the DBC file, the stored procedure code is always accessible. In fact, when a database container file is opened up, any stored procedures are loaded into memory so they are instantly available. REMEMBER, if you open up a DBC file and BROWSE it, DO NOT CHANGE ANY INFORMATION! This could corrupt the database container file and you risk loosing information! This should only be manipulated through the Database Designer!

Triggers are a very powerful addition to the VFP SQL engine. A trigger is to a table as a mouse click is to a form. Triggers are a special kind of event. Just like a mouse click in a form is an event, a trigger in the database is also an event. As we’ll see later, there are dozens of form events, but there are only three table level events. These are the Update, Delete, and Insert triggers. These events are what we can use to enforce database integrity, alert users to possible bad data, and also to create audit trail logs. The latter is a very important function in many applications. It can be important to have a complete history of all changes that have happened to a record. The availability of triggers in VFP make this possible. We can trap at the database engine level, every time a table update occurs. We can then write out to an audit trail table the previous value of the field that was changed along with the new value and any other important information. Other information may include who changed it, when, and why. Since we are defining these triggers at the database engine level, they will be enforced no matter where the table update occurs. We do not have to code for this event in all our forms and programs! Since this behavior is true for all the database triggers, we can now code our business rules in one location.

This allows developers to define what should happen when a record gets deleted in a one to many relationship with another table. For example, using our PROPMAST and PROPTRAN tables, we could define that when a PROPMAST record gets deleted, all of the related PROPTRAN records get deleted also. Or when a PROPTRAN record get inserted, it has to have a matching PROPMAST record first. This would prevent the formation of orphaned PROPTRAN records.

Lets create a simple audit trail trigger to demonstrate this concept. We’ll attach this trigger to the PROPMAST Update trigger. In order to do this, let us follow these steps:

1. Open up the Database Designer.
2. Click on the table PROPMAST.
3. Right-Click to bring up the context sensitive menu.
4. Choose the Modify option. This will bring up the Table Designer we saw earlier when we were creating the index files.

   

   fig 7

5. To view table properties, click on the third tab, TABLE. Fig 6 is the screen you will see.

The first Validation Rule is a record level validation. The Validation text is what would be displayed if the validation rule failed. The Insert, Update, and Delete Triggers are what we are interested in at this time. These fields are all expressions. They can consist of simple logical expressions that evaluate to TRUE or FALSE, or they can be calls to User Defined Functions (UDF’s) that can carry out any complex task that needs to be done. If the return value of these expressions, no matter what form they take, is TRUE, than that action will take place. If the expression returns a FALSE value, than the action will NOT take place.

function createtrail

local lcfield, lorigvalue, lnnumfields, lnfieldnum, lnewval, lcdatatype

for lnfieldnum = 1 to fcount()

if getfldstate(lnfieldnum)= 2

*--- field has changed...

lcfield = field(lnfieldnum)

lcdatatype = type(lcfield)

do case

case lcdatatype = "D"

m.oldval = dtoc(oldval(lcfield))

m.newval = dtoc(eval(lcfield))

case inlist(lcdatatype,"N","I")

m.oldval = str(oldval(lcfield))

m.newval = str(eval(lcfield))

case lcdatatype = "C"

m.oldval = oldval(lcfield)

m.newval = eval(lcfield)

endcase

m.propid = propmast.propid

insert into audit (propid, field, datatype, user, date, oldval, newval ) ;

values (m.propid, lcfield, lcdatatype, "HAROLD", date(), m.oldval, m.newval)

endif

endfor

return

Save the stored procedure code by pressing CTRL+W or using the CLOSE icon in the window title bar.

Any other UDF’s that are needed by triggers should be kept in the stored procedure file as well.

The INSERT trigger can be used to control the integrity of the tables used in your application. For example, a rule can be specified using the RI Builder (LATER) that will enforce the rule that if a record is added on the many side of a one to many relation, that the record can only be added if the parent record exists. This will ensure that there are no orphan records produced. Also, the INSERT trigger can be used to trigger updates of other tables. If a record is inserted into the INVOICE table for instance, it can trigger a program that will update the balance of the customer.

The DELETE trigger can also be used to enforce table integrity. One very common use of the DELETE trigger would be to delete all child records when the parent record is deleted. This can be an automatic procedure that is trapped by the database engine so that these records are deleted automatically. Again, like the INSERT trigger, this can help ensure that there are no orphaned records. Also, this can be used to also trigger a program that would automatically adjust a customers balance when an invoice was deleted for example.

The following table describes when the above data rules are checked:

NOTE: All enforcement’s occur on buffered data except for triggers. Triggers only happen when Buffered data is written to disk or if buffering is turned off for a table.

When a table update occurs, it usually involves doing more than just updating that one table. For example, in an order entry application, there could be as many as four updates that could occur to other related databases:

• Add the new order to the ORDERS table
• Update the sales total for the sales person who took the order
• Update the sales total for the sales persons office
• Update the quantity on-hand total for the ordered product.

So that all the tables are updated correctly when this order is taken, ALL the updates outlined above must complete. If the first three steps were completed, but the last one, the updating of the on-hand quantity, was not completed, the inventory levels would not correctly reflect the fact that an item was sold. This could result in an other order being placed for the same product when in fact, that item is not really in stock. The system would say that it is, but when packer went to get the item off the shelf, it was not there! So in order to prevent this from happening, all of the steps above must occur as a unit. If one does not go through to completion, than the entire transaction must be backed out. Doing this in prior versions of Foxpro or other DBMS’s that do not support transaction processing, is a nightmare! Without transaction support, the programmer has to manually keep track of what process caused the transaction to fail. Then all table updates that occurred before that point, have to be backed out manually. This is not a trivial task!

Let’s take a look at how Visual Foxpro implements transaction processing and how this greatly simplifies the above scenario.

The BEGIN TRANSACTION command in VFP when executed, starts the buffering process that allows all subsequent table updates to be rolled back if any one of them fail. When a transaction is started, all table operations are actually performed on a "buffered" version of the table. A buffered version means that the table operations are being performed on a copy of the original database. The actual underlying data, IS NOT actually being touched! This is a very important concept. The following illustration shows how the buffered table is a copy of the original table that is physically on disk. The buffered version can reside in memory or on disk, depending on its size.

This idea of buffered data is an important concept in VFP. We will come back to it later. Since the information being updated is actually just a scratch copy of the original, if any one of the update routines fails, we can simply discard the buffered version of the tables and we are back to where we started! Things that might make a transaction fail would be if the table or record we need to update is already locked by another user on a network.

If this occurs we can issue the ROLLBACK command. This will do just that, rollback all the data to the way it was at the time of the BEGIN TRANSACTION command. This command alone can save many man-weeks in development. Coding for rollback routine in the old 2.6 way of doing things is a very difficult and high-risk thing to do. Another nice benefit of this is that if during the course of a transaction, someone shuts off the machine, the tables are not left in a state of being half complete. The data is rolled backed automatically.

If everything goes correctly during a transaction, then we can issue an END TRANSACTION command to actually write out the buffered version of the table to disk. This will commit the changes.

We will see examples of this later when we work on a form. Transactions are usually used during the saving of data on a form.

Buffering as described in the previous section, allows VFP to make changes to and manipulate tables without actually committing changes until the TABLEUPDATE() command is issued. There are two types of buffering that we will look into next, row buffering and table buffering.

When VFP enables row buffering, it is only making a copy of the current record that is being edited. This is slightly analogous to SCATTERing memory variables in Fox 2.X and editing them instead of editing the record directly. But with memory variable, there was really no link from the memory variables back to the underlying data. With a buffered record, you retain the characteristics however of working with a table. With row buffering turned on, whenever you move off the record, the buffered record is saved back to the base table. Also, the record can be committed by issuing the TABLEUPDATE function. If the record needs to be discarded, you can issue the TABELREVERT function to discard the current record.

With table level buffering, VFP makes a buffered version of the entire table, as depicted in Fig 7. In this scenario, none of the changes are committed until the TABLEUPDATE command is issued. With table buffering, the user could make many changes to the table, add many records, and then commit them in batch or discard them. This feature allows for a changes to a table to be easily reverted back to their original state before any changes were made. In order to see how this can work, let’s look at a simple example: this can be done from the command window…

1. Open up the property master table. USE PROPMAST
2. BROWSE
3. From the main menu, select Table|Properties…
4. The following dialog appears that displays the properties of the current table.

Another interesting feature of table buffering is how appended records are handled. In order to show this feature, we need to:

1. Bring up the DEBUG window. In the first line of the DEBUG window, type in RECNO(). This will allow us to see the value of the current record number.
2. Click on the BROWSE window to activate PROPMAST.
3. Press CTRL+Y to append a new record.
4. Look at the DEBUG window, the value of the current record is -1! All newly appended records in a buffered table are numbered from -1 on. The tenth appended record would have a RECNO() of -10. In a buffered table, this is how these uncommitted records are distinguished from the committed records.
5. From the command window, type in TABLEREVERT(.T.) again and press ENTER. You will see the newly appended record disappears.

How these are used in an application will follow when we get to the Forms Designer section. These buffering concepts will become clearer when they are tied into an actual form

The View Designer in VFP is a design tool that allows developers to create "smart" queries. The key difference between a view and a regular SQL SELECT statement, is that views can update the tables from which they are based. This eliminates post back routines that were necessary in Fox 2.X. Also, views allow for the easy design of client server (C/S) applications. Through the use of views, remote server data appears to VFP just like local data. This also means that VFP can be used to prototype C/S applications locally using VFP’s native database engine. Then, you can "upsize" these views to the server, ie. SQL Server, and run the application without making any code changes. Let’s take a look at how views are created using the VFP View Designer available through the Database Designer.

1. With the project manager open, click on the Data tab.
2. If not already open, expand the Database group and then expand the "CourseData" database. This will display the tables that are in the CourseData database along with the view section.
3. Click on the "Local Views" header.
4. Click on the "New" button along the right-hand side of the Project Manager.
5. The Next prompt will show a listing of the tables that are in the CourseData database file. Select the file PROPMAST. Once selected, the structure of the PROPMAST table will appear in the top panel of the View Designer. The following screen will then appear:

The upper panel of the View Designer displays the tables that will be taking part in the view. The lower panel displays the selection criteria, fields, ordering, grouping, and updating rules for the view. The list box on the right hand side of the View Designer which is labeled "Selected Output", defines the fields that will be outputted by the view.

6. To define the fields that will be part of the view, all that is needed to do is to drag&drop the fields from the PROPMAST field listing into the Selected Output list box. You can do this by clicking and holding on the field "CABBR", dragging it over the list box "Selected Output" and then releasing the mouse button, thereby dropping it into the output listing. Note that while you are dragging it across the screen, the cursor changes to a circle with a line through it to denote that you may not let go of it at that location. When the cursor changes back to a rectangle, you may release the mouse and drop the field. Do these for the following fields: propid, town, mpaddress, mpcity, mpstate, and mpzip. This same procedure can be done with the "Fields" tab selected as well.
7. Next, we want to create a selection criteria so we do not return all of the records from the PROPMAST table. In the first column in the Selection Criteria tab, is a box for Field Name. Click on the drop-down arrow to get a list of the fields that are in the table PROPMAST. Select the field CABBR for County Abbreviation.
8. Next we need to supply the comparison operator. We want to have CABBR equal to a value so select "equal to".
9. In the example columns, we supply a value that CABBR should be compared against. We will create here what is called a "parameterized view". In this kind of view, the parameter is passed to the view using a "?". Using this technique, the parameter can be changed programmatically or through the use of drop-downs in a form, and then the view can be requeried to reflect the new value of the parameter. In our example we will create a parameter like the following: ?thisform.pccabbr. This gets typed into the "example" column. The "thisform" notation will be explained when we use this view in a form.
10. The View Designer now needs to know what type of parameter this is. Select Query|View Parameters… off of the main menu. In the first row, enter "thisform.pccabbr" and specify it to be of type Character. Click on ok.
11. In order to see what we have so far, let’s run the view. Off of the main toolbar, click on the run button (!).
12. Since this is a parameterized query, it will prompt for the value "thisform.pccabbr" At the prompt, type in "NORF" ,with the quotes, since this is a character parameter. NORF is for Norfolk county. Click on OK.
13. The query is run, and only records that are from Norfolk county are presented in a Browse window.

Next, we want to make this view updatable, and make use of the real power of views! In order to do that, let’s:

14. Click on the Update tab. The update tab will appear like this:

By default, updates are NOT sent back to the base tables. In order to do that, you must check the "Send SQL Updates" checkbox. Do that now.

In the center of the Update tab, is a list box of the fields that are taking part in the view. The two columns to the left of the field list, are important. The first column, the one with the key symbol as the header, denotes what field should be used as a linking field back to the base tables. In our case, the field PROPID is already checked as being the key field because this field has a primary index key on it. The second column, with the pencil as the header, denotes what fields are updateable. By default, all of the non-key fields are checked off as being updateable. On the right hand side of the screen, are the options as to how the base tables will get updated. By default, the option "key and Modified Fields" is chosen. The update logic works like this: An image of the view as it was originally queried, is maintained in memory. When VFP goes to update the base tables, it is comparing the values of the key field and the fields that have been modified that are currently on the disk, with the values of the original query image. If the field values are the same, the update goes through. If the values are different, it does not. How could the value on disk be different from what is in the view image? It could be different if someone else on the network made a change to the base table before you did!

15. With SQL Updates in place, let’s rerun the view. Click on the Run button again in the toolbar.
16. When the result set appears in the browse window, make a change to the MPADDRESS field. Cursor down off the record to the next row. If SET TALK is ON, you will see the VFP message in the message area of the screen, that a record has been updated.
17. To see how the value of MPADDRESS has changed, let’s open up the PROPMAST table and browse it. In the data tab of the project, select the table PROPMAST and click on the BROWSE button. You should see how the value of MPADDRESS has been written back to the table.
18. Now to save this view, click on the close icon. You will be prompted for a view name. Let’s name this PROPMAST VIEW. Views can be opened up just like any other table through the use of the USE command. For example, to open up this view, you would type USE "PROPMAST VIEW". The quotes are necessary since the name of the view contains imbedded spaces.

A very important characteristic of views, is that when the view is requeryed, the result set does not get closed and reopened. Similar functionality in Foxpro 2.x required that the result set be closed, then the SQL Select would be run with the new condition. This would often involve flicker problems in BROWSE windows when the data was redisplayed. When using a grid to display a view, when the view is requeryed and the grid refreshed, the grid does not flicker.

OK, now we are getting to the fun stuff! Since we have covered many of the back-end type issues with VFP, now it is time to display the information that we have laid out in the prior steps. The Forms Designer in VFP is a radical change from the Screen Painter that is in Foxpro 2.X. One major difference is that the code generation phase is gone. VFP runs the SCX files directly by reading the compiled code memo field in the SCX file. This makes for a quicker development environment. Forms can be designed and quickly executed without waiting for code to be generated. Also, the forms now are inheritantly much more intelligent. The forms in VFP, in conjunction with the new READ EVENTS command, make modeless apps trivial. No more crazy event loops. The major advancement however, is in the fact that all of the form objects are true objects now. All objects can be inherited from base objects. They can be used as base objects for other objects. They are now exposed to the full Windows event model. And the forms themselves are objects. The forms can be designed to encompass a set of core functionality, stored as a class, and then used as base forms for other forms. This is a very powerful feature. With this, any changes made to the base form, are automatically inherited by all other forms that inherit their properties from this base form. This give the ability to easily make slightly different version of core functionality without resorting to the old cut and paste method! Finally, forms while in design mode are live! This means that programs can modify forms in design mode to help out in the design process. These programs are called builders, and if time permits, we will explore their features and implementations.

. To see how to begin creating a form, let’s go through the following steps:

1. With the project file open, click on the "Documents" tab.
2. With the Documents tab forward, notice the three different groups of documents. There are Forms, Reports, and Label. We want to create a new form, so click on the Form line.
3. To create a new form, click on the "New" button on the right-hand side of the project manager.
4. A dialog box will appear asking to use the Form Wizard or to just create a New Form manually. For this piece, we will create a form manually, so click on "New Form".
5. Next, you will see the blank Forms Designer. This will be the design surface for all of your data entry forms. The first thing that should be done with the form is to change the background color to a light gray. This is the Windows standard for forms. To change the color, simply right-click anywhere on the forms designer. Choose "Properties" off the pop-up menu. The Property sheet for the form will then appear.

Click on the "Layout" tab. The third property from the top of the list is "BackColor". To bring up a color palette, click on the ellipses(…) following the textbox that holds the RGB color codes. (see above Figure). When the color palette opens up, choose the light gray box on the bottom row. Then click OK. You will see the form change color right away.

The Data Environment (DE) for forms allow forms to be aware of the data that they are to work with. In Foxpro 2.X, saving the data environment with a screen was generally a bad idea. But in VFP, it can be a good thing! There is so much more under the developers control, that the DE becomes a very positive feature. Remember too, since VFP has an active data dictionary (DBC file), any information about the tables that are stored in the DBC file are used in the forms DE. For example, if we add two tables to the DE and they had a relationship defined in the Database Designer, that information will flow to the DE as well. There is no need to keep defining the relationships between tables in all the forms they are used. They were defined once in the Database Designer, so that information is carried forward from that point on.

6. To create the DE, right-click anywhere on the blank form. You will see a context-sensitive menu pop-up. On this menu will be an option for the DE, form properties, builder, code and Help.
7. To bring up the DE, click on Data Environment from the menu.
8. This will then bring up another blank design surface. This is where the tables get added that represent the DE. To add a new table to the DE, right-click again to bring up the DE menu. Select the first menu item: "Add". A list box will appear that has in it, all of the tables that are in the DBC file. It will appear like this:

The Property Sheet is a very vital part of the form design process. The property sheet can be activated by selecting any object on the screen, and then right-clicking. This will bring up the property sheet for that particular object. The property Sheet is laid out similar to the project manager. It is a tabbed form with a tab for the Data components for the current object, the methods, layout information, and other information like what class does this object belong to. By using the drop-down list of objects, you can change the object you are looking at through the property sheet instead of switching back to the form to select another object. The exact layout of the tabs varies from object to object. Not all methods for example, apply to all objects. Also, if you create any new methods for a form, they will appear at the bottom of the Method tab.

The fields are represented on screen by the TextBox object. The TextBox is analogous to the GET field in Foxpro 2.x. The textbox object can have as its data source a fields from a table, a memory variable, or a form property. As with the tables that were added to the DE, when we add fields to the form, any information that was associated with the field in the data dictionary, will be carried over to the form. The fields that are added to forms are not memory variables like in Foxpro 2.x. In Foxpro 2.x, it was not a good idea to edit fields directly. The use of memory variables was better, but was still pretty cumbersome to use. Memory variables had to be created, edited, validated, and then saved back to the table. Now through the use of buffered tables as discussed earlier, we can edit the fields directly in the buffered version of the table. This preserves the benefits of having defined the field level rules in the data dictionary. Also, since the tables are buffered, multi-user concerns are eased in that the table or record is not locked until we actually try to write the changes back to disk. Now with this background, we can begin to drop fields onto the form…

14. To drop fields onto the form, arrange the design surface so you can see both the blank form and the DE at the same time. If you are using a VGA screen and can get only 640X480 resolution, this may be tough to do. A super VGA screen is really recommended for doing development work. It just allows you to have more screen real estate.
15. To drop a field onto the form, simply click and hold on a field, and drag into position over the form. When you have it about where you want it, just let go of the mouse. This is all it takes to add fields to a form! Let’s add the following fields to the form: CABBR, TOWN, MPADDRESS, MPCITY, MPSTATE, MPZIP, LSALDATE, LSALPRICE, ASSDVALTOT. Arrange them so they are in the upper half of the form. We’ll take a look at the property sheet for these fields a little later. All of these fields that you have dropped onto the form, are "data aware". This means that VFP knows what field the textbox is tied to.

The grid in VFP is what the BROWSE window in Foxpro 2.x should have been. The grid object is now a true screen object that can be easily integrated into any form to display local tables, local views, or remote views. The grid responds to a the full Window event model. It components can be manipulated separately ie column, header, textbox. The grid is a container object, meaning it can serve as a container for other screen objects. Instead of just a plain textbox to display data, the grid can also contain drop-downs, list boxes, check boxes, command buttons or any other screen object. It can be inherited from, or it can serve as a baseclass for other grids. What is particularly impressive about the grid, is how easy it is to place on a form. We will represent the many side of this form with a grid object. And the data source for this grid will be the PROPTRAN field. In order to place a grid on the form, let’s continue…

16. Bring up the DE for the form by right-clicking on the form and selecting Data Environment from the menu.
17. Just like you dragged and dropped individual fields onto the form earlier, you can also drag & drop an entire table as well! In the DE, click and hold on the title bar of the PROPTRAN table.
18. Drag it over the form. Notice how the cursor changes into a grid icon. When the icon is over the form, release the mouse. A grid object now appears on the form! Since we dragged the PROPTRAN table over, VFP knows the data source for the grid is the PROPTRAN table.

OK, now we have two tables represented on screen. The upper half of the screen represents the one side of the relationship in the form of individual fields from the PROPMAST table. The lower half of the screen represents the many side of the relationship in the form of a grid based off of the PROPTRAN table. Let’s see what we have so far by running this form.

19. Click on the run (!) button on the main toolbar.
20. VFP will prompt for a name for the form. Let’s call this onemany. Click on Save.
21. When the form is run, the textbox’s are populated with data from the first PROPMAST record and the grid is populated with ONLY THE MATCHING RECORDS from PROPTRAN. Remember, the data environment for this form knows about the relationships between PROPMAST and PROPTRAN. There is only one problem with the form though, how do we navigate the tables?

What we need to do now, is to provide a set of navigation buttons that allow us to move through the table by skipping forward and back, top and bottom, etc. There is a Visual Class library that is already defined for us to use for just this purpose. Under the Class tab on the project manager, there is a visual class library called WIZSTYLE. This shipped with VFP and is the class library that VFP uses when creating forms through the forms wizard. There is a pre-defined class in here called PICTBTNS. This is a set of navigation buttons that are subclassed off of another visual class called TXTBTNS. All of the code for PICTBTNS is actually contained in TXTBTNS. The only thing that was gained by subclassing TXTBTNS, was that instead of text labels on the buttons, icons are used. This is simply another version of TXTBTNS that inherits all of its behaviors from TXTBTNS without making another copy of it. Further, any changes that are made to TXTBTNS to enhance its behavior or correct bugs, will be inherited by PICBTNS. Just like all of the other objects we put on the form, this visual class can be dragged & dropped onto the form also. In order to do this, let’s…

22. Click on the Class tab in the project manager.
23. If the Visual Class library WIZSTLE is not already added to the tab, we may have to add it manually. Assuming it is there, click on the "+" sign to expand the library.
24. Locate the class PICBTNS and click on it to select it.
25. Drag & drop it onto the bottom edge of the form. When you release the mouse button, a set of navigation buttons have been placed on the form! When done, your form should look something like this:

Now when the form is run, click on the next and previous icons and watch how the form is refreshed as you move through the table. The code that is in the navigation class TXTBTNS is handling the skipping of the records and the refreshing of the screen. These 26 steps is all that is necessary to create a fully functional one to many screen. Even though it may look like a lot of steps, it is really quite easy once it is done several times.

One way to make this process even easier to use either a form builder or a form wizard. Without going into a step by step process for the form builder, the form builder helps automate the steps that are required to place fields on the form. With a blank form, you can just right-click and select the Builder option from the menu. You can then select what fields appear on the form using a mover dialog. The builder than places the fields on the form along with identifying labels taken from the caption property of each field in the data dictionary. This can be a real time saver.

The other alternative is to use the Form Wizard. This can be a very effective way of creating many flat file maintenance screens or simple one to many screens. There is nothing wrong with using the form wizard to create a simple screen. Developers should make use of these tools to do the trivial aspects of development and concentrate on the more involved aspects of the project. Remember, if a machine can do a job as well or better then you, let it! Let’s create a simple maintenance screen for the STYLE table.

1. From the Documents tab of the project manager, select the Form header section.
2. Click on New to create a new form.
3. The first time we created a form manually, but this time we will use the Form Wizard. So select Form Wizard from dialog screen.
4. You will then see a dialog box presenting you with a choice to either select a Wizard for a flat file screen or a Wizard for a one to many form. Select the first choice for a single table.
5. You will then be led through the wizard by a series of questions asking you what table to use, what fields, sort order, button style, and form title. The last panel gives you several choices as to how to end. Select " Save and run Form". Save it as "STYLE"

The Wizard will then create a fully functional form for you in a matter of seconds. It will allow you to do routine maintenance of the file. This screen and the one to many screen created earlier will be tied together through the use of a main menu and the READ EVENTS command.

For another example, let’s combine the form with a parameterized view to show how views and form properties can interact. Remember when we created the view, the parameter in the WHERE clause was ?thisform.pccabbr. The thisform part refers to a property of the current form. PCCABBR is the name of the property itself. Using thisform, is like using the table alias to completely reference a field. Thisform points to a property of the current form instead of a table alias which points to a particular table. Let’s follow these steps to create the new form…

1. Select the documents tab of the PM.
2. Select the Forms section of the documents tab.
3. Click on new to create a new form.
4. Off of the main menu, select Form|New Property.
5. A dialog box will appear prompting for the name of the new property. Type in PCCABBR.
6. In the Description box, type in "County Abbreviation"
7. Click on OK.
8. Right-click on the blank form and select Properties to bring up the property sheet.
9. Click on the "Other" tab and scroll to the bottom to see how the new property is listed.
10. Select this property and in the settings textbox, type in ="" to initialize it to character type.
11. Right-click again on the blank form, and select Data Environment from the menu.
12. In the DE, right click and select ADD off of the menu.
13. On this dialog, Select Views to appear in the list.
14. Select "prop master" and click on OK. The view PROP MASTER will appear in the DE.
15. Drag & Drop the view onto the form to create a grid object.
16. Click on the textbox icon on the form toolbar and create a textbox object on the upper half of the screen.
17. Select and right-click to bring up the property sheet for this textbox.
18. On the data tab, set the ControlSource to thisform.pccabbr. This will tie the form property PCCABBR to this textbox.
19. Click on the method tab and double-click on the Valid method towards the bottom of the list. Enter the following code in the method:

=requery("prop_master")

thisform.grid1.refresh

The REQUERY command will rerun the view each time you enter a new value in the PCCABBR textbox. The second line will re-display the grid with the new view.

20. Save this method code.
21. Run the form by clicking on the run (!) icon.
22. It will prompt for the parameter PCCABBR. Type in "NORF" for Norfolk county. The form will be displayed with records in the grid only from Norfolk county.
23. Now type in SUFF in the textbox and press return. The view will be requeried and only records for Suffolk county will appear in the grid.

This is the basics of integrating a view with a parameter into a form. It is a very powerful way of presenting data.

In order to have the two forms work together, we need to create a simple menu that will allow the forms to be called from simply choosing a menu item. The menu designer in VFP is not changed at all from Foxpro 2.x. This was not deemed a high priority, as compared to the forms designer and the data engine. To access the menu designer, we must…

1. Click on the "Other" tab of the project manager.
2. The first category on this tab is menus. With "menus" selected, click on the "New" button to call up the menu designer.
3. We will just create a very simple menu. The first menu pad will have the prompt of "Quit"
4. The second column will specify that this menu will be a command.
5. In the third column, type in the command CLEAR EVENTS. When selected, this command terminates the READ EVENTS command and continues on with the next statement.
6. The second menu pad will be labeled "FORMS" and be of the type "submenu". To create the submenu, click on "create".
7. In the first row of the submenu, type in "Property Screen"
8. Make it a command type in the second column.
9. In the third column, type in the command : "DO FORM ONEMANY"
10. The second row of this submenu should have the prompt of "Style Screen" make this a command type also.
11. The command should read in the second column as "DO FORM STYLE"
12. Save the menu under the name of mainmenu.mnx

Now the only other component we need to tie these pieces together is a program to call the menu and to issue the READ EVENTS command to start the event processor going. In order to create this program, let’s do the following steps…

1. Click on the "Code" tab of the Project Manager.
2. The first category is "Programs". Click on this to select it.
3. Click on "New" to create a new empty program file.
4. Type in the following two lines:

do mainmenu.mpr

read events

set sysmenu to default

After executing FRSTAPP, the main menu will change and will have the two main pads you defined in the menu builder. The first pad "Quit" will issue the CLEAR EVENTS command, drop you out of the READ EVENTS command and exit the application. The second pad will have a drop-down menu that has the two forms that you completed earlier, Property Form, and Style. When the Property Form is run, the statement "DO FORM ONEMANY" is executed which runs the ONEMANY form. While this form is up and running, you can also run the other form "STYLE", and have them both up at the same time. This is the new VFP event model at work! With Fox 2.x, doing this involved writing code that could take a month or more to write and debug. In VFP, it’s all part of the READ EVENTS command!

This is what it takes to put a simple VFP application together. There are MANY more issues that are part of writing a full blown application. However, those issues are way beyond the scope of a introductory course. I hope this has succeeded in showing you some of the more powerful Visual Foxpro features. VFP is a great leap ahead for Foxpro. However, that leap will require many hours of training to fully utilize the feature set that is now present in VFP.

If you are not already, I highly recommend attending the Boston Computer Society’s Foxpro Group. It is a tremendous resource of Foxpro knowledge and contacts.

Also, the Fox forums on Compuserve, are probably your best source of up to date information on all the Foxpro products. There are three forums to service the Fox community: FoxForum, FoxUser, and VFOX.

If you have any more questions, please contact me. My address and phone number are on the last page.

Thanks for your interest!

Harold Chattaway

Optimized Data Solutions (ODS) has become a leader in the Boston Foxpro community. Some of the ways in which ODS has set itself apart are:

Contributing author to …

• Foxtalk Magazine
• The book "Exploring Foxpro"
• The book "OOP in Visual Foxpro" by Wrox Press
• Pinter Foxpro Newsletter

• The Boston Computer Society (BCS) Internet Foxpro Newsletter. One of these articles, "Integrated Browse", was the most popular article since the newsletter was published, with over 1000 downloads from CompuServe. Second article was on Software Metrics.

In addition, ODS has also:

• Presented four times at the BCS Foxpro Users Group
• Presented VFP for the BCS at the BCS Mega Meeting in April of 95.

• Presented VFP for Microsoft at Client/Server World at the Hynes Convention Center in Boston in June of 95.

• Presented VFP at the May Tech Breakfast for MS Solution Providers.

• Lead VFP presenter at Microsoft’s DevDays in Boston, September 12th.

Optimized Data Solutions specializes in utilizing Foxpro on large scale database applications. Some typical projects include order processing and inventory tracking systems for the abrasives industry to multi-million record tables over 1 gig in size for tracking real estate data in MA, CT, and RI.

ODS provides expertise in database consulting, custom development, code reviews, and training in Foxpro 2.X and Visual Foxpro.

We have the years of experience necessary to provide the database solutions your business needs...

Optimized Data Solutions

304 StoneyBrook Rd

Fitchburg, MA 01420

508-345-2421

haroldc@optimized-solutions.com