The simplest method would be to delete the line "Delete all records"...
It sounds as if this table is based on gov classes and not data in the import. Thus the table could be permanent and if needed you could link to it.
Also, creating a file just for the import and cleanup is a useful technique. When done you could then import that.
If there is a unique Identifier value that uniquely identifies each row in your file of imported data, you may be able to set up an "import matching values" type of import where existing records are updated from the import and rows that do not match an existing record are added as new records. In this scenario, you can leave the classifications field out of the list of mapped columns to fields in the Import Records step so that data in it remains unmodified by the import.
Phil, you hit the nail on the head. I did the "import matching records" solution late last night. It worked, and I'll detail it more in this forum when I get home tonight for other's benefit. Now I need to write a script to automate it.
OK, for those that are interested, as I said below PhilModJunk's point was the solution I implemented. I maintain the multiple vendor's product spec data in an Excel spreadsheet.
- I want the spreadsheet to include the serial number (which is my 5 digit product number), and I want that number to range 10000-99999. Therefore I include a calculation cell on every vendors' blank "template" spreadsheet that says "=ROW(Ax+9999)" (this takes the first row, 2, and assigns it item number 10001, and each successive row is one serial number higher.
- Vendor fills in the rest of the data.
- I import copy and paste the vendor daily data into one master Excel spreadsheet.
I import that spreadsheet to FileMaker Pro using this
- file > import records > file...
- in the import options, I map only one field using update matching records in found set (remove mapping relationship on all others)
- the matching is serial number (the ROW calc in Excel) <-> item number (the Primary Key in FileMaker Pro)
- select "add remaining data as new records"
Everything works well, and after the first import I can assign US Customs classification without concern that my next import will overright my new data.
Now, as I mentioned above, I need to script this. Thanks for your help, Phil and Jack.
I map only one field using update matching records in found set (remove mapping relationship on all others)
Correcting myself on one point - above. I've changed it so that I map all of the other fields that are in the spreadsheet to their counterpart in the table, but I only use the matching records check on the serial / item number cell <-> field. This way, updates in the spreadsheet (should an item's specs be changed) populate into the database.