Replace Certain Data In All FoxPro tables

The data in our database might need to be changed once in a while, due to business requirement or human error. For example, a field named lot_code (to store the Lot Code of a manufacturing plant) might need to be changed from 'LT001' to 'LOT001'. But this code has been widely used within your system and is stored in several tables.

In Visual FoxPro, one of the method to solve this is:

1. Select the table
2. Set to an appropriate index
3. Seek and replace the field data in a loop
4. Repeat steps 1 to 3 for other affected tables

But, with this method, you have to be sure not to miss out any fields and tables, or else your transaction's data or reports might produce incorrect results.

To avoid that, here is another method, which scans through all the tables for the affected field(s) and replaces the old value with a new one:
***********************************************************************************
** Program: changefield.prg
** Purpose: Check and replace selected field value in all tables.
** Usage : DO changefield WITH cFields, cOldValue, cNewValue, cDataPath, lExecute
***********************************************************************************

LPARA In_cFields, In_cOldValue, In_cNewValue, In_cDataPath, In_lExecute

IF TYPE('In_cFields') <> 'C' .OR. TYPE('In_cOldValue') <> 'C' .OR. TYPE('In_cNewValue') <> 'C'
=MESSAGEBOX('Location fields information not provided.')
RETURN
ENDIF

In_cFields = "," + ALLTR(LOWER(In_cFields)) + ","
In_cOldValue = ALLTR(In_cOldValue)
In_cNewValue = ALLTR(In_cNewValue)

IF TYPE('In_cDataPath') <> 'C'
In_cDataPath = ''
ELSE
In_cDataPath = ADDBS(In_cDataPath)
SET DEFA TO &In_cDataPath
ENDIF

CLOSE DATA

STORE SET("CONSOLE") TO mOriConsole
STORE SET("CPDIALOG") TO mOriCpDialog
SET CPDIALOG OFF && Prevent Code Page prompt if no code page (same as hit esc).

IF FILE("_script.PRG")
ERASE("_script.PRG")
ENDIF

SET PRINTER TO "_script.PRG" ADDITIVE
SET PRINTER ON
SET CONSOLE OFF

lnFiles = ADIR(gaDatabase, '*.dbf')
FOR nCount = 1 TO lnFiles && Loop for number of databases.
lcFile = ALLTR(LOWER(gaDatabase(nCount,1)))

WAIT WINDOWS "Table " + ALLTR(STR(nCount)) + " of " + ALLTR(STR(lnFiles)) + " : " + lcFile NOWAIT

SELE 0

_mOriError2 = ON('ERROR')
mErrRetry2 = .F.
mErrSkip = .F.
ON ERROR STORE .T. TO mErrRetry2 && Retry to open file if error (happen when CDX file not found). Open again will remove the CDX reference.

USE &lcFile

IF mErrRetry2
ON ERROR STORE .T. TO mErrSkip && Skip the file if still error (happen when file corrupt, etc).
USE &lcFile
ENDIF

ON ERROR &_mOriError2

IF !mErrSkip
** Get the loc field and prepare the REPL statement.
PRIVATE lcReplCommand
lcReplCommand = ''

FOR mFieldCount = 1 TO AFIELDS(aDbfField) && Get field in each dbf file.
mFieldName = LOWER(ALLTR(aDbfField(mFieldCount,1))) && Get field name.
mFieldType = UPPER(ALLTR(aDbfField(mFieldCount,2))) && Get field type.

IF "," + mFieldName + "," $ In_cFields .AND. mFieldType == 'C'
IF !EMPTY(lcReplCommand)
lcReplCommand = lcReplCommand + CHR(13)
ENDIF

lcReplCommand = lcReplCommand + ;
'REPL ALL ' + mFieldName + ' WITH In_cNewValue FOR ALLTR(' + mFieldName + ') == ALLTR(In_cOldValue)'
ENDIF
ENDFOR
USE
**

** Run each REPL statement for current table.
IF !EMPTY(ALLTR(lcReplCommand))
IF In_lExecute
USE &lcFile
ELSE
? 'USE ' + lcFile
ENDIF

lnReplTotal = ALINES(aReplCommand, lcReplCommand, .T.)
FOR lnReplCount = 1 TO lnReplTotal
mReplCommand = aReplCommand(lnReplCount)

IF In_lExecute
&mReplCommand
ELSE
? mReplCommand
ENDIF
ENDFOR
ENDIF
USE
**
ENDIF
ENDFOR

CLOSE DATA
WAIT CLEAR

RELEASE gaDatabase

SET CONSOLE &mOriConsole
SET PRINTER OFF
SET PRINTER TO

SET CPDIALOG &mOriCpDialog

Explanation:
cFields: Accepts multiple fields, separated by comma. This will be useful if there are several fields which store the same content.

lExecute: If true, it directly replaces the data. If false, it just generates the update script (_script.PRG) without affecting the data. The script will be saved in the cDataPath given.

Here is a sample usage on changing the "lot_code" field from 'LT001' to 'LOT001':
DO changefield WITH 'lot_code', 'LT001', 'LOT001','d:\mysystem\data',.T.

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