2 Replies Latest reply on Jun 29, 2012 6:19 AM by philmodjunk

    Custom Calculation assistance - newbie



      Custom Calculation assistance - newbie


      I'm trying to create a custom calculation for generating an autofill customer number.

      What I'm looking for is C + year(- first 2 digits) + (6 digit incremental + 1)

      so customer number would look like - C12000001 - yes I know I should use a 4 digit year, but I'm trying to match something that already exists.


      Any help would be appreciated.





        • 1. Re: Custom Calculation assistance - newbie

          You could create a cartesian self-join for the table.  Then set the Customer Number to auto enter by calculation:

          "-C" & Right ( Year ( Get ( CurrentDate ) ) ; 2 ) & ( Max ( Right ( SelfRelationship::CustomerNumber ; 6 ) ) + 1 )

          • 2. Re: Custom Calculation assistance - newbie

            Warning: In a shared environment where more than one user is creating new customer numbers, this method can generate duplicate customer numbers as it is possible for both users get the same max value.

            1) Do not use the above customer number as a primary key in your relationships. You need it for legacy purposes, but keep it as just a data field you can put on reports and use in finds/searches etc. Use an auto-entered serial number as your primary key.

            2) Use an auto-entered serial number on a text field for your "(6 digit incremental + 1"). You can define this with the leading zeros included in the "next serial value" setting for the field. With the serial number field option, you do not risk duplicates.

            This can give you: "C" & Right ( Year ( DateCreatedField ) ; 2 ) & SerialNumberTextField

            If, as I suspect, you want to start at 1 for each new year, either manually or use a script to update the next serial value at the start of each new year.