Calculation help with serial in pk.

[fmow]

My Serial format is as follows YY-XXX, year last two digits, and  a 001-999 serial.

 

I was wrongly under the impression that the following code, that checks to see what year it is, and if it’s the same it increments, if it’s new it starts anew, was correct:

 

If  ( Left ( GetNthRecord(_pkProject_Serial_Number; Get(RecordNumber) -1) ; 2 )  =  Right ( Get(CurrentDate) ; 2) ; SerialIncrement(GetNthRecord(_pkProject_Serial_Number; Get(RecordNumber) -1);1); Right ( Get(CurrentDate) ; 2) & "001")

Sadly is not, after a find request, or in between records.

 

I tried to change it as follows but now I don’t get ANY serials.

 

If  ( Left ( GetNthRecord(_pkProject_Serial_Number; Get(TotalRecordCount) -1) ; 2 )  =  Right ( Get(CurrentDate) ; 2) ; SerialIncrement(GetNthRecord(_pkProject_Serial_Number; Get(TotalRecordCount) -1);1); Right ( Get(CurrentDate) ; 2) & "001")

 

 

I thought about scripting it, but to no avail. ANY help much appreciated. 

[Wim Decorte]

Oy, that is such a bad idea... leave the pk a true serial # field and create a secondary field that just uses that serial and prefixes it with what you need.

 

You are using get(recordnumber) and that one is sort order / found set specific so you have no guarantee that you will be reading from the last created record when you create a new one.

[Lee Smith]

I have deleted your other post on the same question.

 

Please Do NOT multiple post your questions.

 

This Forum is only one list and you don’t need to multiple post your topics in hopes of reaching a different audience. Those of us that answer the majority of the questions read all topics. Posting the same question in different topic areas, only causes the members more work in having to read your multiple posts to ensure that they are not different, and a loss of time that we can devote to the other questions.

 

If you have any questions about this action, please contact me by Private Message.

 

Lee

[fmow]

Sorry Lee, it was completely by accident, I wasn't aware I was resubmitting the form.

 

@Wim.

Sure, good advice. But even so I 'll have to have some code that increments correctly for the other field, the external one.

 

I am aware my code isn't. But can it be fixed one way or the other?

 

(why is the code with get(totalrecordcount) not working at all?)

[comment]

I'm afraid you may be missing the point: auto-entered serial numbers increment reliably. Any other scheme is open to all kinds of gotchas, especially in a multi-user scenario. Therefore it would be best to abandon your current YY-NNN format and use a contiguous serial number that ignores the year (along with anything else). As Wim noted, you can add the year to the serial number for display purposes. You don't even need another field for that, you can simply merge the date and the serial number together on the layout. You would get essentially the same format, except for the yearly restart.

If you absolutely must have the yearly restart, consider having a startup script check the next serial number using the GetNextSerialValue function and if it doesn't match the current year, use the Set Next Serial Value script step to update the year and reset the serial part to "001".

 

 

---

I don't know why your code isn't working.  Going by your description, it should work after a fashion (it would produce duplicates when not all records are found and when two users are creating a new record more or less at the same time).
 

[fmow]

I'm afraid you may be missing the point: auto-entered serial numbers increment reliably. Any other scheme is open to all kinds of gotchas, especially in a multi-user scenario. Therefore it would be best to abandon your current YY-NNN format and use a contiguous serial number that ignores the year (along with anything else). As Wim noted, you can add the year to the serial number for display purposes. You don't even need another field for that, you can simply merge the date and the serial number together on the layout. You would get essentially the same format, except for the yearly restart.

If you absolutely must have the yearly restart, consider having a startup script check the next serial number using the GetNextSerialValue function and if it doesn't match the current year, use the Set Next Serial Value script step to update the year and reset the serial part to "001".

 

 

---

I don't know why your code isn't working.  Going by your description, it should work after a fashion (it would produce duplicates when not all records are found and when two users are creating a new record more or less at the same time).

 

 

Hey comment, I think I ll do what you suggested and use the scipts to replicate, or rather correct the behaviour of my current calculation and then just use an fm created serial for a a pk. 

 

What would you think of using executesql and doing something like this?

Let ( [
maxSer = ExecuteSQL ( "SELECT MAX ( "_pkProject_Serial_Number" ) FROM Projects" ; "" ; "" ) ;
yn = Right ( Get ( CurrentDate ) ; 2 ) ;
ySer = Left ( maxSer ; 2 ) ;
incSer = Right ( maxSer ; 3 ) ;
incNew = Case ( ySer = yn ; SerialIncrement ( incSer ; 1 ) ; "001" )
] ;
yn & "-" & incNew
)

[comment]

What would you think of using executesql and doing something like this?

 

I think it falls in the "any other scheme" category mentioned in my previous post...

[fmow]

would you condone it? Do you use it?

[comment]

No!

 

I don't know how to make it any clearer...

[Lee Smith]

No!

 

I don't know how to make it any clearer...

LOL.

 

Then is s this your final, final answer.

[fmow]

No I wasn't referring to the original question, but to the use of executesql.

[fmow]

I 've taken your advice and did away with the current serials as backbone pks and fks, and use reliably incremented ones via fm.

 

I am trying to get the executesql code above to work so I can get the proper behaviour for the custom serials, and it's not working

 

The culprit is the executesql command which outputs a ? for some reason.

 

For the life of me I can't tell what could be wrong in this simple line of code....

ExecuteSQL ( "SELECT MAX ( ProjectNo ) FROM Projects" ; "" ; "" )