VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources]

Finding Duplicates

Duplicate records are a constant problem in databases, particularly when you merge data from different sources to update or create them. Even if you're diligent in updating your contact information, unnoticed duplications can trap you into updating the wrong record.

Calculated fields offer a clean and relatively simple way to find duplicates. They do, however, depend on your having a good definition of what constitutes a duplicate record. In this example, we define a duplicate by the simplest criteria: two records with the same first and last name. In a larger database, however, you'll probably want to expand that definition to include some or all of the address as well, to take into account the fact that more than one individual can have the same name.

Because setting up a calculation is time-consuming and you'll probably want to weed out duplicates periodically, we'll create a script to automate the process. Once you run the script, you'll be able to compare the results and then delete the duplicate records.

To find duplicate records

1 .

In the file you want to clean up, choose File > Define > Database (Control+Shift+D/Command+Shift+D).

2 .

When the Define Database dialog box appears, click the Fields tab. Type Dupe Finder for the field name and select Calculation (Control+L/Command+L). Click Create.

3 .When the Specify Calculation dialog box appears, scroll down in the function list on the right to Trim, and double-click to select it (Figure 14.6).

The Trim function deletes any spaces at the beginning or end of the specified text.

4 .In the field list on the left, double-click LastName (Figure 14.7).

5 .

Click to the right of the parentheses and click the ampersand (&) operator button.

6 .Click the quotes button and type a space between the quotes.

The space must be placed within quotes so it can be read as part of the function.

7 .Click to the right of the quotes and click the ampersand operator button.

8 .In the function list, double-click Trim.

9 .

In the field list on the left, double-click the FirstName field (Figure 14.8).

We chose the LastName field before the FirstName field because that's the order in which they appear in our layout. You should adapt the field choices and add text characters (like commas) to fit your needs.

10 .

From the "Calculation result" pop-up menu, choose Text. Click OK twice to finish.

11 .

Choose Scripts > ScriptMaker (Control+Shift+S/Command+Shift+S).

12 .When the Define Scripts dialog box appears, click New.

13 .When the Edit Script dialog box appears, name the script "Dupe Finder."

14 .In the script step list on the left, scroll down to the Found Sets section and double-click Perform Find (Figure 14.9).

15 .In the Script Step Options section, click the "Specify find requests" check box. When the Specify Find Requests dialog box appears, click New.

16 .

When the Edit Find Request dialog box appears, click to select Dupe Finder from the "Find records when:" scrolling list of fields (Figure 14.10).

17 .Click Insert Symbol and select "! duplicates" from the drop-down list, then click Add (Figure 14.11). Click OK four times to finish.

Tips

The best way to look at the duplicate records you've found is in List view, or in a column-format layout. Either one lets you see all the data in a single scrolling page.

Be careful not to choose Delete Found Records when you run the Dupe Finder. Dupe Finder will display both copies of the record so you can compare them. If you delete the found set, you delete both the duplicate and the original.

If you are confident that the new data you're importing is more recent and/or more accurate than what's in your FileMaker file, you can set the Import Action radio buttons in the Import Field Mapping dialog box to "Update matching records in found set." Set the "Add remaining data as new records" to allow nonmatching records to be added normally.