How to delete all duplicates

[philipcaplan]

I have a table with about 100,000 records. One of the fields in the table is called "DecimalNumber" (type is "Number").

There are many records (perhaps 10 to 20%) which have the same value in that field as another. Sometimes only 2 records have common values, sometimes more.

What I want to do is to delete all records except one of each of those which share a value, thereby removing 'duplicates'.

How do I achieve this? I assume a script will be needed, if so please can you give me the whole of that script.

Many thanks in anticipation.

Edited June 15, 2015 by philipcaplan

[Chuck]

Off the top of my head, without testing it at all, which means don't just write it and trust it. Try it on a backup copy of your database first:

Show All Records
Sort Records [table::DecimalNumber]
Go to Record/Request/Page [ First ]
Loop
  If [ $_number = table::DecimalNumber ]
    Delete Record/Request [ No dialog ]
  Else
    Set Variable [ $_number ; table::DecimalNumber ]
    Go to Record/Request/Page [ Next ; Exit after last]
  End If
End Loop

 

[comment]

Try:

# FIND DUPLICATES 
Enter Find Mode [  ] 
Set Field [ YourTable::DecimalNumber; "!" ] 
Perform Find [  ] 
# SORT BY THE DecimalNumber FIELD 
Sort Records [ Restore; No dialog ] 
# DELETE DUPLICATES 
Go to Record/Request/Page [ First ] 
Loop 
 If [ YourTable::DecimalNumber ≠ GetNthRecord ( YourTable::DecimalNumber ; Get ( RecordNumber ) - 1 ) ] 
  Go to Record/Request/Page [ Next; Exit after last ] 
 Else 
  Delete Record/Request [ No dialog ] 
 End If 
End Loop 
# 
Show All Records 

 

Edited June 15, 2015 by comment

[philipcaplan]

Hi comment.  

(1) Where you have "Get(RecordNumber)-1" are you assuming I have a 'unique serial number' called RecordNumber which I can use, because I'm afraid I don't. The dataset came from an outside tab-separated file which I opened in FMPro, and did not have any such numbering.

(2) Or, is "RecordNumber" something that FMPro will understand as being an internal number in sequence?

(3) If not, it seems to me that the only way a script can know if two records contain a duplicate value for DecimalNumber is if the Next Record's value for DecimalNumber is the same as the current record, in which case either one or the other can be deleted.

[comment]

(1) Where you have "Get(RecordNumber)-1" are you assuming I have a 'unique serial number' called RecordNumber which I can use, because I'm afraid I don't.​

No. Get(RecordNumber) is a Filemaker function - look it up.

[particle]

On 6/16/2015 at 4:21 AM, comment said:

Try:

# FIND DUPLICATES 
Enter Find Mode [  ] 
Set Field [ YourTable::DecimalNumber; "!" ] 
Perform Find [  ] 
# SORT BY THE DecimalNumber FIELD 
Sort Records [ Restore; No dialog ] 
# DELETE DUPLICATES 
Go to Record/Request/Page [ First ] 
Loop 
 If [ YourTable::DecimalNumber ≠ GetNthRecord ( YourTable::DecimalNumber ; Get ( RecordNumber ) - 1 ) ] 
  Go to Record/Request/Page [ Next; Exit after last ] 
 Else 
  Delete Record/Request [ No dialog ] 
 End If 
End Loop 
# 
Show All Records 

 

Thanks. Work for my table.