7 Replies Latest reply on Dec 11, 2013 12:15 PM by philmodjunk

    Custom Serial Generation fails

    JohnDee

      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

           )

        • 1. Re: Custom Serial Generation fails
          philmodjunk

               First, I strongly recommend that you not use this serial number as a primary key to link tables in relationships. Use an auto-entered serial number for that purpose.

               You can use a self join relationship that matches records by a year field to get such a sequence that restarts at 1 for each new year.

               You can define a calculation field, cYear to return Year ( DateField ) as a number. Then set up this relationship:

               YourTable::cYear = YourTable|SameYear::cYear

               Then this script step can assign a sequence starting at 1 for each new year:

               Set Field [Sequence ; Max ( YourTable|SameYear::Sequence ) + 1 ]

               But this is simply another way of getting the same results as using ExecuteSQL so if it is working for you, I see no reason to change that approach.

          • 2. Re: Custom Serial Generation fails
            philmodjunk

                 And I forgot the final step:

                 Set up this calculation to get your two digit year, hyphen, sequence with leading zeroes identifier:

                 Right ( cYear ; 2 ) & "-" & Right ( "000" & Sequence ; 3 )

            • 3. Re: Custom Serial Generation fails
              MaartenLeemans

                   I got it figured out, genius me ;-) I think I replace the overal serial (for every Invoice), with this script as it does not 'skip' numbers if I delete a record

                     
              •           Facturen_sj = self joined from Facturen
              •      
              •           Creditnotanummer = my new made serial
              •      
              •           jaar = last two digits from this year

                    

                   If [condition what fields need a serial]

                   If[ Left ( Max ( Facturen_sj::Creditnotanummer ) ; 2 )  = Facturen::jaar ] 

              // So if there is already a record this year //

                   Field Set [ Facturen::Creditnotanummer; Max ( Facturen_sj::Creditnotanummer) + 1]

                   Else

                   Field Set [ Facturen::Creditnotanummer; Facturen::jaar & Right ("001" ; 3)]

                   End If

                   End If

              • 4. Re: Custom Serial Generation fails
                JohnDee

                     @ Maarten

                      

                     I don't know why I didn't use the max function before. Isn't it not find set, ie. context aware. If so it's not going to work.

                • 5. Re: Custom Serial Generation fails
                  philmodjunk

                       Max is an aggregate function like min, average, sum, count...

                       When used with this syntax: Max ( RelatedTable::Field )

                       it returns the max value found in Field for the set of related records. The key is a relationship that only matches to the correct set of related records, in your case, the records from the same year.

                  • 6. Re: Custom Serial Generation fails
                    JohnDee

                         Oh, I see. Wonderful. Many thanks Phil and Martin, this is a very elegant way of going about it.

                    • 7. Re: Custom Serial Generation fails
                      philmodjunk

                           Caution: It is possible to get duplicate values if two different users add a new record at the same time. To guard against that set up

                           Right ( cYear ; 2 ) & "-" & Right ( "000" & Sequence ; 3 )

                           as an auto-entered calculation and select the Unique Values validation field option for it.