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

Importing Data Without Duplications

Although it's good to clean up a database after you've imported records, it's even better to avoid duplication to begin with. Even though FileMaker can find matching records and update them automatically, doing this doesn't give you the option of selecting which of the duplicate records is the one you want to keep.

To compare your new data with your old data, you'll need the field and script created in the preceding section "To find duplicate records." You'll also want to create a clone of the original file first. You can then import the data into the clone, manipulate it in perfect confidence and safety, and import the results into the main file.

To clone a database

1 .

Open the main file in FileMaker. Choose File > Save a Copy As.

2 .

In the "Create a copy named" dialog box, choose Clone (no records) from the "Save a" or "Type" drop-down menu (Figure 14.12). Click Save.

This clone has all the fields, scripts, and other elements from your database, but contains no records. It's a perfect place to import your new data and strip it of duplicates before adding it to the main file.

To relate a cloned database to its original

1 .In the database clone, choose File > Define > File References (Figure 14.13).

2 .When the Define File References dialog box appears, click New.

3 .When the Edit File Reference dialog box appears, click Add File (Figure 14.14). In the Open File dialog box, double-click to choose the original database. Click OK twice to finish.

4 .

Choose File > Define > Database (Control+Shift+D/Command+Shift+D).

5 .When the Define Database dialog box appears, click the Relationships tab. In the Tables/Relationships section, click the Add Table Occurrence button (Figure 14.15).

6 .When the Specify Table dialog box appears, select your original database from the File drop-down list (Figure 14.16).

7 .If your original database contains multiple tables, select the table within the database that holds the information you need to clean up.

8 .

Give the new Table Occurrence a descriptive name (we use Dupe Filter). Click OK (Figure 14.17).

9 .In the Tables/Relationships section of the Relationships tab, click the Add Relationship button.

10 .When the Edit Relationship dialog box appears, select the table inside the cloned database from the left drop-down list, and the new Table Occurrence from the right drop-down list (Figure 14.18).

11 .Select the Dupe Finder field in the scrolling list for both databases. Click Add to add this relationship to the list of related files, then OK to return to the Relationships tab (Figure 14.19).

12 .

Click the Fields tab. Type a new field name in the Field Name box (ours is Dupe Lookup). Select Text (Control+T/Command+T) from the Type drop-down list and click Create.

13 .Click the Options button. The Options dialog box will appear. Select the Auto-Enter tab, then click the "Looked-up value" check box.

14 .When the Lookup for Field dialog box appears, choose Dupe Filter from the "Lookup from related table" drop-down list (Figure 14.20).

15 .In the "Copy value from field" list, click the Dupe Finder field. In the "If no exact match, then" area, verify that the "do not copy" radio button is selected (Figure 14.21). Click OK three times to finish.

So far, we've created a relationship between the original and clone files that will compare the data in them based on the calculation field Dupe Finder. Only duplicate records will have a value copied to the Dupe Finder field in the clone.

To import data without duplicates

1 .Choose File > Import Records > File. In the Open File dialog box, navigate to the file you want to import and double-click to open it.

2 .When the Import Field Mapping dialog box appears, follow the instructions in "To import text data files" in Chapter 12 to map the fields, then click Import.

3 .

When the Import Options dialog box appears, check the "Perform auto-enter options" check box (Figure 14.22). Click Import. When the summary dialog box appears, click OK.

4 .

Choose View > Layout Mode (Control+L/Command+L). Drag the Field tool into the layout. In the Specify Field dialog box, choose the lookup field you just created (ours is Dupe Lookup).

5 .Choose File > Find Mode (Control+F/ Command+F).

6 .Click in the Dupe Lookup field and choose = from the Symbols drop-down menu (Figure 14.23). Click Find.

The = symbol tells FileMaker to find records with no value in the Dupe Lookup field. Since the Dupe Lookup field only contains a value if it is a duplicate record, it will be blank if the imported data doesn't match any records in the main file.

7 .When the find is done, none of the records in the found set are duplicates of those in the main database. Open the main database file and choose File > Import Records > File.

8 .When the Open File dialog box appears, navigate to the database clone and double-click to select it as the file to import. The Import command in the main file will only import records from the clone's found set.

Tips

The Validate Data field option only works when entering data manually. If you have set any field options to validate data, they won't work when you import. Check your field matching carefully before importing.

Although the Dupe Finder/Dupe Lookup process prevents you from importing records that duplicate your existing database, it doesn't exclude duplicates within the data you want to import. If you suspect that the new records aren't clean, you can run the Dupe Finder script on the imported file to check it before you follow the steps to compare it to the main database.