Finding and deleting duplicate records

[Roger1]

Hi I had a beautiful DB with about 6000 records and someone imported about 4000 duplicate records that I need to get rid of.

I need to write a script (i think) to lookup the duplicates and then delete them

Can someone give me some clues? Thanks

[kennedy]

Clues? The FMP 5.5 Users Guide (which you can download from their site) uses that problem as their example script, pages 10-13 to 10-15.

[kennedy]

P.S. Was the pun of the duplicate note intended?

P.P.S. Actually, its an example of the hard part of duplicate records... when they're logically duplicates, but not textually duplicates!

[Roger1]

No duplicate intended, I thought I had stopped the post in time to correct a spelling error, but embarrassingly not

I'll get hold of the manual and have a look when I get home and see how I go.

Thanks

[LiveOak]

This one should be fairly easy. If the duplicate records were all imported at one time, they will be consecutive records when the file is unsorted. So, just Show All, select the first duplicate, Omit Multiple for the number of records imported, Show Omitted, and delete the found set. Much easier that finding duplicates scattered all through the file. This is one reason I always include fields for creation date, creation time, modification date and modification time in every file (although in this case import can fool them).

-bd

[Roger1]

Thanks, the first thing I thought was 'script', but I'll give it a go and look at the number order of the added records.

[Roger1]

[CobaltSky]

If you still want to use a scripted approach, you may wish to have a look at the file which can be downloaded from the following URL:

http://www.nightwing.com.au/FileMaker/demos/FindUnique.fp5

The technique shown in the file extracts a found set of records which include only one occurrence of a given value in a specified field. If you apply the technique to a field which identifies the duplicates (eg an ID field) and add the following two steps:

Show Omitted

Delete All Records [No Dialog]

Then the second and subsequent versions of any records will be deleted, retaining only the first occurrence of each record.

If you include a secondary sort key (reverse by recordID) in the sort parameters for the script, the result will be instead to retain only the most recently added occurrence of each record. Similarly, a second-key reverse sort on a modification date/time field would retain only the most recently edited copy of each record.