AnsweredAssumed Answered

Relationship or calculation to identify duplicate records (for removal)

Question asked by trialuser1111 on Jul 8, 2010
Latest reply on Jul 8, 2010 by trialuser1111


Relationship or calculation to identify duplicate records (for removal)



I have both read and posted about similar issues, but I am revisiting an old problem anew as it has grown to become a real issue with our database.  Essentially, I need to find a way to identify and isolate duplicate records in a table because we had multiple people working on data entry and now we're running into trouble with all the dupes.

Here's what I'm dealing with: we have a table, "Holdings" which logs values over various time periods by Firms and Market Data.  Essentially, every record has two identifying IDs (Holdings::Firm ID and Holdings::Ticker), a series of values, and a set of calculation fields (unrelated to my problem).  I have created a simple calculation field called "UniqueID" which strings together the identifiers and the most recent holding value, e.g.: 1234-ABC-100000 (=FirmID & "-" & Ticker & "-" & 2010 Q1).  What I cannot figure out how to do in FileMaker is identify which holding records have the the same UniqueID, or rather, which UniqueIDs have a value count of >1.

I have read about the "Ugo method" and this looks like it could offer a solution, but I have not been able to get it to work in my database.  I think I need more step-by-step instruction than what I have found so far.  To my understanding, I could use the Ugo method to create portal rows in which I could search for UniqueIDs with values greater than 1.  However, this only gets me halfway home.  If, for example, I find every record with a UniqueID of 2, and delete all found records, I would be deleting BOTH instances of the record.  Is there ANY way to painlessly eliminate duplicates?  I suppose that if I could search by a count of UniqueID, I could repurpose a script that sorts the found results and deletes every nth record.

Any help at all to ease the pain on this one would be greatly appreciated.