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.