Help with Set Next Serial Value and Max (Field) + 1

[wayneg]

Hi I've been learning FileMaker for the past few months and are currently working with 2 versions of a database.  I have a Deployed database and a Development database.

 

The Development database is based off of the deployed version.  I have been making changes to the Development version's layouts.  The layout and field names in the Development version match all field names in the Deployed version, so I'm here trying to write a script that will import all data over from Deployed into Development.

 

Preparation and thoughts:

1. Delete all records on all tables in Development version.

2. When I import records from Deployed into Development, the next auto-enter serial number for any given Development table may be a lower number than the next auto-enter serial number for the corresponding table from the Deployed database.  Therefore I have created a script step to "Set Next Serial Value ( Table::PrimaryKeyField; Max (Table::PrimaryKeyField ) + 1"

3. The auto-enter serial number is a number.  Field type is a number.  Examples: 1, 2, 3, 5, 10, 15, 200, 315.

 

Sample script step to import a table:

Go to Layout ["Example Layout" ( Example )]

Import Records [ No Dialog; "DeployedDatabase.fmp12"; Add; Windows ANSI]

Show All Records

Set Next Serial Value [ Example::PrimaryKeyField; Max ( Example::PrimaryKeyfield ) + 1 ]

Show Custom Dialog ["Import Complete"; "Import has completed"]

 

Issue:

My Example table has 400 records with some missing in between.  The latest record had the highest primary key serial number of 600; the lowest serial number for a record is 74.  The correct, next auto-enter serial number for a new record should be 601.  The problem I'm experiencing is, the next auto-enter serial number is set to be 75 after the import.  I assume the Max function would get 600+1, but instead I got 74+1.

 

Is there a step I'm missing or something that I'm doing wrong in my script?  I'd appreciate any help.  Thank you!

 

[Ocean West]

You need to create a self join Relationship with cartesian product - "X" and then use Max ( ) 

 

Table ---X----Table,  using any field as match field (preferably the ID field ) then the value is

 

Set Next Serial:  Table::PrimaryKeyField ; ( Max ( TABLE_SELF::PrimaryKeyField) + 1 ) 

 

Aggregate functions wouldn't compute the entire found set, in this case. 

[eos]

Aggregate functions wouldn't compute the entire found set, in this case. 

 

Wouldn't this work?

GetSummary ( sMaxOfPrimaryKeyField ; sMaxOfPrimaryKeyField ) // with a summary field sMaxOfPrimaryKeyField = Maximum of PrimaryKeyField

[Ocean West]

yes but at the expense of adding an extra field only to perform a tasks needed a few times?

 

you could also just show all records sort by ID in descending order and then grab the first value of the ID and then add one.

[ron G]

in your script,

 

Go to Layout ["Example Layout" ( Example )]

Import Records [ No Dialog; "DeployedDatabase.fmp12"; Add; Windows ANSI]

Show All Records

Sort (Example::PrimaryKeyfield) Descending  //Puts PK in descending sequence

Go to Record First                                          //Go to first (highest PK) record

Set Next Serial Value [ Example::PrimaryKeyField;  Example::PrimaryKeyfield  + 1 ]

Show Custom Dialog ["Import Complete"; "Import has completed"]

 

I've had your problem and was told by others in a forum that this code won't work. 

But, after several months, it seems to be working.....

[Wim Decorte]

Instead of sorting and go to first, you can just do an "unsort" and go to last.  Much faster.  Unsort puts the records in creation order so the last one is the most recently used.

[Ocean West]

brilliant as usual.

[Ocean West]

however if you ever upgrade to 12 or 13 (or an sql plugin ) i could recommend this:

 

SetVariable [ $sql  =  Execute SQL ( "SELECT Max ( id ) FROM example" ; "" ; "" ) ]

Set Nest Serial Value: [$sql + 1] 

 

and you don't even have to go to the target layout, all scripted no extra schema needed.

[comment]

I would suggest standardizing on:

...
Go to Record [Last]
Set Next Serial Value [ TargetTable::PrimaryKeyField ; SerialIncrement ( TargetTable::PrimaryKeyField ; 1 ) ]

which would accommodate number and text serials alike.

 

 

Note that we are assuming that the imported data's serials are in creation order, and that the source table is not sorted otherwise when being imported (which could easily happen if the source file is open). If these two assumptions are met, it's not necessary to unsort the imported set. If they are not, then unsorting won't help - on the contrary, you must sort the resulting set before resetting the serial (or even better, delete the imported records and redo the import in the proper way).

[wayneg]

Thanks everyone.  I was able to take your suggestions and set the next serial value.  This will make importing a lot easier in the future.  Thank you!