Custom Serial Generation

[fmow]

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

)

[Charity]

I have ready many reports and posts that this is not a good approach.  Why not use a regular serial provided by fm using auto-enter serial.  Then you can use a calculation which groups the year with the serial as you need it but otherwise you can get duplicate records using these kinds of approaches.

[fmow]

Sure, but I am only using it as a convention for the company and the user, not as an internal serial. I took hid of what the others told me here too, and changed my interal serials to autogenerated fm incremental serials.

[Fitch]

If your field name has a space (Project No) or other illegal characters, you have to quote it.

[fmow]

ok, thanks. That's probably that then. I 'll try it out, so I use " " right?

[LaRetta]

 

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

)

 

 

This doesn't seem right ...

 

IncrementNew = Case ( YearSerial = Year ; SerialIncrement ( NumeralSerial ; 1 ) ; "001" )

] ;

 

MaxSer

)

 

Shouldn't your calculation (the portion in red) be IncrementNew instead of the MaxSer?

[comment]

Sure, but I am only using it as a convention for the company and the user, not as an internal serial. I took hid of what the others told me here too, and changed my interal serials to autogenerated fm incremental serials.

 

That's a funny remark: is this "convention" important? If yes, isn't it important enough to prevent it from generating duplicates? If not, why spend so much effort on it?

 

---

P.S. "hid" = "heed"?

[fmow]

I took heed when I wrote hid, thought about the speller not helping me out there, and rolled a dice to get on the wrong side of the 50/50 chance!

 

Interesting comment. I am keeping it because it's used a reference to the jobs, if a company is used in their way of doing things, I guess I should nudge them but not get them to do a 360 on something that I think I can make work with a little work. 

[comment]

There's no doubt you can make it work. The question is: can you make it work reliably? I don't believe you can, at least not going the way you are going now.

 

See also:

http://fmforums.com/forum/topic/90112-calculation-help-with-serial-in-pk/#entry413582

 

---

P.S. "360" = "180"?

 

 

 

 

 

-------------------------------------

 

Wait a minute: YOU were the OP on that thread. Why are you repeating the whole thing all over again?!

Isn't saying "NO!" once enough for you?

[fmow]

I am not using them for table relations though, I am using them as a user aid solely. 

[comment]

I am using them as a user aid solely.

 

Go back to post #7.

End Loop