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)
DO CASE
  CASE LEN( lcPhoneNbr) = 10
   lcResult = TRANSFORM( lcPhoneNbr, "@R (999) 999-9999")
  CASE LEN( lcPhoneNbr) = 7
   lcResult = TRANSFORM( lcPhoneNbr, "@R (   ) 999-9999")
  OTHERWISE
   lcResult = lcPhoneNbr
ENDCASE

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: Visual FoxPro, VFP