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

    Custom Serial Generation fails



      Custom Serial Generation fails


           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" )

           ] ;




        • 1. Re: Custom Serial Generation fails

               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

                 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

                   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]


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

                   End If

                   End If

              • 4. Re: Custom Serial Generation fails

                     @ 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

                       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

                         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

                           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.