Is there a way to append from one file if not duplicate 2

[bebbo]

I want to append from one file to another if it is not there

IE append from file1 into file2 if reference number is not in file2.

file 1 = sysref
1
2

File2 = sysref
2

I just want to append any records into file2 which are not in file2 =from file1

Thanks

 

[EzLogic]

Yes,
Sele * from File1 Where Reference not in (Sele Reference from File2) Into Cursor TempUnique

Sele TempUnique
locate
if eof()
return
endif
scan
scatter memvar
sele File2 && Assuming you want to append to this one
appe blank
gather memvar
endscan

I am pretty sure someone might provide you with an easier one.. but, I think this should do the job.

Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan

 

[Stella740pl]

This should work for you:

SELECT * FROM File1 WHERE Reference NOT IN ;
(SELECT Reference FROM File2) INTO CURSOR TempUnique
SELECT File2
APPEND FROM DBF('TempUnique')

Stella

 

[darrellblackhawk]

I playing around with some ideas and
came up with the following.

[tt]
CLOSE DATA ALL

* Create first table:
* This table's values will be appended to Tmp_file2
* if they meet our criteria
CREATE TABLE (ADDBS(SYS(2023))+"tmp_file1&quot (fld1 i)

* Create second table with an index tag:
CREATE TABLE (ADDBS(SYS(2023))+"tmp_file2&quot (fld1 i)
INDEX ON fld1 TAG fld1

* Used by a function called in the 'for' clause
* of our 'Append' command
bFirstTime = .T.

* Add records to the tables:
FOR i = 1 TO 100
INSERT INTO tmp_file1 (fld1) VALUES (i)
IF MOD(i,3)==0
* Only add numbers divisible by 3
INSERT INTO tmp_file2 (fld1) VALUES (i)
ENDIF
NEXT

USE IN "tmp_file1" && Close append from table

* Make another copy for display purposes,
* open it, and display it in a custom
* browse window
COPY TO (ADDBS(SYS(2023))+"tmp_file2a&quot
USE (ADDBS(SYS(2023))+"tmp_file2a&quot IN 0

SELECT tmp_file2a

DEFINE WINDOW winBrowBefore FROM 2,2 TO 20,30 FLOAT
BROW TITLE "Table before append operation" FIELD fld1:10 WINDOW winBrowBefore NOWAIT

* Reselect our target table and append records:
SELECT tmp_file2
APPEND FROM (ADDBS(SYS(2023))+"tmp_file1&quot FOR Ok2Add()

* And display it in a custom browse window
GO TOP
DEFINE WINDOW winBrowAfter FROM 2,42 TO 20,70 FLOAT
BROW TITLE "Table after append operation" FIELD fld1:10 WINDOW winBrowAfter NOWAIT

FUNCTION Ok2Add()
IF bFirstTime
* The first time this function
* is called, VFP hasn't opened
* the table to append from yet.
* And obviously we don't know
* what the value is. We'll have
* to reopen the file outside of
* this function and determine if
* this first value should have been
* appended to the table.
bFirstTime = .F.
RETURN .F.
ENDIF

IF !INDEXSEEK(tmp_file1.fld1,.F.,"tmp_file2","fld1&quot
RETURN .T.
ELSE
RETURN .F.
ENDIF
ENDFUNC
[/tt]

 

[darrellblackhawk]

I made a mistake in my post.

I was running it in the debugger and
didn't realize that the first value
does get tested.

Although for this method to work properly,
you do have to return .f., the first time
the function is called.

I also added an additional criteria in the test.

Darrell

FUNCTION Ok2Add()
IF bFirstTime
* The first time this function
* is called, VFP hasn't opened
* the table to append from yet.
* And obviously we don't know
* what the value is. We'll have
* to reopen the file outside of
* this function and determine if
* this first value should have been
* appended to the table.
bFirstTime = .F.
RETURN .F.
ENDIF

IF !INDEXSEEK(tmp_file1.fld1,.F.,"tmp_file2","fld1&quot
IF mod(fld1,9)==0 && Test if value is divisible by 9
RETURN .T.
ELSE
RETURN .F.
ENDIF
ELSE
RETURN .F.
ENDIF
ENDFUNC

 

[darrellblackhawk]

And to continue...

Just test if VFP has opened the append from
table yet and return .f. if it hasn't.

Darrell

  FUNCTION Ok2Add()
    IF !USED("tmp_file1")
      RETURN .F.
    ENDIF

    IF !INDEXSEEK(tmp_file1.fld1,.F.,"tmp_file2","fld1")
      IF MOD(fld1,9)==0 && Test if value is divisible by 9
        RETURN .T.
      ELSE
        RETURN .F.
      ENDIF
    ELSE
      RETURN .F.
    ENDIF
  ENDFUNC