FileMaker News

• Inspector 1.6 is now available
• Complimentary FileMaker Tips & Tricks
• Outlook SendMail Plug-in Available
• New FileMaker Pro 8.5 Training CD
• FileMaker @ eTech Ohio
• Special Offers: Free eBook — Free FileMaker Server 8

Integrate FileMaker Pro 8.5 with Microsoft Office!

Complete your suite with the Office Optimizer Kit from FileMaker! The Office Optimizer Kit from FileMaker gives you the tools you need to make the information stored in Microsoft Office infinitely more valuable to you and your business. Find new ways to use FileMaker Pro 8.5 with Microsoft Excel, Word, Entourage, and PowerPoint.

Learn More »

FileMaker Keeps Health Studios in Balance

Creators of management software for Yoga, Pilates, Dance, and Martial Arts studios power their hosted solution with FileMaker Pro 8.5. Reaching out to the wellness studio industry with a product that meets a fundamental need, Dharma Tribe provides efficient and user-friendly management tools for studio operators.

Watch the Video»
Read the Story»

FileMaker Information and Technology

Resources for the IT manager and technology professional
This new resource gives you access to a wealth of FileMaker technical information all in one location, including tips to help you easily deploy and maintain FileMaker software in your organizations. Find helpful white papers, technical briefs, best practices, technology comparisons, and more!

Learn more »

FileMaker Web Seminars: Connect with Knowledge

Take a look at the archive of FileMaker Web Seminars and learn more about FileMaker products. They're FREE and take place on your Web browser. See a range of topics for the beginner or advanced user.

View Archive»

Save Big with Licensing Programs
FileMaker believes you deserve big savings and the easiest licensing options ever. We've got volume discounts for business and government, and very special programs for education and non-profit customers.

Take advantage of these savings today!

Support Corner

Need a question answered?
FileMaker Customer Service is dedicated to providing our customers with the best non-technical service possible. Non-technical service includes pricing, product availability, pre-sales questions and promotions.

Simply go to the FileMaker Knowledge Base. and ask away!
The FileMaker Support Team

Tips and tricks

Find Duplicates Intelligently
This tip explores two methods for identifying duplicates, both of which integrate some calculation and scripting techniques.
By Stuart Gripman

Few problems cut across databases as pervasively as duplicate records. Although record duplication may be most prevalent in databases containing contact information, such as names and addresses, it has the potential to show up anywhere. It comes as no surprise that just about every client I've worked with over the years has expressed a need to identify and remove duplicates.

Finding redundancy seems simple enough. Just find duplicates and delete one of them, right? Well, sure, but how do you define "duplicate?" Consider this example. Your database has the following two records:

Ben Finn
412 Arlington Boulevard
Eastsound, WA 98070

Benjamin Finn
412 Arlington Bl.
Eastsound, WA 98070-1222

Likely, these two records are for the same person, and you should purge one, but if you just went looking for identical records, these two records wouldn't come up because there are small differences on each line. Your method for identifying potential duplicates must allow for "loose" matches if you're going to catch these two "duplicates." In this tip, I'll explore two methods for identifying duplicates, both of which will integrate some calculation and scripting techniques.

The "human knows best" technique
Typically the chore of finding duplicates is going to involve some judgment calls. Somebody has to look over the records and decide whether a given record is a duplicate. Although it can be a real chore, FileMaker Pro does provide some tools to ease the burden. Using some scripting and calculations, you can let FileMaker Pro generate a list of likely duplicates and present them to you for review.

The first step is to create a calculation field. In this scenario, finding duplicates based on any single field will return a large found set with few true duplicates. The new calculation field will combine the contents of multiple fields. Your script will search for duplicates using the calculation field, thereby reducing the number of false duplicates. For this exercise, I chose to combine the Last Name and ZIP code fields. Finding duplicate last names or ZIP codes alone is sure to return many records that aren't true duplicates. When you search on the combination of the two, FileMaker Pro will find fewer records that are more likely to be duplicates.

In Define Database, create a new calculation field named "zct_DuplicateCalc" and enter the following formula:

NameLast & Zip

The ampersand concatenates -- or "glues" -- NameLast and ZIP together.

Now you're ready to write the script that simply searches for duplicate values in your "zct_DuplicateCalc" field and presents the sorted results.

When you run the script, FileMaker Pro finds the records most likely to be duplicates, sorts them so they're appropriately grouped together, and switches to a list layout where you can review the results. In the example file, the script locates 22 records or 11 potential duplicates out of 500 records in the database. Contrast this result with searching for duplicate data in the last name field alone; that results in 58 records. Finding duplicate ZIP codes alone returns 192 records.

Now that you have a manageable list of likely duplicates, you can check them over and manually delete the unneeded records.

The "blunt force" technique
The "blunt force" technique is a script that finds duplicates based on your criteria and deletes all but the first duplicate without pausing for verification. The scripting here is more complex than the previous technique, but it requires no humanreview. Automation comes at the cost of flexibility here. This technique deletes records without asking first. Be sure you're developing and testing this technique on backup copies of your data. When you or your script delete a record, it's gone for good.

Before you can write the script, you'll have to add two new fields to the database. Call the first "zgt_LastValue" and set it to be a text field with global storage. The script uses this field to compare records and it doesn't have to appear on any layout. The second field is "zt_DeleteFlag," a regular text field.

The script works like this:
• Find all records with duplicate cities.
• Sort by city so the duplicates line up together.
• Starting with the first record, go to each record and compare its "City" value to the "City" value in the previous record.
• If the "City" values match, mark the record for deletion.
• If there's no match, keep going.
• After checking all records, find the ones marked for deletion and delete them.

Set your expectations
As you can see, defining duplicates and accurately finding them usually requires some human intervention. This task can be much less onerous if you take the time to define what "duplicate record" means for your database first.

The approaches I presented in this issue are designed to be compatible with FileMaker Pro 7 through 8.5. With minor modifications, you can apply them to earlier versions.