Mark, Group and Count duplicates in your database
1. Run your duplicate search as you normally wouldIMPORTANT: 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") |
