Create next empty serial number

[elipsett]

I'm moving up from FMP6 to FMP8.5, and am rewriting a lot of old code to take advantage of new functions.

If I have a stack of records with ID numbers 1-6,8,9,10, and I want to create a new record with an unused ID number, it is trivial to just keep an incremental ID and add one with every new record. However, I want it to see which numbers are unused, so if I create a new record it assigns ID number 7 in this case.

Any suggestions or links? (Yes, I have been searching though these fora but haven't found an answer yet.)

Thanks for your time

[comment]

I'd suggest you leave the unused numbers unused, and just keep incrementing from the highest used number up. If, for some reason, it is important to have consecutive numbering, you could renumber your old records (this requires a carefully thought out process, esp. if the numbers are used for relationships).

Trying to assign unused numbers to newly created records is a recipe for disaster, IMHO.

[elipsett]

OK, advice taken.

I have a method of doing it in FMP6, but it is remarkably clunky. Hopefully somebody will have an idea. I'd prefer not to give up on the idea, but the method I have now is just too much of a kludge.

[elipsett]

...hmm. OK, same problem, different aspect.

Assuming I want to create a new record with a new highest number, but the records are not in sequential order. For example, the last record in the database could be No. 6, but there could be a No. 8 in the same database.

Is there a trivial way to determine what the highest value for a field is within a given database?

Edited December 10, 2008 by Guest

[IdealData]

Please see the Max function.

[comment]

I strongly suggest you use Filemaker's built-in serial number generator. I don't know of any other reliable method to generate unique record identifiers.

See also:

http://fmforums.com/forum/showpost.php?post/258882/

http://fmforums.com/forum/showpost.php?post/236719/

[elipsett]

Thanks to yuo all for your input. Let me go back and take a look at what is needed to get the job done, and see if auto-generate can do it.

I guess this thread's closed now.