AnsweredAssumed Answered

Custom Serial Generation fails

Question asked by JohnDee on Dec 11, 2013
Latest reply on Dec 11, 2013 by philmodjunk

Title

Custom Serial Generation fails

Post

     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" )

     ] ;

      

     MaxSer

     )

Outcomes