2 Replies Latest reply on Jul 8, 2010 2:06 PM by trialuser1111

    Relationship or calculation to identify duplicate records (for removal)



      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.