Are gaps in your number series really important or do you just need to know which client was added first in a given year?
If the latter, you can simply combine the year you added the client with the serial number you already have.
Do the numbers start over with each firm or just with the new year?
I'm not sure what you mean by gaps in my number series, could you clarify? Do you mean the following:
2011Firm001, 2011Firm002, 2011Firm003 and then goes to 2012Firm004, 2012Firm005?
I am really hoping to avoid that and I want the serial number after YearFirm### to reset at the beginning of the year to value 001.
I found the following script over at http://www.briandunning.com/cf/387 and it appears to be working
year = Year ( Get ( CurrentDate ) );
lastId =GetNthRecord (AEfield ; Get ( TotalRecordCount ) - 1);
lastYear = Left ( lastId; 4 )
year ≠ lastYear or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit ; digit-1 ) & "1";
SerialIncrement ( Right ( lastId ; digit ) ; 1 )
It comes with the following explanation as well
AEfield: text - the text field wich will hold the serial number; it must be setted as auto-enter, always evaluate
digit: number - the number of digit (#) that must increment by one and restart from one every new year
So far it is working very well, do you see any problems with this calculation? This calculation will never be touched again so I don't see a need to make it a script.
I wonder what you'll get if you do this with a networked solution where two users create a new record at the same time. I wonder if the result might be two records with the same ID.
If you never have more than one user adding these records at any given time, it should work just fine.
What you described, was one possible "gap" but it's easily avoided just by using an auto-entered serial number that is reset to 1 at the first of every year. (and there would be no concerns about duplicate ID's here.)
and I can't help but think the use of flags to simplify the database design could be a good thing here...
Nice value list to use as a flag instead of all this repetitive data taking up database space you don't need...