Using a Script to Import Data

Troubleshooting

Matching imports

I can't get an import to work using the "update matching records" option. The outcome is never what I expect.

When you choose to "update matching records" when importing data into a FileMaker table, FileMaker tries to match records in the source to records in the target, based on the specified match field or match fields. We've been assuming that there will be at most one source record and one target record that share the same match criteria. But what happens if there are multiple matches on either or both sides?

Assume that you're doing a matching import based on a Social Security number. If there are several records in the source data with the same Social Security number, the data from the last of these records is used to update matching records in the target (assuming there are any).

On the other hand, if there are multiple records with the same Social Security number on the target side, they are all updated with whatever turns out to be the matching data from the source side. So all target records with the same value in their match field(s) are updated with the same data from the source, whether that means updating two target records or two thousand.

If you put both scenarios together, and multiple records in both the source and target share the same Social Security number, the outcome is as follows: Data from the last such record in the source will be used to update all the matching records in the target. If four matching records were in the source, and nineteen in the target, data from the fourth matching source record would be used to update all nineteen matching target records.

Imports and Validation

I imported data, but some of it turned out to be invalid. I have field validation rules set up, but it seems as though FileMaker is ignoring them.

In previous versions of FileMaker, field validation and data imports didn't mix well. FileMaker simply didn't perform any field validation at all on data that was imported. Even if you marked a field as having to be not empty, for example, it was perfectly possible to import records that had no value in that field. This could be an annoying back door around your carefully constructed validation rules.

The situation is better in FileMaker 8 (and in FileMaker 7 as well), but there are still pitfalls. Everything depends on your field validation settings. When you apply validation to a field in FileMaker 8, you can choose to validate the data Only During Data Entry, or Always. If you select the first option, the behavior is similar to previous versions of FileMaker: Imported data is not checked for validity, and it's up to you to handle the consequences. On the other hand, if you choose Always for the data validation on a field, imported records are checked. If this is the case, any record that does not pass a validation check is rejected, and the dialog box that appears at the end of the import tells you how many records were rejected (though not which ones, unfortunately).