Best way to import into an updated solution

[Brudderman]

I have a partially completed solution that the client has been testing and now wants to begin entering real data into. Since I am still in the process of refining both the user interface and the data structure, however, I will eventually have to get the updated data (about 12 tables) out of the older version and into the newer. We will probably go through this process several times, and I'd appreciate suggestions on how to make it as smooth as possible.

I can certainly import records into each table in the updated version, but should I try to import all records (after deleting existing records) or just import and update records? Will I need a script in the "old" file that finds all records in each table before invoking another script in the "new" file to do the import? I'm not to the point to where I can effectively separate data and interface, so what would work smoothly? And should I export backup files out of each table just in case there's an error?

Thanks for any suggestions!

[Ender]

Hey James,

I'd recommend importing all records into an offline clone of the new version. This is the safest way to ensure that modified records are replaced and that the field mapping is correct. You will also need to check your next serial values.

When you do this, move the original files to another location on the drive. These then becomes your backup. Then you're safe to put the updated files in the Data folder.

While some people like to restore the import field mapping and have it all scripted, this could import into the wrong fields if fields are deleted in either the source or destination file.

[Brudderman]

Thanks, Mike! I can do this OK. For the time being, I'm not changing any field names or deleting any fields, so I might try a script to speed things up (after taking making a backup of my file), perhaps with dialog just for a spot check for matching field names.

I will need to make sure that for each table in the source (old) file that all records are found before import, won't I? For a scripted approach, will that have to be a separate script running from within the old file? I guess that I could run it from a script from within the target (new) file, though, once I initiate the import.

Am I off track here? Any further suggestions would be appreciated.

[Ender]

If the source file is closed or has more than one window, all records will be imported. If the source file is open with one window, the found set will be imported.

[Fenton]

Importing from a closed file, as Ender said, is the fastest way; but there is a caveat. The most automated way to do an upgrade/update/restore import is to reset the next serial value to what it was in the old file. This can be done a couple of different ways. The "official" method is to the use the Set Next Serial Value [] step, with the GetNextSerialValue() Design function.

Set Next Serial Value [ serial field; GetNextSerialValue( "old file name"; "TO::serial field" )]

Note that both the file name and field name are text values. TO is "table occurrence".

The caveat is that this only works if "old file name" is open. So you'd need to Import from the closed file, then open the old file (or run an external script), get the next serial, then close it. Or just do the Show All Records in the old file and import from it open.

Or, alternative "non-official" method. Import from the closed file, then go to the last record of that table, and SetNextSerialValue() to the serial + 1 (include text prefixes, if any). This is usually safe; except in the rare case that the last record of the old file had just been deleted, and it had orphaned child records; which shouldn't be allowed anyway. If anyone knows other glitches or enchancements, please chime in.

Edited November 13, 2006 by Guest

[Brudderman]

If the source file is closed or has more than one window, all records will be imported.

I didn't realize this. Thanks!

[Brudderman]

Or, alternative "non-official" method. Import from the closed file, then go to the last record of that table, and SetNextSerialValue() to the serial + 1 (include text prefixes, if any).

If I am set up to update matching records (and add records for new data), can't I set auto-enter on the serial number field automatically do this? Or is that just asking for trouble?

[Oldfogey]

With a capital 'T'!! Whatever you do, don't let FMP change those serial values. If you've deleted a Parent, there is a gap in the serial numbers and, hopefully, that gap is reflected in the child records - there should not be any with that deleted Parent serial. Auto-enter will fill the gap and mess up your child records.

If you script it, watch out. If you edit the Import script step and if the source file is not there, FMP makes a mess of the import.

I think this might be an error on my part but I can't work out how. I had a script which executed a 'Find All' script in the source file before the Import step. For some reason, FMP got upset and said the source file was open and so couldn't be opened. I just removed the 'Find All' script.

Are you sure you won't be changing field names or adding/removing them? If so, and you are still developing the app, can you let us all know your technique? I seem to be forever adding or renaming fields.

[Brudderman]

I don't want the "Big T" at all! :

So I'm convinced that I need to get the serial values set properly and will have a look at SetNextSerialValue. May be back with more questions, though.

Thanks to all who have helped!

[Fenton]

If you've deleted a Parent, there is a gap in the serial numbers and, hopefully, that gap is reflected in the child records - there should not be any with that deleted Parent serial. Auto-enter will fill the gap and mess up your child records.

While this would certainly be a terrible thing, I do not see this happening. If I import a serial ID field, with the [x] Perform auto-enter option, it does not "reserialize" the data; it brings in the serials intact; it does not fill in a gap. What it does do however is to reserialize the "next value" in the auto-enter options of the field (effectively doubling it). So you need, in any case, to reset that next value after an import (unless you've imported into a clone that has that already, which is unlikely).

I'm not saying you'd want to do this during an "update" however. Because there's no reason I can think of to use [x] Perform auto-enter at that time anyway. You've already got all the data in the source file. It's faster and safer without that option.

[Oldfogey]

I stand corrected. Thanks, Fenton.

Sorry,Brudderman. Provided you import Serial to Serial, FMP will not update it even if you flag Auto-enter.