Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If statement to stop & create a file any of the records have the word 'DUPE' in the field 2

Status
Not open for further replies.
eboughey1008

eboughey1008

Programmer
Jan 6, 2009
31
US

I really just want my program to stop, send me a message, create a file of the duplicate records for my client and then close out of the program if the word Dupes is in my status field. Otherwise I want it to finish the process. It's been years since I programmed so I am pretty rusty. Couldn't find anything in the threads though.

I know this must be easy but I am missing something here. Any help would be much appreciated. Below is the code. I know it's messy because I take snippets from all sorts of code I have to work with my current program.



*SET DEFAULT TO e:\working
*** Create job number directory
CLOSE ALL
CLEAR
SET TALK OFF
SET SAFETY OFF


listno = SPACE (15)
lcfilename= SPACE(60)
listcount = SPACE (15)

@ 5,5 say 'Enter Job Number: ' get listno
READ
jobno = listno
lcJOBNO = "e:\working\jobs\"+ALLTRIM(jobno)+"-T3\"

CHDIR ALLTRIM(lcJOBNO)
lcLastDir = FULLPATH(CURDIR())

*** Populate the status field with word 'DUPE' if one is found ***

thefile = getfile('DBF')

Use Alltrim(Lower(thefile))
INDEX ON UPPER(LEFT(ADDRESS,40)+LEFT(address2,10)+LEFT(ZIP,5)) UNIQ TO T1
DELETE ALL
SET INDEX TO
REPLACE ALL status WITH "DUPE" FOR !DELE() AND EMPTY(status)
RECALL ALL

**** create if statement to stop program, send a message and create file if dupes are found ***

IF status = 'DUPE'
GO TOP
COUNT TO Qty For STATUS = "DUPE"
lcNewFile = ALLTRIM(filename)+"-"+ALLTRIM(TRANSFORM(qty))+" Duplicate Records.csv"
COPY FIELDS NAME, ADDRESS, CITY, STATE, ZIP, LISTSOURCE To (lcNewFile) CSV

@ 15,5 say 'DUPLICATES FOUND. CONTACT CLIENT'
WAIT
CLOSE TABLES
RETURN
ELSE

*** Run program

SET FILTER TO EMPTY(STATUS)

alter table alltrim(lower(thefile)) add column 'recid' n(10)
alter table alltrim(lower(thefile)) add column county c(20)
alter table alltrim(lower(thefile)) add column class c(20)
alter table alltrim(lower(thefile)) add column style c(20)
alter table alltrim(lower(thefile)) add column mileage c(20)
alter table alltrim(lower(thefile)) add column z4 c(20)
alter table alltrim(lower(thefile)) add column crrt c(20)
alter table alltrim(lower(thefile)) add column dpc c(20)

REPLACE ALL recid WITH tag
INDEX on UPPER(LEFT(vin,8))+ALLTRIM(year) UNIQUE TO t
COPY fields recid,first,mid,last,suffix, address, city, state, zip, z4,crrt, dpc, make, model,year,vin,county,class,style,mileage TO bb-uniq

**** copy file for append to file for upload

USE bb-uniq
COPY TO "bb-"+LEFT(jobno,5)+".csv" csv
@ 15,5 say 'File for append copied to csv file: '

ENDIF

WAIT
CLOSE ALL
CLEAR
SET TALK OFF
SET SAFETY OFF




 
Sort by date Sort by votes
That is a bit of a mess.

The first thing I see if a segment that deletes everything, then looks for anything not deleted

Code: 
INDEX ON UPPER(LEFT(ADDRESS,40)+LEFT(address2,10)+LEFT(ZIP,5)) UNIQ TO T1
DELETE ALL
SET INDEX TO
REPLACE ALL status WITH "DUPE" FOR !DELE() AND EMPTY(status)
RECALL ALL

Are you trying to use a unique index to exclude duplicates on the address, just keeping the first non-unique entry?

I think you need to plot out some pseudo code to explain what you actually want before you try and do this, we really have no chance of helping with what you have at the moment.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​
 
Upvote 0 Downvote
I know the strategy o delete duplicates the other way around and, Griff, DELETE ALL or RECALL ALL don't work on ALL records, when an index is set, that does not only set the order of iterating records, a filtered index or a UNIQUE index have the effect to skip rows not in the index. And they are not deleted or not recalled.

Simple demonstration:
Code: 
Create Cursor crsDupes (cvalue c(1), cstatus c(4))
Insert Into crsDupes Values ('a','')
Insert Into crsDupes Values ('b','')
Insert Into crsDupes Values ('b','')
Insert Into crsDupes Values ('c','')
? Reccount(), 'records inserted'

Index On cvalue Uniq To T1
? _tally, 'indexed'

Delete All
? _tally, 'deleted'
Set Index To
Replace All cStatus With "DUPE" For !Dele() And Empty(cStatus)
? _tally, 'marked as dupe'
Recall All
? _tally, 'recalled'
Browse

I still don't know what you actually want to do, eboughey1008, but when the REPLACE ALL has a _TALLY>0 you have (new) dupes.

The strategy depends on a premise, that might not hold true, that no deleted records exist in the DBF at all. Because when you really have deleted rows that really simply where deleted to be removed, you still index them, they might mark an undelete as dupe that isn't a dupe. So the first step should be to PACK such a DBF before you search for dupes.

I said I know it the other way around, you can first DELETE ALL, then index with a UNIQUE index, RECALL ALL and therefore only recall the non-dupes and the first row of dupes, all dupes remain deleted. It has the same prerequisite, you could otherwise recall data that wasn't meant to be recalled.

Bye, Olaf.

Olaf Doschke Software Engineering
 
Upvote 0 Downvote
It's a novel approach, I'll give you that.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​
 
Upvote 0 Downvote
No, it's the tricks of a much older dog I learned them from.

What is good to know is that an index can affect the meaning of ALL.

A SCAN ALL aka SCAN will also only iterate records in index order and only those records in the index.
So when you're interested in only scanning unique records and skip dupes, you don't need to mark them as dupes at all, the index already limits your scope without any additional scope clause or filter.

Aside of a UNIQUE index also an idx that was not open during dbf changes or a filtered index will limit the scope of "ALL" records.

If you ask yourself if that would work for SQL: No, SQL still always runs on new workareas and they neither inherit SET FILTER not current SET INDEX or SET ORDER, SQL will choose which indexes to use itself and will not work with UNIQUE indexes, so you can't trick SQL, but DELETE, RECALL, REPLACE will all be influenced by how an index limits visibility of records.

Bye, Olaf.

Olaf Doschke Software Engineering
 
Upvote 0 Downvote
**Not tested
Code: 
*SET DEFAULT TO e:\working
*** Create job number directory
CLOSE ALL
CLEAR
SET TALK OFF
SET SAFETY OFF


listno = SPACE (15)
lcfilename= SPACE(60)
listcount = SPACE (15)

@ 5,5 say 'Enter Job Number: ' get listno
READ
jobno = listno
lcJOBNO = "e:\working\jobs\"+ALLTRIM(jobno)+"-T3\"

CHDIR ALLTRIM(lcJOBNO)
lcLastDir = FULLPATH(CURDIR())

*** Populate the status field with word 'DUPE' if one is found ***

thefile = getfile('DBF')
**Not tested

Use Alltrim(Lower(thefile))
INDEX ON UPPER(LEFT(ADDRESS,40)+LEFT(address2,10)+LEFT(ZIP,5)) TO T1
locate 
store UPPER(LEFT(ADDRESS,40) to m.address
store LEFT(address2,10) to m.address2
store LEFT(ZIP,5)to m.zip
m.dupe = .f.
skip
do while not eof()
   if UPPER(LEFT(ADDRESS,40)= m.address and LEFT(address2,10) = m.address2 and LEFT(ZIP,5) = m.zip
      replace status with 'DUPE'
      if m.dupe = .f.
         m.dupe = .t.
      endif
   else
      store UPPER(LEFT(ADDRESS,40) to m.address
      store LEFT(address2,10) to m.address2
      store LEFT(ZIP,5)to m.zip
   endif
   skip
enddo

**** create if statement to stop program, send a message and create file if dupes are found ***

if m.dupe 
   lcNewFile = ALLTRIM(filename)+"-"+ALLTRIM(TRANSFORM(qty))+" Duplicate Records.csv"
   COPY FIELDS NAME, ADDRESS, CITY, STATE, ZIP, LISTSOURCE To (lcNewFile) CSV for status ='DUPE'

   @ 15,5 say 'DUPLICATES FOUND. CONTACT CLIENT'
   WAIT
   CLOSE TABLES
   RETURN
else
   *SET FILTER TO EMPTY(STATUS) &&not required all status will be empty

   alter table alltrim(lower(thefile)) add column 'recid' n(10)
   alter table alltrim(lower(thefile)) add column county c(20)
   alter table alltrim(lower(thefile)) add column class c(20)
   alter table alltrim(lower(thefile)) add column style c(20)
   alter table alltrim(lower(thefile)) add column mileage c(20)
   alter table alltrim(lower(thefile)) add column z4 c(20)
   alter table alltrim(lower(thefile)) add column crrt c(20)
   alter table alltrim(lower(thefile)) add column dpc c(20)

   REPLACE ALL recid WITH tag
   INDEX on UPPER(LEFT(vin,8))+ALLTRIM(year) UNIQUE TO t
   COPY fields recid,first,mid,last,suffix, address, city, state, zip, z4,crrt, dpc, make, model,year,vin,county,class,style,mileage TO bb-uniq
endif



 
Upvote 0 Downvote

When I did the first little part for the dedupe, it's just a little code to updated the status field with the word 'DUPE'

Creates the index unique
deletes all those records
sets the index to
updated the field with the word 'DUPE' for all the records that are not deleted
recalls all the records.

It works when i do it at a command prompt so I added it into the program.

After it runs that little bit of code then I want to start the process of looking at the table to see if the status field is populated. IF it is populated then it should create a file, show a message that I need to contact the client and stop running. Otherwise it keeps running.

My problem is the program keeps running whether the field is populated or not so I am missing something here.

I'm wondering if I need a SCAN or a DO WHILE statment.
 
Upvote 0 Downvote

Hi mm0000 -

I just had to add the qty variable back in and add a closed parenthesis on the left(address) portion but it definitely works. The only thing not working is that the exported file won't put the variable LcFilename in it... It just puts the 'qty' stored variable and 'duplicate records'.... Otherwise it's GREAT!



*SET DEFAULT TO e:\working
*** Create job number directory
CLOSE ALL
CLEAR
SET TALK OFF
SET SAFETY OFF


listno = SPACE (15)
lcfilename= SPACE(60)
listcount = SPACE (15)

@ 5,5 say 'Enter Job Number: ' get listno
READ
jobno = listno
lcJOBNO = "e:\working\jobs\"+ALLTRIM(jobno)+"-T3\"

CHDIR ALLTRIM(lcJOBNO)
lcLastDir = FULLPATH(CURDIR())

*** Populate the status field with word 'DUPE' if one is found ***

thefile = getfile('DBF')

Use Alltrim(Lower(thefile))
replace ALL status WITH ''
INDEX ON UPPER(LEFT(ADDRESS,40)+LEFT(address2,10)+LEFT(ZIP,5)) TO T1
locate
store UPPER(LEFT(ADDRESS,40)) to m.address
store LEFT(address2,10) to m.address2
store LEFT(ZIP,5)to m.zip
m.dupe = .f.
skip
do while not eof()

if UPPER(LEFT(ADDRESS,40))= m.address and LEFT(address2,10) = m.address2 and LEFT(ZIP,5) = m.zip
replace status with 'DUPE'
if m.dupe = .f.
m.dupe = .t.
endif
else
store UPPER(LEFT(ADDRESS,40)) to m.address
store LEFT(address2,10) to m.address2
store LEFT(ZIP,5)to m.zip
endif
skip
enddo

**** create if statement to stop program, send a message and create file if dupes are found ***

if m.dupe
alter table alltrim(lower(thefile)) add column FILE1 c(40)
REPLACE ALL file1 WITH FILENAME
REPLACE ALL FILE1 WITH STRTRAN(FILE1,'CQ','')
REPLACE ALL FILE1 WITH STRTRAN(FILE1,'DMS','')

LcFilename = ALLTRIM(file1)

GO TOP
COUNT TO Qty FOR status = 'DUPE'
[highlight #FCE94F]lcNewFile = ALLTRIM(LcJobno)+TRANSFORM(qty)+ALLTRIM(LcFilename)+"- Duplicate Records.csv"[/highlight]
COPY FIELDS NAME, ADDRESS, CITY, STATE, ZIP, LISTSOURCE, file1 To (lcNewFile) CSV for status ='DUPE'

alter table alltrim(lower(thefile)) DROP column FILE1

@ 15,5 say TRANSFORM(qty) + ' DUPLICATES FOUND. CONTACT CLIENT'
WAIT

CLOSE TABLES
RETURN

ELSE


*SET FILTER TO EMPTY(STATUS) &&not required all status will be empty

alter table alltrim(lower(thefile)) add column 'recid' n(10)
alter table alltrim(lower(thefile)) add column county c(20)
alter table alltrim(lower(thefile)) add column class c(20)
alter table alltrim(lower(thefile)) add column style c(20)
alter table alltrim(lower(thefile)) add column mileage c(20)
alter table alltrim(lower(thefile)) add column z4 c(20)
alter table alltrim(lower(thefile)) add column crrt c(20)
alter table alltrim(lower(thefile)) add column dpc c(20)

REPLACE ALL recid WITH tag
INDEX on UPPER(LEFT(vin,8))+ALLTRIM(year) UNIQUE TO t
COPY fields recid,first,mid,last,suffix, address, city, state, zip, z4,crrt, dpc, make, model,year,vin,county,class,style,mileage TO bb-uniq

endif

**** copy file for append to file for upload

USE bb-uniq
COPY TO "bb-"+LEFT(jobno,5)+".csv" csv
@ 15,5 say 'File for append copied to csv file: '

WAIT
CLOSE ALL
CLEAR
SET TALK OFF
SET SAFETY OFF
 
Upvote 0 Downvote
eboughey1008 said:
I want to start the process of looking at the table to see if the status field is populated. IF it is populated then it should create a file, show a message that I need to contact the client and stop running
Click to expand...

Well, then look at what I gave you. You just need to check whether _TALLY>0 right after your
Code: 
REPLACE ALL status WITH "DUPE" FOR !DELE() AND EMPTY(status)

So for example:
Code: 
REPLACE ALL status WITH "DUPE" FOR !DELE() AND EMPTY(status)
IF _TALLY>0
    STRTOFILE("Stop","C:\output\yourfile.txt")
    Messagebox("Duplicates detected, contact client")
    RETURN && or perhaps even QUIT - or put the rest of the code into the ELSE branch.
ENDIF

Bye, Olaf.

Olaf Doschke Software Engineering
 
Upvote 0 Downvote
Besides, your code already has a section creating a file and quitting:

Code: 
IF status = 'DUPE'
GO TOP
COUNT TO Qty For STATUS = "DUPE"
lcNewFile = ALLTRIM(filename)+"-"+ALLTRIM(TRANSFORM(qty))+" Duplicate Records.csv"
COPY FIELDS NAME, ADDRESS, CITY, STATE, ZIP, LISTSOURCE To (lcNewFile) CSV

@ 15,5 say 'DUPLICATES FOUND. CONTACT CLIENT'
WAIT
CLOSE TABLES
RETURN
ELSE

It's just after the RECALL ALL and that destroys your condition for detecting that you have found dupes.

So store the _TALLY right after REPLACE into a variable and then check whether that is >0 after RECALL ALL and you will be good.

Bye, Olaf.

Olaf Doschke Software Engineering
 
Upvote 0 Downvote

eboughey1008 -

You have defined lcfilename = space(60) at the beginning of the program. Thats why there is lcfilename is blank in lcnewfile.


 
Upvote 0 Downvote
i ended up putting a do while statement in after making a uniform filename field...

alter table alltrim(lower(thefile)) add column FILE1 c(40)
REPLACE ALL file1 WITH FILENAME
REPLACE ALL FILE1 WITH STRTRAN(FILE1,'CQ','')
REPLACE ALL FILE1 WITH STRTRAN(FILE1,'DMS','')


GO TOP
COUNT TO Qty FOR status = 'DUPE'

SET FILTER TO !EMPTY(STATUS)
INDEX ON filename TAG filename *** used this to keep the CQ/DMS info in order ***
lcOldJobNo = file1
GO TOP
DO WHILE NOT EOF()
lcNewFile = ALLTRIM(FILE1)+" JOB-"+TRANSFORM(qty)+" Duplicate Records.csv"
COPY FIELDS NAME, ADDRESS, CITY, STATE, ZIP, LISTSOURCE, file1 To (lcNewFile) CSV for status ='DUPE'
lcOldJobNo = file1
ENDDO

alter table alltrim(lower(thefile)) DROP column FILE1

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Filip Brnic
  • Locked
  • Question
While there is any .f. (false logical records) in the table, do something, if there isnt any, do something ekse
Replies
4
Views
291
Chris Miller
Chris Miller
TheLazyPig
  • Locked
  • Question
If an Excel file already exists in the folder... 1
Replies
2
Views
472
igorsandler
igorsandler
bharons
  • Locked
  • Question
Find the location of the column in the VFP table 2
2
Replies
25
Views
2K
ggreen61
ggreen61
AndrewMozley
  • Locked
  • Question
Offering the user the option of database browsing 1
2
Replies
23
Views
2K
AndrewMozley
AndrewMozley
bharons
  • Locked
  • Question
The conversion of the varchar value '1111224080294' overflowed an int column 1
Replies
12
Views
2K
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top