I am using FM 4.1 and I have a ID field defined as an auto-enter serial.

This ID is supposed to be assigned automatically and users don't see it until the record is submitted properly and a confirmation message is loaded. (i.e. The record with ID "x" has been sucessfully created.) This field is always set to a value of "0" in a hidden field and when the form is submitted, FileMaker re-assigns it a value based on the "next value" auto-enter serial value it finds.

The problem I am having is this: when a new record is submitted using a form and there is an error this serial number increments even though a new record was not actually created. When the form is finally completed properly, I end up with gaps in the numbering of my records.

I tried changing the ID field to be a calculation similar to the following (instead of a auto-enter serial):

ID = max(ID) + 1

but FM 4.1 won't let me do this because it is a "circular definition".

Any suggestions?

[This message has been edited by chandra (edited August 07, 2000).]

The serial number can be set as part of your new records script. Create a field "Constant" defined as a calculation =1. Call the database file User. Create a self join relationship with Constant matching Constant called UserByConstant. In your script perform a SetField step with Serial=Max(UserByConstant::Serial) + 1. If you try an auto enter with the relationship, it will give you the same error, you must use a script step. -bd

I created a new field in the existing database called "Constant" as a calculation = 1.

I defined a relationship called UserByConstant in which I related the database to itself using Constant = ::Constant.

I created a script with a New Record Request and the script step, Set Field (Serial, Serial=Max(UserByConstant::Serial).

When I run the script the result is "0" in the Serial field. Is there something obvious that I missed or did wrong?

Nevermind, I figured out the problem. Yay!

In my script, I should have had the Set Field step defined as follows, instead of what I have posted in my post above.

Set Field (Serial, Max(UserByConstant::Serial) +1

Darn those little details!

Thanks for your help!

[This message has been edited by chandra (edited August 09, 2000).]

Chandra,

I was curious as to how your database is coming along? I am making a similar database (calculation wise). In testing it, I have found that after 100 records or so, the database gets very slow in calculating the next serial number. I was wondering if you had created records this high and how your database is responding. Any answers would be appreciated. Thank you.

-Joe Privitelli

[email protected]