Create a list of possible duplicate records

[argwallace]

Hello,

 

Here is what I am wanting to accomplish. I have a contacts database that contains a number of your basic contact fields (i.e. first and last name, addresses, phone numbers, emails, etc.). I want to add a feature into my solution that will attempt to locate duplicates. I would like to present this data in a simple format that could be followed up by the user to determine which record is kept, deleted or perhaps merged together. My question is how to 1) find these records, 2). display all of the possible records and their accompanying duplicate records in a list, 3) provide options for then  following up. I am open to working with Execute SQL, relationships, etc. Just not sure how to best approach this situation to enable me to produce a good list and format for the results.

 

Any ideas are much appreciated!

 

Stephen

[Brian C]

FileMaker uses the "!" as a means to find duplicate entries in a single field, but what you do with this simple functionality is another matter entirely. Location of duplicates can be a bit complex depending on how exact or fuzzy you want your results to be. Duplicate searching in filemaker can only give you exact matches, so misspellings or sounds like become issues.  You should look into using a SoundEx custom function (check brian dunning's website) to assist with this process to get  a 'sounds like' type of phonetic match if you really want it to be more powerful.

 

As for how you collect the results of your searching, you may want to dump the IDs into a temporary table tagged with the user account so you are only storing 2 pieces of data. There are fancy techniques to store results in a global variable so that the data is not stored physically but lets keep things simple for the purposes of this post.  Doing it this way, you can present the user with a layout of search results.  Just make sure you do a cleanup of the records when they make a selection or choose an action. And just in case they loose connection to the server for some reason, make sure there is a search that occurs up at the very beginning of your script so that it can do a cleanup in case there are records still out there.

Edited June 19, 2014 by Brian C