Mark, Group and Count duplicates in your database

Build your list of AccuZIP6 commands by requesting commands and sharing commands with other users

Moderators: Chrissy, Steve, Terry, Donna, Dave, Eric, MartyH, Kristen, Geoff, Vince

Post Reply
User avatar
Dave
Site Admin
Posts: 620
Joined: Wed Sep 20, 2006 3:20 pm

Mark, Group and Count duplicates in your database

Post by Dave » Wed Jun 13, 2007 3:55 pm

Use this command to assign a group ID to the duplicates in your database as well as a count on how many are in each group and the priority record based on what you selected in the duplicate setup window.
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)

Code: Select all

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")

Post Reply