The auto-import / matching feature expects to be used where two parallel tables have the same numbering scheme. Obviously, it's not working well for you. In the situation you describe, one method would be to import all the Excel records into a "scratch" table. First, blow away all the records in the scratch table and import fresh. Then run a script using a relationship between a global ID in the scratch table and the ID field in the "real" table, using a Set Field command. (This method is referred to by Ray Cologon as "processing in place".) Here's a basic outline of how it might work in your situation:
1) Start a loop at the first record.
2) If the ID in the scratch table is populated, then set the global field equal to it. If not, set it to null (empty).
3) Run a series of Set Field commands to update the fields in the real table equal to the values in the imported data.
4) Go to the next record.
The highlighted part is the real trick to the technique. There's a behavior in FileMaker that allows you to set a field in a child table through a relationship using a global on the parent side. If the global is empty, it will automatically be set to the auto-enter value of the key field on the child side when the new record is created.
There are other advantages to this technique. If the import fails for some reason, you haven't blown away "good" data. You can also set error traps on records that fail so you can tell which record(s) blew up.
You can make it a little more sophisticated by using the FieldNames function to fetch the names of the fields on the targeted layout and then using Set Field By Name instead of hard-coding Set Field script steps. Makes the code more expandable.
I've had reliable success in FM v11 doing the import method you describe where it matches on an ID and imports unmatching as new records so I'm surprised to hear it is not working properly for you.
Just to be clear that it isn't working, can you double-check that you are having FileMaker "Perform auto-enter options while importing" in the Import Options? Could it be that the unmatched records are importing but aren't being assigned IDs as you require?
Thanks for your comment, Tom, but...
I have always has success with this technique in the past, too. That's why I'm baffled.
I triple-checked everything before I started this discussion. The number of records in the file stays the same, even though there are four new records that should come into the file. If I insert a fake ID, the new records come in and the number of records in the file increases. Then I have to manually provide the new records with IDs that should be auto-entered. It makes no sense to me. I was hoping to find someone who had run into the same issue—something that only happens under certain unusual circumstances.
This technique will definitely solve the problem, but it seems unnecessarily complicated since FileMaker already has a function in place to import those extra records. I'm just confused as to why it doesn't when it has always worked in the past.
If I can't figure out why it's failing, I will use this method. It's one I had not thought about, and a great reason to be on this disucssion list.
The fact that a fakeID (i.e. non-matching ID) works and a blank ID doesn't gives a clue.
Check the validation for the auto-enter ID field to find out if it requires it to be non-empty or in a numeric range and so on.
It is possible that each of the empty ID records are failing validation and are thus not imported.
These failure-to-import records should be tallied in the post-import dialog that comes up if you do the import manually (not in a script).
Great suggestion, Tom. But it turns out there are no special requirements on the matching field or any of the other fields that data is going into.
By the way, I have it set so I see the post-import dialog and it shows no errors. (I always do that while testing.) It also shows clearly only two records (the ones with matching data) were updated/imported. That means the four records with no ID were not imported. It's easy to see at the end of an import because the current found set would normally include both the updated records and the newly imported records. In this case, only two records are found at the end of the import—unless I insert a fake ID into the "new" records in Excel file.
These results caused me to create an Excel document and replicate your import to test out some ideas.
I found to my surprise that an empty value in the match field consistently causes the imported record to be skipped on import in FM v11. This is not specific to Excel files. It happens with a CSV file created from the Excel file.
I tested it in FM v12 too. Same behavior.
It looks like this is the way it is designed to work.
Thank you for doing that extra work, Tom.
Don't your results seem inconsistent with our expectations? What's the point of importing records that already have IDs since we want FileMaker to assign the IDs? Once there's an ID in the field, FMP does not overwrite it. (I know I'm preaching to the choir here.) I've done a similar import successfully for the last ten years with a text file for an annual convention put on by an insurance company. Do you suppose it's something peculiar to Excel files?
I also checked the documentation before posting, and there's no mention of exceptions to importing the remaining records.
Yes, these results do seem inconsistent with my expectations.
I do find the same behavior with Excel, CSV, or tab-delimited files. This is not exclusive to Excel files. From what I can tell, the important bit is that the file is updating matching records and the match field is empty in the record being imported. FileMaker is choosing in those situations to omit importing the record.
I can only speculate about the reasoning behind this behavior.
When doing an import in this way, the assumption is that the matching field (or fields) is important. The examples in the documentation use real world information like an employee ID or the complete name of the person and so on. The underlying assumption is that this is important information such that a record is bad and should be discarded if that important info is omitted. I think the FileMaker developers were trying to do the user a favor here by doing this additional quality check since imported data can be of all shapes and sizes.
The case you illustrated of a matching import against the internally assigned ID is a tricky one that either fell outside what the developers had envisioned or they had to pick one behavior and chose the one that better benefited the novice users with imperfect data files.
Thanks again for running those tests.
Now that I think about it, my text file for the insurance company does include their EmployeeID. Occasionally we had an employee who wasn't in our convention file that had been added to the staff after we imported their basic info, so this issue never came up.
Sound to me as if I'll have to use the method Mike posted. But I also think the folks who write the documentation should add the caveat to the help files. I've been using FileMaker since version 1.0 and wrote seven editions of Learn FileMaker Pro (never mentioning this, because it never came up), and didn't know about it.
Thanks for your work, Tom.