1 Reply Latest reply on Nov 7, 2013 7:16 AM by philmodjunk

    Help with auto calculation of serial

    JohnDee

      Title

      Help with auto calculation of serial

      Post

           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. 

        • 1. Re: Help with auto calculation of serial
          philmodjunk

               This is not a good choice for a serial number to be used as the table's primary key in relationships to other related tables. A simple auto-entered serial number is better for that purpose.

               Use two fields, one for the year and one for the serial number sequence 1-999. Set up a self join that matches records by the year fields. Use the Max function to return the largest serial number value of the record with the same year and add 1 to assign a serial number to a new record.

               Note that most methods used for assigning such a value risk generating duplicate values if more than one user is creating new records at the same time.