But for some reason it deletes all existing records that are not in the table being imported.
How's that again?
Can you describe what you did in greater detail?
Import records with the matching option might overwrite data in matching records, but it won't delete records nor affect the data in existing records that don't match, so we need a more detailed description of what you did and the results you got.
One "gotcha" that suprises novice users when they use the matching recorsd option is that the matching is only done between the table of data being imported and those records in the target table that are in the current found set. Often you need to do a show all records before doing such an import.
Also, do a show all records on your target table, if you have not already done so and make sure that the records were not just omitted from the current found set, but are still present in the table.
In an FM table, I have 786 existing records.
In an Excel table, I have 558 records to be imported.
*** None of the records to be imported from Excel are the same across all fields as the existing records in FM [currently]. Although some of them have the same dates in that field, which is perhaps the problem.
So using Add New Records and Update Existing, I should have 1344 records in the FM table. Which I do if using Add New Records only.
However, if I check Add New Records and Update Existing Records, I end up with 786 records in the FM table. None that were there before the import.
Update existing will overwrite your existing records with data from your source file and this is what is happening.
Why do you need that option? It's almost never used as it relies on the order of records in your target table matching to records in the source file. Update Matching is used as you can use matching values in the two tables to ensure that the correct record in the target table is updated with data from the matching rows of data in your source file.
I would think that the last 228 records in your target table (786 - 558) would remain unchanged by this import.
From FileMaker help:Updating existing recordsWith 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.