4 Replies Latest reply on Jul 7, 2011 12:43 PM by rjlevesque

    Unique Client ID, how to calculate with new year

    jimmelo

      Title

      Unique Client ID, how to calculate with new year

      Post

      Ok I realize the title isn't the best but here is what I am trying to do.  I wan to create an internal unique ID that I can use to sort clients from 2011, 2012, 2013, etc.  So I want this unique ID to b:

      Year ( Get (CurrentDate ) ) & "Firm" & [+ 1 for each new client]

      in other words the first client will have the unique ID number 2011Firm001

      the second will be 2011Firm002, etc.

      In 2012 I want it to start again at 2012Firm001, etc.

      I also am using a unique client ID number that is just a straight up count from 1 but I have reasons for doing it this way (promotions, etc.).

      Any help would be greatly appreciated.

      Jim

        • 1. Re: Unique Client ID, how to calculate with new year
          philmodjunk

          Are gaps in your number series really important or do you just need to know which client was added first in a given year?

          If the latter, you can simply combine the year you added the client with the serial number you already have.

          Do the numbers start over with each firm or just with the new year?

          • 2. Re: Unique Client ID, how to calculate with new year
            jimmelo

            I'm not sure what you mean by gaps in my number series, could you clarify?  Do you mean the following:
            2011Firm001, 2011Firm002, 2011Firm003 and then goes to 2012Firm004, 2012Firm005?

            I am really hoping to avoid that and I want the serial number after YearFirm### to reset at the beginning of the year to value 001.

            I found the following script over at http://www.briandunning.com/cf/387 and it appears to be working

            Let([
            year = Year ( Get ( CurrentDate ) );
            lastId =GetNthRecord (AEfield ; Get ( TotalRecordCount ) - 1);
            lastYear = Left ( lastId; 4 )  
            ];
            year
            &

            "Firm"

            &

            Case(
            year   ≠   lastYear  or Right ( lastId ; digit ) = 10^digit -1; Right ( 10^digit  ; digit-1 ) & "1";
            SerialIncrement ( Right ( lastId ; digit ) ; 1 )
            )
            )

            It comes with the following explanation as well

            parameters:
            AEfield: text - the text field wich will hold the serial number; it must be setted as auto-enter, always evaluate
            digit: number - the number of digit (#) that must increment by one and restart from one every new year

            So far it is working very well, do you see any problems with this calculation?  This calculation will never be touched again so I don't see a need to make it a script.

            Thanks,

            Jim

            • 3. Re: Unique Client ID, how to calculate with new year
              philmodjunk

              I wonder what you'll get if you do this with a networked solution where two users create a new record at the same time. I wonder if the result might be two records with the same ID.

              If you never have more than one user adding these records at any given time, it should work just fine.

              What you described, was one possible "gap" but it's easily avoided just by using an auto-entered serial number that is reset to 1 at the first of every year. (and there would be no concerns about duplicate ID's here.)

              • 4. Re: Unique Client ID, how to calculate with new year
                rjlevesque

                and I can't help but think the use of flags to simplify the database design could be a good thing here...

                Nice value list to use as a flag instead of all this repetitive data taking up database space you don't need...