auto-increment alpha-numeric field for new records

Discussion created by jzanter on Mar 20, 2017
Latest reply on Mar 31, 2017 by jzanter

Hi All -

Pls point me to a link if this is old hat!


I'm a casual coldfusion developer (all intranet apps) and I now find myself in a new position where we do things with FM.


I am cuting my teeth on a simple DB project. One feature I would like to have is, when a new record is created, for this field, mount number, to be automatically created and be non-editable. The non-editable part I think I see.


The field values consist of one letter followed by three numbers, like k181. We follow the alphabet sequentially and the numeric portion goes from 1 to 999 serially, then the alpha increments and the numbers start over at 1, like k001, k002, ...


How I've done this in the past, is to query the table for the most recent record - can just use max of the serial primary key that's already created - use the mount number from that record, separate the alpha and numeric parts, increment the numeric part, and concatenate (prepending zeroes as necessary). And, if numeric = 999, then increment the letter using the ascii code.


Piece of cake right? I have started to reconstruct this sequence bit by bit but I think this wheel has been invented.