Disable the auto-enter options during import and instead, use replace field contents to apply serial numbers to the found set of records immediately after import.
Oh yeah, and assuming that you are doing an "import matching" type operation to import records and add new for those that don't match, do a constrain found set to omit all records except those that do not yet have a value in the serial number field, then do the replace field contents operation.
Thanks, Phil. I'm sorry for the delayed reply. I'm going to see if I can figure out your suggestion above tonight and I'll report back.
Phil, I think I'm doing something wrong. This isn't working for me. I see where you're going with the "replace field contents" on found set, but for the import to work, it seems to want to always pull in all of the data from the source table, right? So in doing that, it is going to "reimport" items that are already in the FM table, overwriting them with Excel data (which does not include a serial number). Therefore, my serial is wiped out with each refresh of the recurring import.
I think I found a simple way to accomplish this, albeit not from within FM. From within Excel, I've added a column to store the serial no. I put it in column A, and set the formula to =ROW(B2)+9999, then fill it down. With the first row containing titles, this sets the first line of data (2) to serial no. = 10000, then the serials will increase by one from that point on. I can then import the whole lot into FM via recurring import, and the serials come along for the ride.
1) Assume the source spreadsheet is never sorted, does anyone see any danger in this method?
2) My preference would still be to contain the serial assignment within FM, so if anyone can tell me where I'm going wrong, I would still appreciate it, and probably adopt the FM solution if possible.
Thank you all very much.
but for the import to work, it seems to want to always pull in all of the data from the source table, right?
Only if the import so specifies. It's controlled by what field mapping options have been set up for the import.
If you must import the data over and over again instead of entering it directly via layouts designed for the purspose in FileMaker, then adding a serial number field makes sense. You can use the serial number column as a match field to the corresponding serial number field in the FileMaker table you can specify that the import be an import matching/add unmatched records as new type of import. The key detail you need to watch out for with this import is to do a show all records to pull all records into your found set before you import the data.
Note: It might still be possible to import the data and generate the serial number in FileMaker if you import from either a named range in the spreadsheet that only defines the newly added rows of if you import into a secondary table, omit or delete the rows that already exist and then import the remaining records from the temp table into your current table. This, however, will leave previously imported records unchanged so this is not an option to consider if you need the data in fileMaker to update when previously imported data in the excel file are modified.