Is it possible that there is a space or other hidden character in the Excel ID field? I ran into something like this once and that was the issue. The solution was to import the data into a new table. Then do a replace field contents with the current value using the filter function. The filtertext should be just the allowable characters. This should remove any characters you can't see and then should match on import.
You should check the existing data just to be sure there is nothing funny in the ID field there, but you never know.
I thought of that; that's why we did a Raplce Field Contents on the data source after it was moved into its own table in FileMaker -- to remove any spaces, carriage returns, etc. Even when we copied an entry from the target table and pasted it into the matching field in the source table, the import failed. That's what's got us concerned.
Thanks for the idea though.
No bug that i am aware of.
However, there are reasons for a failure that have nothing to do with the key values matching.
if you have two or more records in the destination that matches the Key field in the source only the first will be update.
If there are field validation rules on any field that fail the that record will not import.
As a test, and since you already have the relationship built, try using a looping script and a set field to do an import like operation but on a record by record basis and see if you get an error on any of them. Be sure the script sets every field imported into in the import script step. Any of the fields receiving data could fail validation and prevent the import from occurring.
If it is consistent about the records being skipped. Check the ID field in
the target table.
Last thought, possibly an index issue. Turn index off on the field in the
target table. Close the file. Reopen it and turn indexing back on. This
should create a new index for the field. Then try the import.
Sent from my mobile device... Please excuse typos.
There are no duplicate primary key records in either table.
There are no validation rules on the fields in question. Nevertheless, I went ahead and created and ran a script that would update each record individually in a loop using the Set Field script step and monitored it in Script Debugger and used the found set of records that were not updated in the import. There were no errors when the script ran.
Thanks for the ideas, though.
The fact that we were able to create a relationship that works using the ID fields tells me that these are OK. I tried re-indexing the fields to see if that would work, but no luck.
Thanks again for the ideas.
In the target file you are starting with a found set of all records?
No. The found set is 8,900 records out of a possible 10,400. The update table has 2,700 records.
More to the point, the starting found set does include all the IDs from the update table?
Yes, all of them are in the found set.
As I mentioned earlier, we tried this with a found set of 1 record that we knew was skipped, running the import both as part of a script and manually. The update failed both times.
dcawrse wrote, in part:
There are no validation rules on the fields in question.
Any validation rules on any field in a record can keep an import from working, not just on the fields being affected by the import process.
If the relationship you created to test the keys is working OK for all records, then I would guess that something as simple as an empty field that isn't supposed to be left empty might be the validation failure that stops the update (missing zip, city, etc. which might have a validation setting that allows user overrides but the import catches on it).
I would say that could be possible were it not for the fact that the script that uses the Set Field update ran without error. If there were a validation issue, when the script ran while being monitored by Script Debugger, wouldn't it show an error in the 500-509 range?
If the validation is set to allow user override during data entry, the scripted process will run just fine on existing records because you aren't editing the field with validation, but the import process is pickier than a script--it's trying to avoid creating invalid records, and it's probably testing the same issues during an import/update as in an import which only creates new records.
We update the database all the time by importing data as new records and have never have an issue then. Also, there were more than 1,000 records where the import worked. If this were the indeed the issue, I find it hard to believe that it would selectively work on some records and not on others.