This blog has moved

This blog is now located at http://rickborup.blogspot.com/.
You will be automatically redirected in 30 seconds, or you may click here.

For feed subscribers, please update your feed subscriptions to


This blog is moving

This blog is moving, stayed tuned for more details.



Conditional TRANSFORMs in VFP

Visual FoxPro's TRANSFORM function is a powerful tool for converting an expression into a string. The VFP Help file defines the syntax of the TRANSFORM function this way:

TRANSFORM(eExpression, [cFormatCodes])

eExpression is the value, or an expression that returns the value, to be TRANSFORM'ed. The optional format codes enable additional control over the resulting string. The available format codes are enumerated and explained in the TRANSFORM() Function topic in the VFP Help file.

The @R format code is a little different that the others. It tells VFP to read a format mask and use it to format the resulting string. This is useful for formatting character or numeric data that may require a specific format, such as currency values, social security and FEIN numbers, and telephone numbers. The following example shows how to use TRANSFORM with @R and a format mask to format a U.S. phone number with area code in the conventional manner.

TRANSFORM( "2175551212", "@R (999) 999-9999")  && Result is "(217) 555-1212"

(Note that the space after @R is required to achieve the expected result.)

Phone numbers, however, are a good example of where you might want to use a different format mask in different situations. Assume a table with phone numbers stored in a character field. Depending on the data and how it was entered, the table might contain U.S. phone numbers with area codes, U.S. phone numbers without area codes, and international phone numbers. Assume the phone number column is wide enough to accommodate any of the anticipated possibilities.

CREATE TABLE myTable ( cPhoneNbr C(20))
INSERT INTO myTable ( cPhoneNbr) VALUES ( "2175551212") && U.S. phone nbr w/ area code
INSERT INTO myTable ( cPhoneNbr) VALUES ( "5551212") && U.S. phone nbr w/out area code
INSERT INTO myTable ( cPhoneNbr) VALUES ( "+46 704123456") && International phone nbr
A format mask of "@R (999) 999-9999" works for the first value but returns garbled results for the other two. For the U.S. phone number without an area code, a format mask showing a blank area code would be more appropriate, while for the international number it may be best to use no format mask at all and just display the number the way it was entered.

Assume the length of the unformatted phone number can be used to determine what type of phone number it is. Then in a method or procedure code a conditional statement like IF or CASE can be used to determine which format mask to apply.

lcPhoneNbr = ALLTRIM( myTable.cPhoneNbr)
CASE LEN( lcPhoneNbr) = 10
lcResult = TRANSFORM( lcPhoneNbr, "@R (999) 999-9999")
CASE LEN( lcPhoneNbr) = 7
lcResult = TRANSFORM( lcPhoneNbr, "@R ( ) 999-9999")
lcResult = lcPhoneNbr

The problem with this is that it won't work in a report field, where a single expression is needed.

Fortunately, VFP enables conditional formatting within a single TRANSFORM by using a function for [cFormatCodes]. Leaving out the "myTable." prefix for brevity, the following can be used as a report field expression to get the desired result:

TRANSFORM( cPhoneNbr, ;
ICASE( LEN( ALLTRIM( cPhoneNbr)) = 10, "@R (999) 999-9999", ;
LEN( ALLTRIM( cPhoneNbr)) = 7, "@R ( ) 999-9999", ;

If that seems a little long to stuff into an expression field, and it could get worse if there are more alternatives, the conditional code can be factored out to a UDF or to a method on some object and called from within the report field expression:

TRANSFORM( myTable.cPhoneNbr, GetPhoneNbrMask( myTable.cPhoneNbr))

The ability to use a function in place of a static format code enables us to extend the power of VFP by performing conditional formatting within a single TRANSFORM function.

Tags: ,



Password Agent 2.6

While discussing backup tools in my session on Disaster Recovery and Business Continuity Planning at Southwest Fox 2009, I mentioned that I use a password management program call Password Agent from Moon Software. While probably less well known than some of its competitors, I've used this particular app for several years and have always found it to be reliable and easy to use. The only caveat was that it had not been updated in quite some time, but by coincidence a new version was just released yesterday. Among other changes this release is listed as compatible with Windows 7. If you're interested in a good password management tool, check out Password Agent 2.6 at www.moonsoftware.com/pwagent.asp.

Tags: , ,



Southwest Fox 2009 - Don't Miss It!

Hard to believe, but it's only a little over four months until Southwest Fox 2009, which will be held October 15-18, 2009 at the beautiful Arizona Golf Resort and Conference Center in Mesa, Arizona. Start making your plans now, because conference organizers Rick Schummer, Tamar Granor, and Doug Hennig have once again put together a great selection of speakers and topics for a conference that is not to be missed. I hope to see you all there!

Tags: , ,


SWFox 2008 Session White Papers Published

My session white papers from Southwest Fox 2008, Hidden Treasures: The FoxPro Foundation Classes and Automating QuickBooks with QODBC, are now available for download from the FoxPro Developers page of my website.

Update [Sunday, 07-Jun-2009]: I posted the wrong links to these papers yesterday. The links are now correct.

Tags: , ,



Restore Open Tabs in Internet Explorer 8

Unlike IE7, Internet Explorer 8 does not offer to remember open tabs on shutdown. But after re-launching IE8, you can restore them by going to Tools > Reopen Last Browsing Sesssion.

Tags: ,



VFP Task Pane Tip

The Visual FoxPro Task Pane Environment Manager enables you to set up the environment you want to be associated with each project. Among other things, you can specify the project file, the default directory, the path, the resource file, and any environment settings that may be unique to each project.

When you click the link to a project file in the Task Pane Environment Manager, VFP applies your settings and opens the project. However, the Task Pane window remains open and visible on your screen. If you don't want the Task Pane window hanging around after your project has been opened, there's an easy way to make it disappear.

Select a project in the Environment Manager. Click on the icon to modify the environment set and select the Environment Settings page, as shown in the screenshot. Notice there are edit boxes for two scripts, one that runs before the environment is set and the other that runs after the environment is set and the project is loaded.

To automatically close the Task Pane window, simply add "_oTaskPane.Release( )" as the last or only line in the script that runs after the environment has been set and the project has been loaded. Click OK to save your changes. The next time you open the project from the Task Pane, the Task Pane window will automatically close after the Project Manager window opens and you'll have a clean screen to work with.

Tags: , ,



SWFox 2007 session white papers published

In order not to diminish the value of attending the conference itself, I always wait at least six months before publishing the white papers from my presentations. I didn't really intend to wait 16 months to publish the papers from Southwest Fox 2007, though! In any case, Framework Fundamentals and So You Want to be an Independent Developer are now available for download from the FoxPro Developers page on my website.

Unless the year gets away from me again, look for the papers from my Southwest Fox 2008 sessions to be published sometime in late April.