Serial Number calculation tweak needed

[Rich S]

Howdy, all:

I found this gem of a serial number calculation (below) on this site--and I wish I could recall who posted it so I could give him his public due--but when I run it as is, it injects a 7 in the thousands' place, e.g., 200707011, 200707012, 200707013, etc.

Being FM-declined, I tried substituting some of its values but couldn't get rid of that 7 to replace it with a zero. What would be the fix for the calc?

As always, TIAFYH.

Let([

year = Year ( Get ( CurrentDate ) );

lastId = GetNthRecord (id ; Get ( TotalRecordCount ) - 1);

lastYear = Left ( lastId; 4 )

];

year

&

Case(

year ≠ lastYear or Right ( lastId ; 5 ) = 99999; Right ( 100000 ; 4 ) & "1";

SerialIncrement ( Right ( lastId ; 5 ) ; 1 )

)

)

[comment]

My guess is the 7 is coming from your input into the lastId parameter.

You should be aware that calculated serials are not 100% reliable. For example, if two users are creating a new record at the same time, they could very well end up with the same serial number for both. Best to stay with auto-entered serial numbers, IMHO.

[Rich S]

Thanks for the tip, Comment--and I've read that caution elsewhere--but in the solution I'm working on the serial number, and year prefix, need to auto-reset at the turn of the year; I couldn't see how FM could natively do that using its Autoupdate applet...

...unless it can?

[comment]

The best way, I think, is to talk them out of it. Because who cares about serial numbers? The year prefix can be easily handled by a calculation or even by simply merging the date and serial fields on a layout. The rest of the string is not intended for human consumption anyway, so let the humans stay out of what doesn't concern them.

But if I HAD to do it, I would look for a way to run a script once a year to reset the serial. At least that way the danger of any mishaps would be confined to the first business day in a calendar year.

[Raybaudi]

...but when I run it as is, it injects a 7 in the thousands' place...

Hi

the calculation is correct...

Only you have to be sure to unflag the box: "Do not evaluate if all referenced fields are empty."

BTW: the comment's advise is pretty correct

[Rich S]

There's no smiley for "D'oh!", is there?

(...and MANY thanks for your calc, Ray--it finally clicked that you're the author of it.)

...and thanks again for the advice, Comment; I'll broach my client again about the serial numbers...though he who wields the pen that writes the paycheck usually wins. :S