duplicate records

[lvadmin]

I have a simple program that uses usertable.dbf and imports data from 9600.dbf then deletes 9600.dbf, it waits until 9600.dbf appears again then imports the data again and deletes it.......this is all that it does... on and on

9600.dbf will consist of about 1 to 10 records that contain the fields, fullname, address, city, state, zip, phone, curdate, curtime, list.

now the problem I have is some of the data that some of the records that get written to 9600 have already been loaded into usertable.dbf

Can anyone help me out here, I need a simple .prg that will check each record in 9600.dbf to see if its already in
usertable.dbf (the same fullname, phone and curdate fields)
and if these three fields contain data that is the same it needs to overwrite the existing record....

I have spent alot of time on this and I can't figure it out... please help....


fullname is 40 characters long and is first and last name
phone is 10 characters long and is a phone number
curdate is 8 characters long and is the date the record was written to the 9600.dbf file.

 

[lvadmin]

Another thing I forgot to mention is that if the curdate field is different with the duplicate records, its okay.(that would mean is not a duplicate record)...and that the data can just be appended into usertable.dbf as normal.... this would probably never occur but it could.....


Thanks,

Ross

 

[Mike Lewis]

Lvadmin,

Off the top of my head:

USE 9600 IN 0
USE USERTABLE IN 0
SELECT 9600
SCAN
SELECT USERTABLE
LOCATE FOR ALLTRIM(9600.FULLNAME) = ALLTRIM(USERTABLE.FULLNAME) AND ;
ALLTRIM(9600.ADDRESS) = ALLTRIM(FULLNAME.ADDRESS) + ;
<< other field tests go here >>
IF NOT FOUND()
* This is not a duplicate, so add it to usertable
APPEND BLANK
REPLACE FULLNAME WITH 9600.FULLNAME, << other fields here >>
ENDIF
SELECT 9600
ENDSCAN

I haven't tried to test this, and it might not be 100 percent right, but it should give you a start.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[JRB-Bldr]

First you have to define what is a "Duplicate"?

Can the phone number may be legitimately duplicated with different full names or maybe different dates, etc.

Then once you know what is the definition of Duplicate, create an index within UserTable.dbf on that field or a combination of those fields.

Then when you get the new table (9600.dbf)

SELECT UserTable
SET ORDER TO KeyTag && Created in above INDEX

USE 9600 IN 0 EXCLUSIVE
SELECT 9600
SET RELATION TO <above expression> INTO UserTable
* --- Delete Duplicate Records In 9600 ---
DELETE ALL FOR !EMPTY(UserTable.FullName)
* --- Remove Duplicate Records ---
PACK
SET RELATION TO
* --- Add Non-Duplicate Records Into UserTable ---
SELECT UserTable
APPEND FROM DBF('9600')

Good Luck,
JRB-Bldr

 

[lvadmin]

A duplicate here is defined as any (fullname, phone number, and curdate)
if the fullname and phone number match but curdate is different the record is not a duplicate

I tried the second code and I ran into problems at:

SET RELATION TO <above expression> INTO UserTable

I'm not sure what is ment by <above expression> and I get an error when I compile it....(im kind of a rookie with fox)

please help!

 

[TheRambler]

Hi,

First you have to create the index.
In the command window type:

USE UserTable EXCLUSIVE
INDEX ON fullname+phone+DTOS(curdate) TAG KeyTag

Then use the code provided by JRB-Bldr, changing the line
SET RELATION TO <above expression> INTO UserTable
to
SET RELATION TO fullname+phone+DTOS(curdate) INTO UserTable

("above expression" means the expression used to create the index).