Duplicate Alert – prevent FileMaker duplicate entries!
Filemaker duplicates are a problem waiting to happen
Misunderstandings with important customers, shipments arriving at the wrong address and reporting errors that cause embarrassment, loss of revenue or both are risks created by FileMaker duplicates. Put simply, duplicates in your FileMaker database are a problem waiting to happen.
It’s a dog’s life
Nancy, a dog parlor owner, learned the hard way the problems that duplicate entries can create. Nancy owns a dog parlor and cares for people’s pets while they are on vacation. To handle the busy summer and winter vacation months, she hires seasonal staff. Seasonal staff do not know Nancy’s regular customers and so would create a customer entry even though they were already in the database.
When Nancy ran her monthly report she noticed that things were off. Digging into it, she discovered that the duplicate entries were throwing off her numbers and she now worried about customer service that month. Seasonal groomers would be treating regular customers as if they were new.
After spending several late night hours fixing the duplicates, she called me to help her stop the duplicate record problem.
In discussing solutions, we considered forcing staff to search for a customer before they created a new card. But it felt like the risk of double entry was still too great. It would be better to stop the duplicate entry before it happened. What about a duplicate alert? Perfect.
How was I going to stop duplicates from happening?
As I was standing at the entrance of the shop at the computer — with a parrot perched along side me squawking “Hello!” — I needed a method that would be easy for Nancy to follow behind the scenes and quick to develop.
To come up with the right method, I knew a few things:
- to prevent duplicates you need to flag them
- the method needed to be simple enough behind the scenes so that Marica could understand it (no field calculations or scripts)
- the alert needed to be bold and not interrupt work with a pop-up or other type of click
Flagging duplicate entries in FileMaker
To flag duplicates entries you need a self-joining relationship and a trigger field — the piece of data that if there is more than one, there’s a duplicate. In Marcia’s case, we used the customer name as the trigger to flag duplicates.
How do you keep the duplicate alert simple and bold?
Noodling through the possibilities (with the occasional squawked “Hello” from Polly), it dawned on me that a piece of text with the option to hide the piece of text when it was not a duplicate would be just the trick. The duplicate alert would be:
- free of field calculations, scripts, pop-ups and clicks
- as bold as I wanted to make the text
The steps to add a duplicate alert to your own FileMaker database
To add a duplicate entry alert in your FileMaker database follow these steps:
- Decide on the duplicate trigger field (in the dog parlor example it was customer name). You may need to create a calculation field if several fields combined will trigger the duplicate. In the dog parlor example, I used a calculation field that combined First Name and Last Name (First Name & “ “ & Last Name).
- Create the self-joining relationship by going to Manage Database under the File menu and navigating to the Relationships tab.
- Add the table to the graph that needs the duplicate alert (Contacts in our example) giving it a name like “Table Name | self on trigger field” ex. Contacts | self on name
- Place the new table alongside the main table (Contacts in our example) and create a relationship based based on the trigger field. Click OK. You’re done here.
- Make a new calculation field in the main table called Duplicate Alert with this formula: Count(Table Name | self on trigger field) > 1
- For the layout that you want the duplicate alert to appear, go into Layout mode or Edit Layout and add a text object to the layout and type the word DUPLICATE. Style the text as you would want it to appear (red background and white text for example). Place it where it makes sense on your layout.
- Click on the DUPLICATE text object, and in the Inspector on the Data tab type the following calculation: Duplicate Alert <= 1.
- Test it out. Create a duplicate entry and see your duplicate alert appear!
By adding that simple and important piece of duplicate alert text that displays at just the right time on the FileMaker layout, Nancy was able to keep her reports accurate, staff informed and customers happy.
Too busy to add a duplicate alert yourself?
If adding a duplicate alert to your FileMaker database sounds great and you just don’t have the time, no problem. Take the first step. Request a free, no obligation call with me. I’ll help you get this essential functionality added to your own database.
ALLISON, Partner & Software Development Director
I am here to make your work clear and predictable. When your most complex processes are elegantly handled by the push of a button your mind is free to do more of what you excel at. When your custom software application is well constructed, it handles the heavy lifting in the background so the goal remains clear and in focus. Your own unique work style is now the norm. Your custom software is an extension of you, a faithful, smart and powerful application that's easy and open to evolve if/when the time comes.