Mark, Group and Count duplicates in your database

1. Run your duplicate search as you normally would
IMPORTANT: Make sure you have the “Backup Duplicates” selected in the bottom of the window.
2. Close the duplicate window after the process has completed and update the database (no records will actually be deleted).
3. Run the command you just made from the code below.
NOTE:
This command will add 3 additional fields to your data.
DUPKEY (group ID),GROUPCT (group count)and PRIORITY (priority record "1" for priority and "2" for the others)

ON ERROR com_message=message() PUBLIC cDupFilePath,comAlias,comUnique PUBLIC ARRAY comArFiles(1,1) SET SAFETY OFF * Get the current Databases Alias Name comAlias=ALIAS() * Capture all back file names in memory =ADIR(comArFiles,ADDBS(JUSTPATH(DBF(comAlias)))+".rpt") Create the variable name for the duplicate records cDupFilePath=ADDBS(JUSTPATH(DBF(comAlias)))+FORCEEXT(comArFiles(1,1),'dbf') comUnique=SYS(2015) USE (cDupFilePath) IN 0 ALIAS (comUnique) comUnique2=SYS(2015) SELECT PADR(thecode_xx,50) as thecode_xx,"000000" AS Dup_Group FROM (comUnique) group by 1 ; WHERE !EMPTY(thecode_xx) into cursor (comUnique2) readwrite REPLACE Dup_Group WITH ALLT(TRANSFORM(recno())) ALL INDEX ON PADR(thecode_xx,50) TAG thecode_xx SELECT(comUnique) INDEX ON therec_xx tag therec_xx SELECT(comAlias) ALTER TABLE (comAlias) ADD COLUMN Dup_Group C(10) ALTER TABLE (comAlias) ADD COLUMN dupkey C(50) replace all dupkey with "" ALTER TABLE (comAlias) ADD COLUMN Dup_Delete C(1) replace all Dup_Delete with "" ALTER TABLE (comAlias) ADD COLUMN Groupcnt C(4) replace all Groupcnt with "" REPLACE dupkey WITH &comUnique..thecode_xx,; Dup_Delete WITH IIF(!DELETED(),'1','2') Groupcnt with ALLT(TRANSFORM(&comUnique..thedupcnt_)) for SEEK(RECNO(),comUnique) REPLACE Dup_Group WITH ALLT(TRANSFORM(&comUnique2..Dup_Group)) FOR SEEK(PADR(dupkey,50),comUnique2) RECALL ALL USE IN IIF(USED(comUnique),SELECT(comUnique),0) USE IN IIF(USED(comUnique2),SELECT(comUnique2),0) replace Dup_Delete with "D" for "2" $Dup_Delete replace Dup_Delete with "" for "1"$Dup_Delete Alter table (comAlias) drop column dupkey RENAME (FORCEEXT(cDupFilePath,"rpt")) ; TO (cDupFilePath+".txt")

ON ERROR com_message=message()
PUBLIC cDupFilePath,comAlias,comUnique
PUBLIC ARRAY comArFiles(1,1)
SET SAFETY OFF
* Get the current Databases Alias Name
comAlias=ALIAS()
* Capture all back file names in memory
=ADIR(comArFiles,ADDBS(JUSTPATH(DBF(comAlias)))+"*.rpt")
* Create the variable name for the duplicate records
cDupFilePath=ADDBS(JUSTPATH(DBF(comAlias)))+FORCEEXT(comArFiles(1,1),'dbf')
comUnique=SYS(2015)
USE (cDupFilePath) IN 0 ALIAS (comUnique)
comUnique2=SYS(2015)
SELECT PADR(thecode_xx,50) as thecode_xx,"000000" AS Dup_Group FROM (comUnique) group by 1 ;
   WHERE !EMPTY(thecode_xx) into cursor (comUnique2) readwrite
REPLACE Dup_Group WITH ALLT(TRANSFORM(recno())) ALL
INDEX ON PADR(thecode_xx,50) TAG thecode_xx
SELECT(comUnique)
INDEX ON therec_xx tag therec_xx
SELECT(comAlias)
ALTER TABLE (comAlias) ADD COLUMN Dup_Group C(10)
ALTER TABLE (comAlias) ADD COLUMN dupkey C(50)
replace all dupkey with ""
ALTER TABLE (comAlias) ADD COLUMN Dup_Delete C(1)
replace all Dup_Delete with ""
ALTER TABLE (comAlias) ADD COLUMN Groupcnt C(4)
replace all Groupcnt with ""
REPLACE dupkey WITH &comUnique..thecode_xx,;
   Dup_Delete WITH IIF(!DELETED(),'1','2') Groupcnt with ALLT(TRANSFORM(&comUnique..thedupcnt_)) for SEEK(RECNO(),comUnique)
REPLACE Dup_Group WITH ALLT(TRANSFORM(&comUnique2..Dup_Group)) FOR SEEK(PADR(dupkey,50),comUnique2)
RECALL ALL
USE IN IIF(USED(comUnique),SELECT(comUnique),0)
USE IN IIF(USED(comUnique2),SELECT(comUnique2),0)
replace Dup_Delete with "D" for "2" $Dup_Delete
replace Dup_Delete with "" for "1"$Dup_Delete
Alter table (comAlias) drop column dupkey
RENAME (FORCEEXT(cDupFilePath,"rpt")) ;
   TO (cDupFilePath+".txt")

« Back to Commands

New to the
Mailing
Industry?
More Info

Professional Services
More Info

I am especially motivated to take time to draw attention to Vince. In two instances, we potentially faced loss of product – significant loss, but thanks to everyone’s efforts, most notably Vince’s, we ended up with minor re-work and zero loss of stock. He’s patient and thorough, knowledgeable and resourceful. And he’s patient – A Big Plus when dealing with me. Long story short, the guy was able to not only produce a solution, but to prove to me the veracity of it. I hung up the phone saving thousands of dollars in catalog stock, not lost but salvaged, and my department had to spend just a wee bit of time on job re-work (which I was happy to do, all things considered). But I also hung up with a peculiar urge to write about Vince, incongruent as that may be since I rarely am moved to write letters of this sort. So… In a time of crisis, Vince was a terrific asset and ally. If I were his boss, I’d attempt a merit raise authorization, he was that exceptional.

H.D., Shawnee Mission, KS