Script a new "next value" after importing records?

[Philippe]

The situation:

Let's say I have an empty FMP file, and I want to import records (by script) from another FMP file, which is the same, except for the fact that it contains 100 records.

Each record in the file is identified by an ID field, incremented by 1 everytime a new record is created, because of the field: option/auto enter setting. ID number of the 100 records to be imported are between 1 and 150, because 50 records were deleted.

My problem:

How can I import these 100 records while keeping their ID numbers unchanged (unselecting "perform auto enter option while importing"), but at the same time having the ID for the next record to be created in the receiving file to be 151?

In other word, is there a way to set by script a new "next value" in my ID field option/auto enter?

Any help would be welcome!

[John Caballero]

I'm very interested to hear any solutions for this problem as I do data migrations from time to time. The way I do it is to make a note of the next value(s) of the ID field(s) in the source file (Define Field Options) and then set them in the target file (again, Define Field Options) prior to importing. This works fine, but I would love to (reliably) automate this as one client has over 70 files.

[Mike D.]

There is a script step called Set Next Serial Value under Miscellaneous steps. Take a look and see if that would be of use to you.

HTH, Mike

[Fenton]

This step and function was added in 5.5 (I believe, maybe 5.0?).

The step is at the bottom in the Miscellaneous section, and the function is in the Design group.

Use them together:

Set Next Serial Value [ GetNextSerialValue("file name", "serial ID field name") ]

Example:

Set Next Serial Value [ GetNextSerialValue("24Hr_WorkOrders.fp5", "WO#") ]

In this case both values are from another file (the original file, this is part of an "update" routine, where the new file has been renamed manually to a different name.

Since this is in an update file, with NO relationship to the original, both the values are TEXT, hard-coded. So type carefully, and remember that Developer will NOT update these values if you change the file names.

(Actually it's in original AND the update file, since the update file is a clone of the original file. It's for automated updates; a little tricky to set up, but well worth the effort if you do it more than once.)

Here's what FileMaker Help says:

Resets the next serial value in an auto-entry serial number field. Set Next Serial Value allows you to use ScriptMaker to update the next auto-enter serial number value. You can define this script step to use any calculation expression to determine the next serial value of a field that has been defined as an auto-entry serial number field. The calculation always evaluates to a text result. For example, you might want to reset the next serial value after you do one of the following:

import records into a FileMaker Pro database with an auto-entry serial number field

delete multiple serialized numbers from a FileMaker Pro database.

This script step can operate on multiple files. If you specify a field in another file, then FileMaker Pro attempts to update the serial number for the specified field in the other file. To specify a field in another file, define a relationship to that file and use the Specify Field option to select a field from that file.

Note This script step affects the definition of the field you specify instead of the actual contents of the field that you specify.

Options

Specify Field lets you select a field. You must specify an auto-entry serial number field when defining the script.

Click Specify to define the calculation. In the Specify Calculation dialog box, type the calculation you want evaluated, or use the field list (on the left), and the functions list (on the right) with the mathematical and text operators to build the calculation.

Example

The following example calculates the number of the next available invoice ID. If the invoice ID contains non-numeric data, then the calculation would need to be more sophisticated to maintain the numeric and non-numeric data.

Go to Record/Request/Page [Last]

Set Next Serial Value["Invoice ID", "InvoiceID + 1"]

[John Caballero]

Cool - thanks Fenton. I don't rename the files when I'm doing a data migration. I'm guessing that that could be a problem since I will have files with duplicate names - an empty, recently updated version of the file, and the older version of the file that has all the data and none of the updates (after the data is migrated I move the now updated file into the same directory as the old file and overwrite), or is FMP smart enough to get around that?

[Fenton]

You know, I don't know for sure that you could not do it with duplicate names. But I find it kind of freaky, not to mention confusing, so I rename the "New" files; which are basically just clones of the originals.

One trick to working with update files is to switch the relevant files to a blank layout, at least one with NO related fields. Then you don't have to worry so much about files opening "behind your back" (sneaky little guys :-)

I put the "New" files in a "New" folder (highly technical naming technique), just outside the real files' folder. I keep this structure intact, so the files don't lose the path to the original files.

Then it's a fairly simple matter to import from the orignal and store that import order. You then run the routine to reset the serial number, in the New file, to the next in the orginal.

There is one "Prepare to Update" script which I run first. It's in the original file, its only steps are:

Go to layout [ Blank ]

Show All Records.

It's called from the New file. Then the New file closes the original.

Once it's all done, the New file is now the real file, so I lop off the New, and replace the original with it.*

The odd thing is that then the orignal is a latent "new" file, that is, it has all the scripts, including a scripted import of itself, from the "New" folder. You can use it over and over, using the same scripts. This is very convenient if you have to do an update onsite with the client breathing down your neck; it makes you look really efficient and you hardly have to think :-/

It's a little weird to think about (the circular nature of the scripts, perhaps the clients also). I think I better take my evening walk now. I'll have to work up some example files someday soon. I have some, but they're from before version 5, when it was really a nightmare to reset the serial numbers; it's much simpler now.

*Actually there is a more awesome technique, first developed by Eric Scheid, which overwrites the original with the New file, using the Save a Copy As step. This eliminates having to rename the files; but is best practiced at home first. Oops, no more data :-|

[Philippe]

This is a great script step that was unknown to me! The only problem here is that I will finally have to upgrade, since in my v4.0, this script step does not exist yet...

Thanx a lot, anyway!

[Fenton]

As I said, I have a solution which was developed for v.4. But I would say that it is really not worth the effort. The later version is so much simpler, faster and safer.* I would just do v.4 updates manually, unless the situation makes that impossible for remote installations. It is likely that sometime in the future you will have 5.5+, then it will be worth it.

By the way, the only script that has to exist in the files to be updated is the "Prepare to Update" which is just Go To Layout ["Blank"] and Show All Records. For that matter you can do this part manually; a necessary step in any upgrade method.

The "modern" part of the script only needs to exist in the "new" file. So you could convert a v.4 file to 5.5 format and then continue with the update.

*The v.4 method involves doing a Replace, on 1 record at a time, for every single record, to update the "next" serial number. Needless to say, this is not fast for 100,000 records.