FileMaker serial number follies

For the past couple of days I’ve been bonking myself on the head repeatedly trying to figure out what would seem to be a simple FileMaker operation. I want to create an alphanumeric serial number that increments as follows:

A1
A2
A3
…
A998
A999
B1
B2
B3

…and so on until it rolls over at Z999 and resets back to A1. Resetting is OK – there’s no need to keep a permanent archive as once these references have passed out of the larger tracking system they just get deleted.

In a sudden flash of post-ice coffee-enhanced thought, I came up with the solution. Make sure that the serial field is set to auto-enter a serial number on commit. Set “Next value” to A1 and “increment by” to 1. Create a “New Record” script that looks like this:

New Record/Request
If [ Int(SerialField) > 998 ]
Set Next Serial Value [ SerialField ;
Let ( [
alphabet="ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;
CurrentLetter = Left( SerialField ; 1 ) ;
CurrentLetterPosition = Position ( alphabet ; CurrentLetter ;
1 ; 1 ) + 1 ] ;
Middle ( alphabet ; CurrentLetterPosition ; 1 ) & 1 )
]
End If
Commit Records/Requests

It’s an obvious solution in retrospect and I can’t believe it had been vexing me for so long.