Delete all but one duplicate record?

[ebot]

Hi, just wondering if it's possible to write a script to find multiple duplicate records (ie. everyone with the surname 'adam' and 'bert' and 'cat' etc.), then delete all but one of each duplicate record (ie. leave one adam, one bert, one cat ...?

Of a database of 3000 records, 1500 are duplicates (argh).

Any help gratefully appreciated. In the meantime, it's back to the manual ... *sigh*

Cheers,

[AndriesV]

Assuming

1- all other data fields for the duplicate records are the same (otherwise it will be more difficult to script which records may (not) be deleted)

2- a UniqueRecID number field exists with a unique number for each record e.g. a serial number field, if not existing you could create this field first and fill it with a serial number using the Replace command

The following script would mark all but the firs records with a duplicate surname. You could make the script directly delete the duplicates but this way you can check visually first before manually deleting the duplicates. (2nd you avoid issues with a looping script that either omits or deletes records, there have been previous posts explaining the propper way to do that it you want to find out.)

- Create a selfjoin-relationship on the field surname (if that is the field that should be made unique)

(probably best to set it to "unique values only" in define fields so no new duplicates can be entered)

- Create a field "DuplicateCheck"

- Create a script with the following steps

- find all

- go first record

- loop

- if (count(Selfjoin::Surname) > 1) and (UniqueRecID > min(Selfjoin::UniqueRecID))

- set field DuplicateCheck = "Duplicate"

- else

- set field DuplicateCheck = "Unique"

- end if

- go next record (exit after last)

[BobWeaver]

Another method is given as an example script in the filemaker manual on page 10-13.