Search code examples
foxpro

Foxpro replace with "" not in

I hereby seek for a solution. I have one dbf for january and february each. There are two columns named A and B in each dbf, A is a series of number and B is vlookup, and I want to replace the columnB in february with "new" if its values in columnA do not appear in the column A in january, and "old" for vice versa. My code is the following:

REPLACE ALL B WITH "Old"
REPLACE ALL B WITH "New" FOR A NOT IN (sele A FROM &filejanuary)

Thanks.

Solution

  • The above commentator was close.
    Using SQL UPDATE Commands... 

    * --- Replace ALL Feb.B with "Old" ---
UPDATE February SET February.B = 'Old'

* --- Now change Feb.B to "New" for Applicable Conditions ---
UPDATE February SET February.B = 'New' ;
  WHERE February.A NOT IN (SELECT A FROM January)

    NOTE - there is also a VFP Command approach 

    * --- Replace ALL Feb.B with "Old" ---
SELECT February
REPLACE ALL February.B WITH "Old"

* --- Relate table January to table February through Field A ---
SELECT January
SET ORDER TO A   && Assumes Index built on Jan with Expression = A

SELECT February
SET RELATION TO A INTO January
* --- Replace ALL Feb.B with "New" for No Related A in Jan ---
REPLACE ALL B WITH "New" FOR EMPTY(January.A)

    Good Luck