Max Serial Number + 1

[longncsu]

Currently every time I create a record, I have a serial number increment by 1.

What I want is every time I create a record, the serial number finds the max. value and increments that by 1. Also, I don't want the serial number to be modifiable. I know I could use the "Set Next Serial" script, but that only works on 5.5+ (correct me if I am wrong) and I can't use a script for what I am doing. I know you can do this in Access. Help?

[LiveOak]

Create a record number field:

RecordID (number)

A self relationship (call it SelfByConstant) based upon:

Constant (calculation, stored, indexed) = 1

Then create a calculation field:

NextID (calculation, number) = Max(SelfByConstant::RecordID) + 1

Then define the RecordID as a lookup of NextID based upon the SelfByConstant relationship and prohibit the modification of RecordID.

-bd

[Ugo DI LUCA]

Sorry to "walk over your toes" Brent...

Be aware that this calc works half-way...

I've also used it for a long time to categorize items before I discovered that, along with a manageable "sort order" issue, it could break depending on the number of records that would be entered, and as soon as you delete one record.

Before you run into a "possible" mess, you'd better check this thread, where Ray offers, as usual, a clever explanation and an alternative (which would however depend on the existence of a next record_id).

May I say it ? Thanks again Ray.