Rick,

One simple way to remove duplicates is to:
1. Have a scratch field to store true/false if it is a duplicate. I'll call this field 'Temp'.
2. In a Script:
2a. Sort the records on the Field to find duplicates of. I'll call this field 'Desired'.
2b. Use the FMP script step 'Replace' on the 'Temp' field and replace it with the formula:
Desired = GetNthRecord( Desired ; Get( RecordNumber ) -1 )

Since the records are sorted by 'Desired', after the Replace script step:
'Temp' field has "0" if the previous record is the different than the current record.
'Temp' field has "1" if the previous record is the same as the current record.

Therefore, the first instance of duplicates has as a "0" and the remaining duplicates has a "1" in 'Temp'.

Now you can find all "Temp = 1" and delete them. You will be left with the first instance of each duplicate. You can modify this if you want to keep the last.

It doesn't matter if 'Desired' is indexed, stored or unstored except that the sort time will get progressively longer...

Removing duplicates can be done without using field 'Temp' and without sorting on 'Desired if you want to make some custom functions that returns the RecordNumbers of all duplicates. Then you take the RecordNumber list and delete records an a script loop starting at the end. (Deleting records starting at the start changes the remaining record numbers in the list and will delete the wrong records.)

The advantage is that (1) you don't have to sort and (2) you can make this a generalized Script that is passed a FieldName only. This second method would be unnecessary if FMP allowed you to sort programmatically by Fields plus their sort directions but unfortunately FMP doesn't have this ability. If it did you could use the simple approach above to generalize this in one script to work on any field.

The simple approach above can easily be extended to match multiple fields via a script that sorts on multiple fields and concatenating the field values in the 'Replace' script step as:
Desired1 = GetNthRecord( Desired1 ; Get( RecordNumber ) -1 ) and
Desired2 = GetNthRecord( Desired2 ; Get( RecordNumber ) -1 ) and
Desired3 = GetNthRecord( Desired3 ; Get( RecordNumber ) -1 ) and
Desired4 = GetNthRecord( Desired4 ; Get( RecordNumber ) -1 )

Regards,
Steven




On Jan 7, 2008, at 1:16 PM, Frederick J. Krall wrote:

You can remove dupes using a self-join:

1. Give every record in the file a unique serial number. Create a number
field, "RecordID" and set it to autoenter serial numbers. Find All and
Unsort the file, then do a Replace on that field to set all records to a
serial number.

2. Create a self-join relationship on the field you want to check dupes on.
Call this relationship "Dupes."

3. Then create a final Calculation field, number result, called DupeFlag
that compares the RecordID to the RecordID through the self-join:

RecordID=Dupes::RecordID

This field will return a 1 for the first record in every group of duplicates
and a 0 for every other record.

To remove duplicates, you simply find for "1" in RecordID, Find Omitted,
then Delete All. Poof, they're gone, so save a copy of the file before you
begin this task.

I learned this from David Thorp in the legacy days of FM3 and have used it
ever since. There may be more modern ways to do it, but this works for me.

-----------------
Rick Krall


> From: "R. Kirk McPike" <fireball1244@[Protected]>
> Reply-To: "FileMaker Talk" <fmpro@[Protected]>
> Date: Sun, 06 Jan 2008 23:09:38 -0600
> To: "FileMaker Talk" <fmpro@[Protected]>
> Subject: Remove Duplicates
>
> Is there a way to remove duplicates from a FM database? I'm using the
> donations application, and I have some lists to import, and there are some
> duplicates on the lists. I don't see a command, like there is in Access (or
> even Excel 2007!) to remove duplicates. Am I missing something? Is there a
> way to do this using AppleScript?
>
> Kirk