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.
Searching Higher ASCII If you look at the storage options for a field in Manage Database, and you see that it is set to a particular language, you may not think much about what it can do for you. Try changing the language to Unicode. This will make FileMaker index include upper ASCII characters, so you can search for values without enclosing them in quotes. For example, you will be able to search for an email address by including the at sign (@).
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.
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.
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:
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:
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:
The only other difference is to attach a validation formula to the "duplicate" field instead of using the "Unique" option. Here's the formula:
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:
Let's start with the script named "Record [Search]" on the data entry layout that starts the process:
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:
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:
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.