Visual FoxPro Tricks

The page lists various tricks and quirks I've encountered with Visual FoxPro over the years and tricks to bypass them.

Dynamic Inferred Structural Typing

It's refreshing to have a language intelligent enough to determine what type of variable you need based on how you assign it and not need to cast types every time it changes. For example, the following code will yield "1.1," but in a strong-typed language, you would need to declare the type of i as an int, f as a float, Sum as a float, cast Integer into a float to add them, and then convert to a string to display them with MessageBox.

i = 1
f = 0.1
Sum = i + f
MessageBox(Sum)

However, this make it very east to shoot yourself in the foot with sloppy code like this which will run without error:

s = "test"
s = 1
s = .T.
s = DateTime()

Not to mention, this prevents the compiler from identifying type-based errors at design time, so you get a lot of "Operator/operand type mismatch" errors during runtime. For example, the following code compiles just fine, but crashed when you run it:

Integer = 1
String = "This is not a number!"
Sum = Integer + String

Uncommon Logical Representation

Most languages use the words "true" and "false" to represent their values, but VFP uses the first letter surrounded by periods; so, .T. is true and .F. is false. There is also .NULL. for null.

Non-Exact Comparison With Equals

By default in VFP, when you compare two strings with an equal sign, VFP only compares to the length of the second string. This yields results different from most programming languages. For example, the following code will return true:

MessageBox("Testing" = "Test")

To force VFP to behave like other languages and compare the strings to their full length, use a double equals sign. Thus, the following code will return false:

MessageBox("Testing" == "Test")

There is another option in VFP with the command, Set Exact On, but this is misleading, because it doesn't actually use exact matching. Instead, VFP pads the length of each string with spaces until they are of equal length, but this can yield undesired results as well. For example, the first comparison will be false, but the second will be true:

Set Exact On
MessageBox("Testing" = "Test")
MessageBox("Testing" = "Testing ")

VFP Processes Fields Before Variables

When reading or writing data, VFP tries to access table fields before memory variables. This can create unexpected results when you have a memory variable with the same name as field in the currently selected table. For example, in the following code, the MessageBox will read "Table Field" not "Memory Variable":

Create Cursor Test (MyVariable C(11))
Insert Into Test (MyVariable) Values ("Table Field")
MyVariable = "Memory Variable"
MessageBox(MyVariable)

To force VFP to read and write to a memory variable instead of a field, add "m." to the beginning of the variable. For example, in this code, the MessageBox will read "Memory Variable":

Create Cursor Test (MyVariable C(11))
Insert Into Test (MyVariable) Values ("Table Field")
m.MyVariable = "Memory Variable"
MessageBox(m.MyVariable)

Personally, I find it to be best practice to never give my variables names that might be used by fields. To guarantee this, I use Hungarian notation for memory variables, but not for fields.

Can't Force Field Types Using Select

Because VFP uses inferred variable typing, you cannot specify the type of a variable you want when you define a variable. While this generally doesn't cause a problem, because most variable types are properly inferred, it does create a problem when using Select to create new fields. The following code:

Select *, Space(50) As NewCharField From table name Into Cursor Test

Will create a cursor of the specified table with a new 50-character-long field called NewCharField. This works as expected because VFP properly infers character variables. However, this code:

Select *, 0 As NewIntField From table name Into Cursor Test

Will create a cursor, not with a new integer field as desired, but with a numeric field with a length of 1. Although you can increase the length of the numeric field by substituting 00, 000, 0000, etc. for the field value, VFP has no way to force the query to use a cursor type of integer.

There are a couple ways you can get around this. Obviously, you can use Alter Table to add an integer field, issue the Select statement, and then Alter Table to drop the field, however this requires exclusive access to the table and is very resource heavy.

A simpler method is to use an existing field from the table that is the type you desire, and then wiping out the values afterward. For example:

Select *, Existing Integer Field As NewIntField From table name Into Cursor Test
Update Test Set NewIntField = 0

However, if the table you're querying from doesn't have an integer type field, you can use a Create Cursor command to make a cursor with all the desired fields, including the new integer field, run the query into a second cursor, and then append the results into the created cursor:

Create Cursor Test (Existing fields, NewIntField I)
Select * From table name Into Cursor Test2
Select Test
Append From DBF("Test2")

Brackets Are Used For Both Strings and Arrays

Like Visual Basic, VFP uses parentheses to call an array element, for example:

Local Array aTest(10, 2)

However, VFP also lets you use brackets like a C-based language:

Local Array aTest[10, 2]

But, to really complicate things, VFP also lets you use brackets for strings:

sString = [This is the contents of a string]

Using brackets for array dimensions confuses the interpreter's color syntaxing, so, when you use brackets to denote an element in an array, it is colored as though you're setting a value of a string. Since VFP is more BASIC-like than C-like, I prefer to use parentheses for my arrays which yields the correct color syntax.

Weak Cursor Support

VFP uses temporary tables called cursors which, despite the ambiguous name, are extremely useful. However, several commands do not work on them. For example:

• You cannot Pack a cursor.
• You cannot Alter Table a cursor if the cursor uses long field names. VFP will give a cryptic error about trying to Pack a cursor, but this is wrong. Although, strangely, you can still use Modify Structure to make changes.
• Append From only works if you first wrap the cursor's alias with DBF().

VarType Can't Identify Non-Existent Properties On Non-Existent Objects

Because VFP uses the period operator for both table fields and object properties, it prevents VarType from being able to properly distinguish a table from non-existent properties on non-existent objects. For example, in the following code, NotAnObject will properly be described with a "U" for undefined, the oForm.Visible property will be described with an "L" for logical, the oForm.NotAProperty non-existent property will be described with a "U," but the non-existent property on the non-existent object will throw an error of "Alias 'NOTANOBJECT' is not found," because, since it's not an object, VFP will assume it's a table before running VarType.

MessageBox(VarType(NotAnObject))
oForm = CreateObject("Form")
MessageBox(VarType(oForm.Visible))
MessageBox(VarType(oForm.NotAProperty))
MessageBox(VarType(NotAnObject.NotAProperty))

You can get around this problem by using the Type command when you're not sure if the object will exist:

MessageBox(Type("NotAnObject.NotAProperty"))

Config File

VFP's configuration files are stored in .\Users\[Your User]\AppData\Roaming\Microsoft\Visual FoxPro 9

File Path Commands Aren't Quoted

In VFP you can execute file path commands like CD and Copy File as though you were at a command line. For example:

CD C:\Temp
Copy File Test.txt To C:\Windows

Because file paths do not have to be quoted or be forced into a variable, the compiler can't predetermine if the value is legit, so clearly incorrect statements will go unnoticed and compile just fine. For example, the following code which is clearly missing the last closing parenthesis will try to copy C:\Temp\Test.txt into a directory called "(cPath":

cPath = "C:\Temp"
CD (cPath)
Copy File (cPath + "\Test.txt") To (cPath

To avoid accidents like this, I always use string variables or constants when writing a path.

Can't Close Database When Its In the Open Project

The command Close Database will properly close a database during run time and in the Command Window during design time. However, if you have a project open that has a database included in the Data tab, running Close Database during design time will not close the database (even if you do not have the project's Data tab selected), and will not throw an error of its failure to close the database. To close the database when it's included in a project, you must first close the project then issue Close Database, or just use Close All, which will close both.

VFP Doesn't Escape Quotes In CSV Format

If you use Copy To or Append From to export or import a table into CSV format, and a character field contains a double quote, VFP will not escape the quote. For example, the following code will generate invalid CSV output:

Create Cursor Outbound (Description C(20))
Insert Into Outbound (Description) Values ('Lt. Blue "Cyan"')
Copy To ("C:\Temp\Description.csv") CSV

The CSV file will have the following contents:

description
"Lt. Blue "Cyan""

While this looks acceptable, it will cause most CSV importers to give unwanted results because the quotes inside the quotes are not escaped. Even VFP, the creator of the CSV file, cannot import it correctly. VFP sees the second double-quote as the end of the field prematurely ending the field and throwing off the remainder of the line. When you run the following code:

Create Cursor Inbound (Description C(20), ShouldBeBlank C(20))
Append From ("C:\Temp\Description.csv") CSV

You will get a cursor with the following values:

Description          ShouldBeBlank
Lt. Blue             Cyan""

Some importers (like LibreOffice) are smart enough to import the file properly because its program will only end the field when it sees a double-quote followed immediately by a comma. However, VFP's export can create truly unreadable output when you include a comma in the field as well:

Create Cursor Outbound (Description C(20))
Insert Into Outbound (Description) Values ('Broken",Field')
Copy To ("C:\Temp\Description.csv") CSV

The contents of the file will look like this:

description
"Broken",Field"

And, importing the file with the following code:

Create Cursor Inbound (Description C(20), ShouldBeBlank C(20))
Append From ("C:\Temp\Description.csv") CSV

The contents of the new cursor will look like this:

Description          ShouldBeBlank
Broken               Field"

VFP also doesn't properly handle the two most common methods of escaping a double-quote. If you create a CSV file with the following contents:

description
"Lt. Blue ""Cyan"""
"Lt. Blue \"Cyan\""

Most CSV importers will import the data correctly. However, if you use the following VFP code to import it:

Create Cursor Inbound (Description C(20), ShouldBeBlank C(20))
Append From ("C:\Temp\Description.csv") CSV

Your cursor will have the following data in it:

Description          ShouldBeBlank
Lt. Blue ""Cyan""    
Lt. Blue \           Cyan\""   

Since VFP can't handle either exporting or importing CSV files with character fields that have double-quotes, you must handle this process manually. The easiest way to do this that I'm aware of is to replace double-quotes with a "safe" character, that is, a character that is unlikely to ever occur in normal data and can't easily be typed in on a keyboard, for example, a pilcrow (¶). The following code will make a duplicate of a table an replace all instances of double-quotes in character fields with the pilcrow symbol:

Select * From YourTableNameHere Into Cursor CSVCursor ReadWrite
nColumns = AFields(aColumns, "CSVCursor")
For nColumn = 1 To nColumns
	If aColumns(nColumn, 2) = "C"
		cCommand = "Update CSVCursor Set " + aColumns(nColumn, 1) + " = StrTran(" + aColumns(nColumn, 1) + [, '"', "¶")]
		&cCommand
	EndIf
EndFor
Copy To "C:\Temp\YourTableNameHere.csv" CSV

When you import the file, run the following code to put the double-quotes back:

Create Cursor YourTableNameHere (Field1 C(20), Field2 C(20), Field3 C(20))
Append From "C:\Temp\YourTableNameHere.csv" CSV
nColumns = AFields(aColumns, "YourTableNameHere")
For nColumn = 1 To nColumns
	If aColumns(nColumn, 2) = "C"
		cCommand = "Update YourTableNameHere Set " + aColumns(nColumn, 1) + " = StrTran(" + aColumns(nColumn, 1) + [, "¶", '"')]
		&cCommand
	EndIf
EndFor

Of course, this only works when working from VFP to VFP, which you wouldn't normally do. If you're exporting or importing data in VFP, it usually means you're working with another program, so, you'll have to figure out how to replace or encode the double quotes in that program as well.

Importing From a Table With an Auto-Incrementing Unique Key

Needing to import the data from one table to another is quite common, and can be done with the command:

Append From table name 

However, if the table you're importing into uses an auto-incrementing unique key, the above command will fail, because you can't insert a record with a specific value in an auto-incrementing unique key. A quick way to get around this problem is to eliminate the unique key from the table being imported using Modify Structure, run the import, then add it back. However, if you don't have the ability to modify the structure, you can use a cursor to do it:

Select * From table name Into Cursor ImportCursor ReadWrite

At this point, you might think to use an Alter Table SQL statement to eliminate the unique key, but VFP can only run Alter Table on cursors when all of the field names are 10 characters long or fewer. If the cursor does feature a long field name, you can strangely still use Modify Structure on the cursor and manually remove the column. Once the column is removed you can safely append the data with the following line:

Append From DBF("ImportCursor")

Sporadic Failures On One-Record Tables

My company found an extremely rare bug that occurs when you run a Locate command on a table with only one record. Even if Found() returns .T. and RecNo() returns 1 indicating it is on the first record, VFP is actually on End of File and will return an empty value for any requested field. Our test code was something to this effect:

Create Table "Settings.dbf" (SettingNo I, SettingName C(4))
Insert Into Settings (SettingNo, SettingName) Values ("Test", 1)
Locate For Settings.SettingNo = 1
If Found() = .T.
    MessageBox(RecNo())
    MessageBox(Settings.Name)
EndIf

For the majority of the time, the MessageBoxes would read "1" then "Test", but every so often the second MessageBox would be blank. This can be resolved by adding several blank records to the table.

Cannot Pass Properties By Reference

VFP allows variables to be passed as parameters by reference by adding an at symbol (@) at the beginning of the variable. For example:

Local cName
cName = "Dean"

ChangeName(@cName)
? cName

Procedure ChangeName
    LParameters cNewName As String
    cNewName = "Smith"
EndProc

In the above program, cName will be changed to "Smith" because we called the ChangeName procedure with a variable by reference rather than by value. This is very useful when you want to return multiple variables from a single procedure. However, VFP does not allow you to pass an object's property by reference, even when they're the proper type. This program which is similar to the first attempts to pass an object's property by reference:

Local oObject
oObject = CreateObject("Custom")
oObject.AddProperty("cName", "Dean")

ChangeName(@oObject.cName)
? oObject.cName

Procedure ChangeName
    LParameters cNewName As String
    cNewName = "Smith"
EndProc

Running this will cause VFP to assume that oObject is a table (because it contains a period) and it will throw the error "Alias 'OOBJECT' is not found."

Cannot Nest Containers Inside Each Other In a Class

VFP allows you to nest their base container-type classes (container, page frame, option group, etc.) over and over again. However, if you try to do this with a user-defined container class more the one time, you will get a "class definition is cyclical" error.

To see the error, create a Visual Class named Container1, based on a Container, and store it into a new VCX file. Create another new class, with the name Container2, base it on the Container1 class, and store it into the same VCX file. Modify Container2 and drop a Container1 into it, then, edit the Container1 class, and drop another Container1 into it. Save the Container2 class then try to drop it onto a form, you get the "class definition is cyclical" error.

The strange thing is, you can build this object in the class designer just fine, making it several deep, and VFP won't complain. It's only when you try to instantiate the class as an object that VFP throws the error. You can also nest Container1 several times deep on a form, and VFP won't thrown an error.