"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
FileMaker 20 Video Tutorials
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Window Locking
 |
Everything Changes
 |
Subsummary Unique Count
 |
Scripted Change Log
 |
Abstracted Log
 |
|
Meta-Consulting
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 Complete Contact Manager
|
Quick Tip:
|
Accurate Placement
To accurately position an object on a layout, select the item by clicking once on it and move it one point at a time using the arrow keys on the keyboard. The arrow keys will also override the Grid feature. In FileMaker 13 and later, Dynamic Guides will also assist you when maneuvering with the arrow keys. |
|
|
FileMaker 20 Video Tutorials
|
Fun Stuff:
|
DevCon History
Here's a list of the FileMaker Developer Conferences* through 2020:
| # | | Year | | Location |
| 23 | | 2020 | | Nashville, Tennessee** |
| 23 | | 2019 | | Orlando, Florida |
| 23 | | 2018 | | Grapevine, Texas |
| 22 | | 2017 | | Phoenix, Arizona |
| 21 | | 2016 | | Las Vegas, Nevada |
| 20 | | 2015 | | Las Vegas, Nevada |
| 19 | | 2014 | | San Antonio, Texas |
| 18 | | 2013 | | San Diego, California |
| 17 | | 2012 | | Miami Beach, Florida |
| 16 | | 2011 | | San Diego, California |
| 15 | | 2010 | | San Diego, California |
| 14 | | 2009 | | San Francisco, California |
| 13 | | 2008 | | Phoenix, Arizona |
| 12 | | 2007 | | Orlando, Florida |
| 11 | | 2006 | | Orlando, Florida |
| 10 | | 2005 | | Phoenix, Arizona |
| 9 | | 2004 | | Phoenix, Arizona |
| 8 | | 2003 | | Phoenix, Arizona |
| 7 | | 2002 | | Palm Desert, California |
| 6 | | 2001 | | Orlando, Florida |
| 5 | | 2000 | | Palm Desert, California |
| 4 | | 1999 | | San Diego, California |
| 3 | | 1998 | | Monterey, California |
| 2 | | 1997 | | San Jose, California |
| 1 | | 1996 | | Santa Clara, California |
* Name changed to Claris Engage in 2020 to match renaming of FileMaker, Inc. to Claris.
**Due to the COVID-19 virus, this conference was changed to a virtual conference. |
|
|
The Philosophy of FileMaker recommends PCI!
|
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.
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:
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:
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:
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.
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!
|
|
Comments:
|
|
|
Greg Hardy
|
02/17/2020
|
|
|
Just wondering if there is a demo file available please John?? I have spent a few days trying to do this but am a bit confused where the buttons should be??
|
Response by: John Mark Osborne
|
02/18/2020
|
|
My sincere apologies. I did every step to include the example file except for one. It should be available now. Thanks for letting me know. Happy FileMaking!
|
|
|
|
|
