Remove duplicate records from a table

Summary

This article provides a script that will remove duplicate records from a table for a user specified field.

Procedure

1. Open an new script.
   
   
   A. Activate the Project window.
   B. Click the Scripts icon.
   C. Click New.
   
   
2. Copy and paste the following script:
   
   

   Code:
   '-- RemoveDuplicateRecords.ave 
   
   '-- This script will determine if duplicate records exist within a table based
   '-- on a user-defined field. It will create a new table without the duplicate records.
   '-- You will need to have a table document as the active document when running
   '-- this script. It will prompt you for the field to compare values on in order to determine
   '-- if it is a duplicate record. It will also prompt you for an output filename. 
   
   atable = av.getactivedoc 
   avtab = atable.getvtab 
   selbitmap = avtab.getselection 
   
   '-- Set all bits in the selection bitmap to false 
   selbitmap.clearall 
   
   '-- Determine a field to run the comparison on
   '-- Sort the field in ascending order
   
   afieldlist = avtab.getfields 
   sortfield = msgbox.choiceasstring(afieldlist, "Please choose a field that will determine if duplicate records exist:", "Fields") 
   atable.sort(sortfield, false) 
   
   '-- Create a starting value of nil to compare table values
   '-- If your table has nil values, you can choose a new value
   
   avalue = nil 
   
   '-- For each record in a Vtab 
   '-- Convert aRowNumber in the table to a record number in the VTab of the table
   '-- The record number will now be dependent on the order of the sort field
   '-- Compare each record's value string to the previous record's value string
   '-- Each record that is determined not to be a duplicate is set to TRUE in 
   '-- the Selection Bitmap for the Vtab 
   
   for each rec in avtab 
   arecordnumber = atable.convertrowtorecord(rec) 
   comparisonstring = avtab.returnvaluestring(sortfield, arecordnumber) 
   if (comparisonstring = avalue) then 
   continue 
   avalue = comparisonstring 
   else 
   selbitmap.set(arecordnumber) 
   avalue = comparisonstring 
   end 
   end 
   avtab.updateselection 
   
   '-- Export the selected records to a new file (anewVtab)
   '-- Create and open a new table based on the newVtab
   
   newFileName = FileDialog.Put ("$HOME\newfile.dbf".asfilename, "*.dbf", 
   "Choose output filename!") 
   anewvtab = avtab.export(newFileName, dbase, true) 
   anewtable = table.make(anewvtab) 
   anewtable.setname(atable.getname++"Without Duplicate Records") 
   'av.getproject.adddoc(atable) 
   anewtable.getwin.open 
   selbitmap.clearall 
   avtab.updateselection 
   

   
   
3. Click the
   
   button to compile the script.
4. Click the
   
   button to run the script.