Create next empty serial number

December 9, 200817 yr

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

December 9, 200817 yr

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.

December 10, 200817 yr

Author

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.

December 10, 200817 yr

Author

...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, 200817 yr by Guest

December 10, 200817 yr

Please see the Max function.

December 10, 200817 yr

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.