×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)

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




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

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 Smileing

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.

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

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
https://www.doschke.name

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

It's a novel approach, I'll give you that.

Regards

Griff
Keep Smileing

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.

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

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
https://www.doschke.name

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

**Not tested

CODE --> Foxpro

*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 

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

(OP)

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.

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

(OP)

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'
lcNewFile = ALLTRIM(LcJobno)+TRANSFORM(qty)+ALLTRIM(LcFilename)+"- Duplicate Records.csv"
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

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

Quote (eboughey1008)

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

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
https://www.doschke.name

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

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
https://www.doschke.name

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


eboughey1008 -

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close