Omitting duplicates in DB keeping one

[enquirerfm]

I have a large DB to which another DB refers to pick up some data. THE DB is however full of dupes - e.g.

shirts,blue,small

shirts,white,medium

shirts,grey,large,

shirts,blue,small etc

How would I find all the records which contain: shirts,blue,small (let's say there are 50 of them) and then keep just one of them. Also, assuming there are 2,500 different products e.g. shoes, t-shirts, ties etc - how can I do it for each product automatically without doing a separate find and delete each time?

I can find the dupes (using !) the problem is it finds all of them. So, even if I omit dupes then I have none left of any one record.

Edited November 2, 2010 by Guest

[bcooney]

This is a great discussion from Six Fried Rice's blog:

http://sixfriedrice.com/wp/deleting-duplicate-records-in-filemaker/

Pay attention to the need to preserve relationships, when deleting duplicates!

[enquirerfm]

OK tried this and another script and not getting very far with either. Something might help me: how can I outout a file which has only one example of each entry in a field. So in the example it would output "shirts,blue,small" to a file but only once.

[bcooney]

Well, "shirts, blue, small" which I assume is not your real data represents three fields: item, color and size. So, I'd concatenate these fields to be:

item & "|" & color & "|" & size.

Then, I'd subsummary by that field and export the recs grouped by the subsummary.

See JMO's site http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000744

type in "duplicates" - he has several techniques.

[enquirerfm]

I tried using this script:

Sort Records [ No Dialog; cDupKey ]

Go to Record/Request/Page [ First ]

Set Variable [ $DupCheck; cDupKey ]

Go to Record/Request/Page [ Exit after last; Next ]

Loop

If [ cDupKey = $DupCheck ]

Delete Record [ No Dialog ]

Exit Loop If [ get(recordnumber) = get(foundcount) ]

Else

Set Variable [ $DupCheck; cDupKey ]

Go to Record/Request/Page [ Exit after last; Next ]

End If

End Loop

It didn't work as is so I had to add in the line with the chevrons then it did.

Sort Records [ No Dialog; cDupKey ]

Go to Record/Request/Page [ First ]

Set Variable [ $DupCheck; cDupKey ]

Go to Record/Request/Page [ Exit after last; Next ]

Loop

Copy [select;cDupKey ] <<<<<<<<<<<<<<<<<<<<<<

If [ cDupKey = $DupCheck ]

Delete Record [ No Dialog ]

Exit Loop If [ get(recordnumber) = get(foundcount) ]

Else

Set Variable [ $DupCheck; cDupKey ]

Go to Record/Request/Page [ Exit after last; Next ]

End If

End Loop

[bcooney]

That doesn't make sense. cDupKey must NOT be set to global storage, but simply a calc field in your table. Could that be the problem?