Import Matching Records Not Working
We needed to update a set of records in an existing database to reassign accounts to new account managers. The source data was in an Excel file, so rather than deal with that, the first thing we did was to move it into a newly created table in FileMaker. We then set up an import script to handle the update of the target table using the "Update Matching Records in found set" parameter and AcctNo = _pkx_AcctNo as the match fields in their respective tables. (Note: All fields involved in the import are defined as Text, are not calculations, and are indexed.)
After running the import, we noticed that about 25% of the records that should have been updated were not touched. We first thought there might be an issue with the source data, so we did a Replace Field Contents on the AcctNo field to clean up any stray spaces, carriage returns, etc. We also added a field to check for the required length in term of number of characters. After doing that, the import still partially failed.
We then picked out a couple of records where the import failed, copied the field value from the _pkx_AcctNo field and pasted it into the AcctNo field in the source table and ran the import again. It failed again.
Finally, we created a relationship between the two fields (AcctNo and _pkx_AcctNo) to see if the relationship would work on those records where the import failed -- it did. While this will allow us to complete the import process as required, my concern is that there is a remaining issue with the importing of matching records that is causing it selectively fail without reason or warning.
Has anyone else seen this? Is there a reported or unreported bug for import matching? (FYI, we are using FileMaker 12.0.4 for Windows and the database is hosted on FileMaker Server 12 running on Windows 2008 Server R2.)