Custom Serial Generation fails
I am having a really hard time generating the serial YY-XXX, yy:year two digits, XXX: three digit serial to increment. I need it to check the current year and if the last serial is last years, to start over (eg. the last serial being 13-067 in Jan. 1 will lead the next serial to be 14-001). Otherwise to simply increment from the highest XXX by one.
Upon the suggestion of another developer I am using executesql for this since fm’s context awareness in the find command renders this unworkable. Having said this I fail to see how fm doesn’t have a way to output this pretty rudimentary serial without resorting to executesql.
The script outputs a ? after the executesql command and thus fails to work. (I doubt this is the problem, but I am using a non latin alphabet for field names, that seem to work fine everywhere)
Anyway, here’s the code:
Let ( [
maxSer = ExecuteSQL ( "SELECT MAX ( Project No ) FROM Projects ; "" ; "" ) ;
Year = Right ( Get ( CurrentDate ) ; 2 ) ;
YearSerial = Left ( maxSer ; 2 ) ;
NumeralSerial = Right ( maxSer ; 3 ) ;
IncrementNew = Case ( YearSerial = Year ; SerialIncrement ( NumeralSerial ; 1 ) ; "001" )