The best approach I've been able to come up with is to script the import process and include steps that use the Set Next Serial Value to update any auto-entered serial numbers after import.
For a single table import, you script might look like this:
Go To Layout [//specify import layou]
Import Records [//specify import details]
Sort Records [Restore ; No dialog ] //Sort on serial number field in descending order
Go To Record [First]
Set Next Serial Value [Table::SerialNumberField ; Table::SerialNumberField + 1]
Awesome, thanks for the response. I guess I've been resisting moving to scripted imports for a while - doing it manually allows me to see what's going on, I guess - call me a paranoid newbie :-p But it's probably time to start automating it to save myself the headache of going through every single table and importing, then updating auto-enter serials.
Question: That "Sort Records" script step - does that actually mess with the table in question, in terms of reordering its records, permanently? If so, I guess I'd want to re-sort it after the above script, to put it back in ascending order, yes?
You can certainly re-sort a given table occurrence's found set as often as you need to.
You may not realize this, but each "box" (called a table occurrence) in Manage | Database |Relationships has its own current record, found set and sort order. Thus, you can have many different found sets for the same table (and sort orders, current records) as you want simply by defining additional table occurrences in the relationship graph and attaching each to a different layout.
Interesting! That is a *really* handy thing to know. I have two layouts which reference the same table, one is a list view and shows the most recent (sort by auto-enter ID number, descending); the other is a normal layout. I'd rather keep all layouts in ascending ID-number order (i.e., the order in which they were created) - so maybe I could create another TO of this one table and thus it can have its own sort order. Brilliant tip, thanks!
Question about your import script - why sort descending then go to record (first)... why not sort ascending and go to record (last) ? I've done it verbatim to your spec and it works brilliantly (SO worth the investment of time/effort) but I'm just wondering if there's any reason why ascending/last wouldn't also work?
Lastly, regarding the import file. I noticed that the Import Records script step doesn't let you specify field-matching options unless you also specify a specific file to import. This reduces flexibility a bit because it means my import file always has to have the same name... any ideas on this?
"Question about your import script - why sort descending then go to record (first)... why not sort ascending and go to record (last) ?"
Either method works. Use whichever method you prefer.
"I noticed that the Import Records script step doesn't let you specify field-matching options unless you also specify a specific file to import. This reduces flexibility a bit because it means my import file always has to have the same name... any ideas on this?"
There's a way to do this. Set up your import records script step to work from a specific file so that you can correctly map your fields for import. Then, after you are finished insert a variable into the Specify Data Source dialog so that it looks like this:
Now you can use a variety of methods to place a specific filepath into $PathVariable and the fields will still be correctly mapped.
OK thanks for clearing up the sort-order thing.
And re: the import records "Specify Data Source" dialog - I did some reading on this, you can specify more than one path, and it will try them in the order listed. So you're saying that so long as the originally-specified import file remains in the list of paths, the field-matching settings will be retained. Nice trick! It would be nice if Filemaker would make this script step a little better implemented - for example, some way of saying "if the specified file has a table by the same name, try to import all records [incl. switchable options such as Match Field Names]...
as always, I'm enlightened by your expertise Phil - thanks very much
Hmm... just thinking... is there any way in FMP to bring up a File browse type dialog box, to let the user select a file to import from?
What I do is define a container field and use Insert File to pop up a dialog for inserting the file into the container by reference. I can then extract the file path from the container field and put it in a variable that's refrenced in my Import Records script steps.