"No Holding Back FileMaker Blogging"


Navigation:


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



The Philosophy of FileMaker recommends PCI!



Become a patron of this FREE web site!


Recent Blogs:

Deleting Duplicates
Deleting Duplicates

The Downside of Indexing
The Downside of Indexing

Loss of Focus
Loss of Focus

Naming Standards
Naming Standards

Restoring Find Criteria
Restoring Find Criteria




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.



Document Management Videos


Fun Stuff:

Claris Commercial
Few people know that Claris produced a commercial and it aired on national television. It was one of those favor things so it only played once. I'm not saying it's anywhere close to the impact of the Apple Super Bowl commercial but it's fun to have a look. Download the Claris commercial



Tips & Tricks Videos







RSS Feed
Preventing Duplicates
Level: Intermediate
Version: FileMaker 18
Category: General
Tuesday, January 14, 2020
Preventing duplicates from ever being entered in the first place is my personal preference. Sometimes we don't have a choice and have to remove them after the fact. But, if you can stop them from ever being entered, it's going to make your life much easier. In this article, we'll discuss a variety of techniques for stopping duplicates in their tracks including validation, script triggers and plain old vanilla scripting.

Preventing Duplicates

Read First!
The first article in this series covers how to remove duplicates once they've been entered. We go over then ins and outs of searching by the exclamation point (!) and define a calculation that assists with duplicate record detection. The identification process is then scripted and even pushed to the point of actually deleting the duplicates automatically. The article even goes through an example of how to use the export feature to remove duplicates. Even if you are familiar with all these concepts, it's a good idea to read the entire article before reading this one.

Deleting Duplicates

History
One of the earliest methods I learned for preventing a duplicate record was via validation. This was way back in FileMaker Pro 3.0 when there wasn't the option to "do not replace existing value in field (if any)" on an auto-enter calculation. Therefore, it was required to use a relationship and a live calculation field. This worked pretty well except that editing a record that wasn't a duplicate would end up locating itself. There were all kinds of workarounds like using the Case and Lookup functions in conjunction. These days, you can specify multiple key fields and exclude the current record from the relationship using the primary key and the not equals relationship operator. In the following section, we'll cover an updated version of this validation for uniqueness.

Unique Validation
Probably the easiest method for validating a duplicate is to use the "Unique" option. The trouble is, it needs to be applied to a single field so a calculation is required. No problem since we defined a great formula in the first article of this series (see link above). However, the calculation can't be a live calculation field since there's no way to validate a calculation field. The solution is to attach the calculation field to a field with an auto-enter formula with the option to "do not replace existing value in field (if any)" so the formula updates just like a live calculation field.

For ease-of-use, here's the formula from the previous article that should be entered as an auto-enter formula in a text field named "duplicate". It's not a perfect formula and should be adapted to the data contained in your unique FileMaker solution:

Left(name_first; 1) & name_last & LeftWords(address; 1)

Once this formula is placed in the auto-enter calculation portion of the "duplicate" field and the option to "do not replace existing value in field (if any)" is unchecked, all you have to do is click the "Unique" option in the validation area of the "duplicate" field:

Preventing Duplicates

I like this technique because it's fairly easy to implement, just requiring the combination of a basic auto-enter calculation formula and the "Unique" validation option (which is just the click of the mouse). It also uses internal functionality to locate duplicates, which is almost always faster than programmed methods, as you'll see in the next technique.

Relationship Validation
I always tell my students that relationships are one of the slowest things in FileMaker. I say this because so many people use a relationship in place of what could be done so much faster and better with a find, a subsummary report or any number of other solutions. It's all about using the right tool for the job. With that said, it's also best to have as many tools on your tool belt as possible. You never know mention circumstances might dictate the need for a specialized approach to solve a problem.

The relationship method is very similar to the unique validation method described above except that it uses a relationship to identify the duplicate entry. Same calculation, just a different validation option. Let's start with the relationship which is a self-join:

Preventing Duplicates

Preventing Duplicates


The only other difference is to attach a validation formula to the "duplicate" field instead of using the "Unique" option. Here's the formula:

IsEmpty(DUPLICATES_Check::duplicate)

I've seen some folks use the Count function but that can slow down your solution since it has to aggregate multiple related records rather than just grabbing the first related record, if it exists. Needless to say, the IsEmpty function is usually faster.

So, why use the relationship method when the unique technique is so easy? Like I said, its' always a good idea to have multiple tools on your belt. In this case, while the relationship method clutters up the graph with an extra table occurrence, it also allows for multiple matching fields. For example, the duplicate checking fields might be of different fields types and can't be represented in a single calculation with a single output type.

Lazy Users
The most common reason for duplicate entries is user laziness. Maybe because they are too busy or just don't care. Whatever the reason, folks don't check if the entry is already in the database. One approach is to force them to check by asking for the basic record information before creating the record. This takes a lot more programming work but can be much faster than validation methods when there are a lot of records.

The first step is to create three global text fields for temporary storage of the first name, last name and address:

xfirst
xlast
xaddress


The next step is to create a layout that will be used as a window card to identify duplicates. It should be smaller than your data entry layout so it can hover above it without blocking all the information entirely. This gives the user the impression they haven't gone to another place, ultimately making them at ease with the interface. This layout will be a list view containing the first name, last name, address, city, state and zip fields. It will also have three buttons on it that will be discussed below:

Preventing Duplicates

Let's start with the script named "Record [Search]" on the data entry layout that starts the process:

Preventing Duplicates

The script starts by asking the user to enter the first name, last name and address of the contact to be entered. In order to preserve the current found set, a new window is opened and the search begins. I prefer spelling out my find scripts since they are easier to read the restored Perform Find steps. Also, the new window is specified as a Card since later on it might be used to display the duplicates. No sense in opening a regular window, closing it and then opening a card window.

If no records are found, a new record is created using the "Record [New]" script:

Preventing Duplicates

This script closes the window, creates a new record, enters the data from the global fields and goes to the next field in the tab order. Very simple but that's all that's needed.

BTW: The window won't be seen opening and closing when there are no duplicate records. It happens so fast, FileMaker doesn't redraw the screen. If you do see unwanted redraw remnants then simply add a Freeze Window script step.

If one or more records are found, the scripts simply remains on the card window layout. The "Record [New]" script should be attached to a button in the header so the user can choose to create a new record rather than choose one of the possible duplicates. Next to the "Record [New]" button is a Cancel button that simply closes the window. Lastly, a button in the body runs the following script titled "Record [Choose]" so the user can choose one of the possible duplicates as the record they want to modify:

Preventing Duplicates

To move the selected record in the card window to the data entry layout, a variable is set to the primary key. When the window is closed, a search is performed for the selected record. It's important to perform the search with "==" operators if you have a primary key that is a text field. Otherwise, FileMaker may locate multiple records since text fields can have partial matches (e.g. "10" is mistaken for "1"). If you have a number result on your primary key, omit the "==" operator.

What do I do?
Surprisingly, the most common technique I use is a simple scripted search for duplicates. Rarely do I get clients who want to prevent duplicates from being entered. That's because they don't end up getting a lot of duplicates either through good employee behavior or limited repeat customer interaction. And, sometimes clients need to experience rampant duplication of records to want a solution like the ones described above. The truth hurts sometimes but it always teaches us a lesson. What it boils down to, is offer the multitude of solutions but try to choose the simplest solution that solves their real problem. No need to offer the most complex solution unless it benefits the client.

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

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!