IF ELSE problem

[drpep]

I have two databases (A) (B) that should have the same data in each record. The fields are in order by account number and position number. However, I need to verify on occasion that each contains the same records. I’m attempting to write a short program that uses two fields to verify. If the two fields match in the same record, I want the program to write the position number from (A) to a new field in database (B). However if one of the two field records doesn’t match, I want to place an X in a new field called missing in (A) by that record. This is what I’ve done so far, the IF Found works but the else REPLACE doesn’t. Can anyone assist me? Thanks in advance.

SELECT B
SCAN
SELECT A
LOCATE FOR A.ACCT = B.ACCT AND A.POS = B.POS
IF FOUND()
REPLACE B.CHECK WITH A.POS
ELSE
REPLACE A.MISSING WITH "X"
ENDIF
SELECT LAW07

 

[mm0000]

The following code should do it:

select B
index on acct+pos tag apos &&assuming acct and pos are both character fields
set order to apos

select a
set relation to acct+pos into b
scan
if a.acct=b.acct and a.pos = b.pos
replace b.check with a.pos
else
replace missing with 'X'
endif
endscan

 

[jimstarr]

What do you mean that the ELSE ... REPLACE doesn't work? Is it possible that all the records match? Also, I don't see an ENDSCAN.

Jim

 

[JRB-Bldr]

I don't see in your code where you set up a RELATION between table A and B.

If you want to SCAN B without any RELATION set up between the two tables, you would need to do something like:

SELECT B
SCAN
   * --- Get B Table Values ---
   mcAcct = B.Acct
   mcPos = B.Pos
 
   * --- Now Look for matching Record IN A ---
   SELECT A
   LOCATE FOR A.ACCT == mcAcct ;
      AND A.POS == mcPos
   IF FOUND()
      SELECT B
      REPLACE B.CHECK WITH mcPos
   ELSE
      REPLACE A.MISSING WITH "X"
   ENDIF

   <do whatever else>

   * --- Continue SCAN To next record ---
   SELECT B
ENDSCAN

But your easiest method would be to set up a relation and then do a single REPLACE command.

SELECT A
SET ORDER TO AcctPos  && Index already built on acct+pos 
* --- Set Default Value ---
REPLACE ALL A.MISSING WITH "X" 

SELECT B
SET RELATION TO acct+pos INTO A
* --- Set B.Check Value & Clear Default A.Missing ---
REPLACE ALL B.CHECK WITH A.POS,;
  A.MISSING WITH " " ;
  FOR !EMPTY(A.ACCT + A.POS)

SET RELATION TO

Good Luck,
JRB-Bldr

 

[jimoo]

If you issues a REPLACE command for a table that is not in the current work area, VFP (especially VFP 6 and before) can be temperamental and not always write the data.

Prior to the replace command, select the table. If necessary, select it back for the scan to work.

IF FOUND()
SELECT B
REPLACE B.CHECK WITH A.POS
SELECT A
ELSE
REPLACE A.MISSING WITH "X"
ENDIF
SELECT

Notice the 2 Select statements I added before and after the first replace command.


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Olaf Doschke]

Hi Mike,

you know, that Replace can replace within many tables at once. Therefore the table.field syntx and not using IN TARGETALIAS sometimes makes sense, especially in conjunction with relations.

In this scenario the replace should only be done in one of those two tables as either a record in B is missing, which is denoted in A or a record in B is found, which is denoted in B. So you are right here and that would work in the original code:

IF FOUND()
   REPLACE CHECK WITH A.POS IN "B" 
ELSE 
   REPLACE MISSING WITH "X" IN "A" 
ENDIF

Bye, Olaf.

 

[Imaginecorp]

Hello Drpep;
You are sitting at the End Of File (EOF()) if the record is not found. Your method is not the way to do this. Pick another field that is common to both tables Example: Customer ID. Both tables should be indexed on it
select B
Scan
cId = b.ID
select a
if indexseek(cid)
=indexseek(cid,.t.)
if A.ACCT = B.ACCT AND A.POS = B.POS
REPLACE B.CHECK WITH A.POS
ELSE
REPLACE A.MISSING WITH "X"
ENDIF
select b
endscan

 

[DSummZZZ]

Hey all,

Just as an FYI, jimoo said:

If you issues a REPLACE command for a table that is not in the current work area, VFP (especially VFP 6 and before) can be temperamental and not always write the data.

That is correct.


If the currently selected table is at EOF() and you try to do a replace in another table (unselected work area), it will ALWAYS fail. Selecting the table to do the replace in would most likely fix the problem:

SELECT B
SCAN
SELECT A
LOCATE FOR A.ACCT = B.ACCT AND A.POS = B.POS
IF FOUND()
REPLACE B.CHECK WITH A.POS
ELSE
[COLOR=red]SELECT A[/color]
REPLACE A.MISSING WITH "X"
ENDIF
SELECT LAW07

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Imaginecorp]

Dave I am sorry but that is Not correct.
Select A
LOCATE FOR A.ACCT = B.ACCT AND A.POS = B.POS
if Found()
*********
else
select a
*Where is the record pointer in A?
*Its at EOF()
*Cannot do a replace in EOF()
endif

 

[DSummZZZ]

You're right. I wasn't paying attention how the code was written. I only wanted to point out that a REPLACE won't happen in a table which is not currently selected if the table which is currently selected is at EOF().


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm