Thursday, June 26, 2008

Fast Replace Fields In All FoxPro Report Formats

I've always found the Visual FoxPro report format as one of the best reporting tools around. It is easy and fast to develop, and there are seldom connection or linking issues (read: Crystal Report).
But of course, it only binds to the FoxPro database. :)

One of the amazing things is that the report can actually be processed as a data table. You can manipulate the report contents as if working with a table, as long as you know what you're doing.

For instance, if there are certain fields' expression in the report format that need to be changed, and it happens that you also need to change the same thing for 100 over reports, here is a faster way to do:

Create a small procedure that will loop through all the reports, and modify the field expression contents for each:

***************************************************************
* Program: rptchgfield.prg
* Purpose: Replaces old field expression with new expression in all reports.
* Usage : DO rptchgfield WITH cReportPath, cOldField, cNewField
***************************************************************

LPARA In_lcReportPath, In_lcOldField, In_lcNewField

In_lcReportPath = ADDBS(ALLTR(In_lcReportPath))
In_lcOldField = ALLTR(UPPER(In_lcOldField))
In_lcNewField = ALLTR(In_lcNewField)

lnFiles = ADIR(laDatabase, In_lcReportPath + '*.frx') && Get all files into an array.

FOR lnCount = 1 TO lnFiles && Loop for each tables (report format).
lcFile = laDatabase(lnCount,1) && Row, Column = 1, file names.

lcReport = In_lcReportPath + lcFile

USE &lcReport
GO TOP
DO WHILE !EOF()
IF In_lcOldField $ UPPER(expr) && Check the field expression that matched.
lcExpress = UPPER(expr)
lnStart = AT(In_lcOldField, lcExpress)
lnReplace = LEN(In_lcOldField)

REPL expr WITH STUFF(lcExpress,lnStart,lnReplace,In_lcNewField) && Replace with the new expression.
ENDIF

SKIP
ENDDO
ENDFOR

CLOSE DATABASE
RELEASE laDatabase

=MESSAGEBOX("Process Complete.")

Run the procedure with command like:
DO rptchgfield WITH "d:\reports\", "phone1", "off_phone"
After completed, all the reports in d:\reports\ with field expression "phone1" will be converted to "off_phone".

If you find this post helpful, would you buy me a coffee?


No comments:

Post a Comment