"No Holding Back FileMaker Blogging"


Navigation:


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



FREE FileMaker 18 Videos



Become a patron of this FREE web site!


Recent Blogs:

Merging Duplicates
Merging Duplicates

Twenty Nineteen in Review
Twenty Nineteen in Review

Preventing Duplicates
Preventing Duplicates

Deleting Duplicates
Deleting Duplicates

The Downside of Indexing
The Downside of Indexing




The Philosophy of FileMaker recommends PCI!


Quick Tip:

Record Locking
In FileMaker 6 and earlier versions, record locking occurred when a guest on the network clicked into a field. That's was all that was required to prevent others from changing the record. Unfortunately, this also prevented other guests from copying data from a locked record and other basic features. FileMaker 7 has changed how record locking works. In order for a guest on the network to lock a record, he must actually modify a field, allowing user to work with a record without locking it. However, the most important change is when scripting for record locking. You don't want to test if the record is locked by setting a field to a value. FileMaker 7 introduces the Open Record/Request script step which attempts to lock a record without modifying it. If the record is locked, an error of 301 will be returned. If the record is not locked, this script will lock it until the Guest exits the record manually or the Commit Record/Request script step is intiated.



Beginner, Intermediate and Advanced Video Training


Fun Stuff:

Jaymo
David Knight (aka Speedy) started calling me JoMo years ago, because of my initials, and it kinda stuck. Over the years, it's evolved to the current Jaymo but you can call me anything as long as you are friendly. Other nicknames include Hoss, n2 and Wiz.



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

Example
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.

Merging
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.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

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!