"No Holding Back FileMaker Blogging"


Support this site by clicking on a sponsor below or becoming a patron!

Tips & Tricks Videos

Become a patron of this FREE web site!

Recent Blogs:

Everything Changes
Everything Changes

Subsummary Unique Count
Subsummary Unique Count

Scripted Change Log
Scripted Change Log

Abstracted Log
Abstracted Log


Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.

Create a FileMaker Calendar

Quick Tip:

Parts over Objects
When you have placed fields and other objects on a layout, it can be difficult to rearrange the parts -- because a part cannot move above an object. However, if you hold down the ALT key (Windows) or Option key (Macintosh), you can drag a part over a field or other object.

FREE FileMaker 18 Videos

Fun Stuff:

FileMaker, Inc.
FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.

The Philosophy of FileMaker recommends PCI!

RSS Feed
Merging Duplicates
Level: Intermediate
Version: FileMaker 18
Category: Scripting
Tuesday, February 25, 2020
A script that identifies duplicates and deletes them isn't always the best choice. Sure, it works great when you are importing new contacts from a marketing campaign but what if the duplicate comes from an overzealous or lazy employee? Sometimes you need a system for merging a new record with an old one. While this can be completely scripted, I truly believe a human needs to oversee this process in most cases.

Merging Duplicates

I'll be using an example of a contacts solution to demonstrate this technique. The simple reason is it's easy for everyone to grasp and start following along. Not everyone will have a contacts solution so they'll need to modify the technique slightly to meet their needs. I've also included a simple related table of hobbies to demonstrate how related records can be merged. There's no need to get more complicated to demonstrate this technique but your real-life scenario is almost always going to be more intricate. I'm just here to show you the basic concepts. Once you understand, it will be easy to modify the techniques to meet your own unique needs.

Merging Duplicates

Finding Duplicates
The first thing you need to do is identify the duplicates. The calculation and script for creating a found set of duplicates was covered in the first article of this series titled "Deleting Duplicates":

Deleting Duplicates

The second article is not really relevant to the article you are reading right now but I'd still recommend reading it to complete the articles series on duplicates:

Preventing Duplicates

Here's the basic calculation and script for finding duplicates:

name_last & name_first & LeftWords(address; 1)

Merging Duplicates

If you've worked with scripts, calculations and the exclamation point find mode operator then this script is pretty easy to understand. You'll need to adjust the calculation to meet the needs of your data since identifying duplicates is specific to the content in each database.

Once duplicates are identified in a found set, you can easily walk through them and decide which ones you want to merge. Again, I believe this feature shouldn't be completely automated and requires a human to decide what records and what fields on those records to merge. The scripts are just there to ease the process of merging records. To allow the users to pick and choose which records to merge, you will need some sort of interface that initiates the merging feature. I have chosen a portal that displays the records the solution thinks are duplicates. The additional relationship is a self-join to CONTACTS_Duplicates and has two sets of match fields:

Merging Duplicates

Merging Duplicates

FYI: A self-join relationship is one that relates a table to itself.

The first set of match fields uses the same calculation field from the script above, allowing the self-join relationship to see all other records that match the current record. The second set of fields eliminate the current record from the relationship by using the primary key and the not equals operator so you just see duplicate records.

Before merging, I'd start with a simple Go to Related Record step in the portal to allow users to simply look at the duplicate record in a new window. They may want to look at it before committing to a merge:

Merging Duplicates

Before creating the merge script, we'll need a layout that assists the script. Attach the CONTACTS table occurrence (TO) to the new layout. I designed my layout to be smaller than the default window size for my solution so I can display it as a Card Window. This will allow the user to work with the merge feature without leaving the current record. It's an interface concept that puts the user at ease by leaving the original data in the background.

Place two columns of fields on this layout. Start with the fields you might want to merge from the current record using the CONTACTS table occurrence. The second column of fields is identical to the first except that they are from a new self-join relationship called CONTACTS_Merge. I like to call this a temporary relationship because it's based on a global field. The idea is to place the primary key from the duplicate record in a global field and use that global field in a relationship to display the duplicate next to the original.

Merging Duplicates

This merge technique is going to allow merging at the field level. Therefore, you'll need one script for every field you want to merge. Since the script is a single Set Field, you don't need to clutter the Script Workspace. Simply attach the Set field step to a button.

Merging Duplicates

I'd recommend creating the first button in it's entirety so you can quickly make the rest of the buttons with few modifications. Use Smart Duplicate or the Option (Mac) or Ctrl (Windows) along with a drag of the mouse to quick duplicate the button. You can find out more about Smart Duplicate in Stuff Ya Oughta Know.

Once you are done, your layout should look something like the following:

Merging Duplicates

The Done button in the upper right corner simply closes the window. I prefer this over the close window icon FileMaker provides in a Card Window so I turn the option off in the New Window script step. Which reminds me! You probably wanna see the script on the button in the portal that displays this merge layout.

Merging Duplicates

That's right, the script isn't that complicated. As discussed previously, the duplicate record primary key is grabbed from the portal where the merge button is clicked and then placed in a global field to complete the relationship for displaying the duplicate record next to the original. Then, a Card Window is displayed using the new layout we just designed.

Merging Related Records
Merging duplicate records is almost never as simple as a single table. If you have a flat file then you don't need to read any more. If you have even one related table, you'll need to offer additional merging features. Fortunately, the extra programming isn't very difficult at all. All you need is an additional relationship and a lengthy but straightforward script:

Merging Duplicates

Merging Duplicates

The idea in the script above is to grab the primary key from the current non-duplicate record and the clicked hobby from the portal showing the duplicate hobbies. These values are placed in variables so they can be transported to a new record that will be related to the current non-duplicate hobbies relationship. More variables may be needed if there are multiple related values that need to be moved. Once the new record is created, Set Field steps populate the new record and relate it to the current non-duplicate contact record. The last steps simply select the new portal row so it's clear to the user the hobby has been merged.

Once you're done, place two portals on the merge layout. The first is from the HOBBIES TO and the second is from the HOBBIES_Merge TO. Place a button in the HOBBIES_Merge portal that runs the scripts discussed above. Here's what it might look like:

Merging Duplicates

What's Left?
That's pretty much everything I know about duplicates in a series of three articles. You can either prevent them before they are entered, delete them after they are entered or merge them if you want to keep information from both records. If you have any other cool techniques, please place them in the comments.

John Mark Osborne

Example File (available to patrons at $5.00 per month): Download here if you are a patron

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!