Methods of importing data into an existing file

There are three ways that you can import data into an existing file. You can:

•	Add new records to the file

•	Update existing records in the file

•	Update matching records in the file

These options are available in the Import Action area of the Import Field Mapping dialog box when you’re importing data into an existing file. Each of these options is described in detail below.

Important The import options that update existing records and update matching records both overwrite existing data during the import process and cannot be undone. To safeguard your data, choose File menu > Save a Copy As to make a backup of the FileMaker Pro file before you perform an import that updates records.

Adding records

When you add records, the import process creates a new record in the target file for each importable record in the source file.

When you add records from most source file formats, the import process adds all the records from the source file. If the source file is a FileMaker Pro file, you can add only the records in the found set and skip adding the omitted records. For more information, see FileMaker Pro format.

Updating existing records

With this option, you replace data in your file with data from the source file. For each field you import into, data from the first importable record (or row of data) in the source file overwrites fields in the first record in the target file. Data from the second importable record (or row of data) in the source file overwrites fields in the second record in the target file, and so on. When you replace data, FileMaker Pro doesn’t examine or compare the data in the files.

You can choose whether to replace or not replace data on a field-by-field basis.

Records in the target file are replaced with the same number of records from the source file. If there are more importable records in the source file, data from the extra records in the source file will not be imported unless you also choose Add remaining data as new records. If there are more records in the target file, data in the extra records in the target file will not be replaced.

Updating matching records

You can update matching records and fields in your target file with data from another file. For example, you might have a copy of a database on your desktop computer and another copy on your laptop computer. You can update the file in your office with the changes you make on the road.

You determine which records in the source file update which records in the target file by choosing one or more match fields in each file. If data in the match field(s) of a record in the target file matches data in the match field(s) of a record in the source file, the record in the target file will be updated with data from the source file.

Match fields must uniquely identify each entity in your database. For example, in a database of people, you could use one match field such as an Employee Number, or multiple match fields such as Last Name, First Name, and Phone Number. (Using Last Name alone might identify more than one person, so it isn’t a good match field to use by itself.)

You also specify the fields you want to import. The contents of all fields you select to import, in all matching records, will overwrite data in the target file, even if the field in the source file is blank.

When the target file contains a found set, only the found records are updated. (If the source file is another FileMaker Pro file, you can also import only from a found set. For more information, see FileMaker Pro format.)

The following table shows an example of how a record in a target file appears before and after being updated by a matching record in a source file. In the Mapping column,

(Windows) or

(Mac OS) indicates a match field,

indicates to import the field, and

(Windows) or

(Mac OS) indicates not to import the field.


Source file	Mapping	Target file	Result
123-456-7890	or	123-456-7890	123-456-7890
John	or	John	John
Q			Q
Smith		Smith	Smith
456 New Rd.		123 Main St.	456 New Rd.
Newtown		Anytown	Newtown
USA		USA	USA
		3/3/1960	3/3/1960
		(408) 555-6789

Notes

•	If the source file is a FileMaker Pro file, you can import only the records in the found set and skip importing the omitted records. For more information, see FileMaker Pro format.

•	Match fields in the target file cannot be container, summary, or unstored calculation fields. The field type of match fields should correspond to the type of data in the matching field. For example, number fields should not be matched to fields containing text.

•	If all specified match fields in a record are empty, that record will not be updated.

•	If a record in the source file matches more than one record in the target file, all matching records in the found set of the target file will be updated.

•

If more than one record in the source file matches a record in the found set of the target file, the target file will be updated with the last matching record in the source file. (During the import process, the matching record in the target file is actually updated multiple times — once by each matching record in the source file — so the last matching source record to update the matching target record determines its final content.)

•	The found set after an update contains the records that were updated (those that matched) and any new records added, regardless of the found set before update.