1 Reply Latest reply on Sep 12, 2011 4:45 PM by philmodjunk

    Merging Duplicate Customer Records

    DustinLohse

      Title

      Merging Duplicate Customer Records

      Post

       

      Hi,

      Is there a script or something I can use to merge all records with the same ID? Or at least a faster way of doing so?
      Any feedback would be appreciated.

      We're using Bidder Central Live Webcast Auction Software through FileMaker Pro v. 6 if that makes a difference. The reason I'm merging them is so we can see a total spent amount.

      New here. Just started working at the Alaska Auction House a few weeks ago. It's a great place. They hired me on as their tech guy, to fix their computers and electronics when need be, and right now I'm trying to work through their database of customers. I have zero experience in this kind of thing, but I try to pick it up quickly.

      Getting to the point: All their computers are new and up to date, yet they are still running an old version of FileMaker Pro. Version 6. I've been assigned the task to go through the entire customer record (10k+ customers) and delete all the duplicate records. I spent a couple hours doing this yesterday. I was able to figure out how to sort them by duplicates (using the !) and have been making slow, by steady progress. I've just been going through the list (whish is still 5,000+ customers) and merging them one by one. Some of them have many, many duplicates. I did notice that all of these duplicates are also sorted by Inet ID. I read up on the forums a little bit and found that a script might be useful. However, my programming knowledge is limited as well.

      Cheers,
      Dustin

        • 1. Re: Merging Duplicate Customer Records
          philmodjunk

          Assuming that the "same ID" is 100% consistent, there's a simple, non-scripted way to filter out all the duplicates in one go, but I don't know if you can do it in FileMaker 6 like you can in a current version. What you do is take an empty copy of this file (a clone created in save a copy as...), set a Unique values validation rule on this ID with "validate always enabled", then import all the client records into this clone. The unique values validation then filters out the records with duplicate ID's during import and you'd then swap this file for the original after using the import to omit all the duplicates.

          This is so much simpler than a looping script, that you might consider downloading a free trial of FileMaker 11, convert a copy of this file to FileMaker 11 by opening it, do the import method to filter out the duplicates, then export the clean data as a tab deliimitted file so that you can then use a clone of your FileMaker 6 file to re-import the cleaned up data.

          If you try that and get it to work, you might show your bosses the trial copy and point out a few of the improvments that are now possible...