5 Replies Latest reply on Aug 21, 2012 3:03 PM by hopkinsprinting

    Customized Number Format...

    hopkinsprinting

      Title

      Customized Number Format...

      Post

      <!--StartFragment-->I was wondering if it would be possible to have a custom auto-generated number appear in a given field each time a new record is added that looks like this:

      INC2012081509

      INC is short for Incident
      2012 = the current year
      08 = the current month of the year
      15 = the current day of the current month
      09 = the sequential record number for the current day (more than one incident could be added in a given day)

      If this is possible, can someone explain how?  If a script is required, can you show me the code as I am not a scripter?

      Thanks! :-)
      hp 

       

       

       

       

       

       

       

       

       

       

       

       

       

        • 1. Re: Customized Number Format...
          philmodjunk

          I don't recommend that you use this field as a primary key.

          Define this first field, "dateTag" as a text field and give it this auto-enter calculation:

          09 = the sequential record number for the current day (more than one incident could be added in a given day)

          Let ( T = Get ( CurrentDate ) ; "INC" & Year ( T ) & Right ( "0" & Month ( T ) ; 2 ) & Right ( "0" & day ( T ) ; 2 ) )

          Define a self join relationship based on dateTag:

          YourTable::dateTag = YourTable 2::dateTag

          Define DaySerial as a number field.

          Use these auto-enter settings:

          Put 0 in the data box.   //feel free to see if it works without this option, I'm working from memory here
          Enter this auto-enter calculation:

          Max ( YourTable 2::DaySerial ) + 1

          Clear the "do not evaluate if all referenced fields are empty" check box.
          Clear the "do not replace existing values.." check box.

          Define a third field, as text to get your final results:

          dateTag & daySerial

          set a unique values validation rule on this field. (It's possible for two users creating records in this table to get the same DaySerial value for the same date. This validation setting will catch such an event.)

          • 2. Re: Customized Number Format...
            hopkinsprinting

            Hi PhilModJunk,

            Your instructions got me about 95% of the way!

            For some reason the number in the daySerial field is not adding 1 to the existing number.  Not sure what I have done wrong.

            Is there a way to have the number in the daySerial field reset to 0 when a new day starts?

            Thank you so much!
            hp Laughing

            • 3. Re: Customized Number Format...
              philmodjunk

              Check all the auto-enter settings. Leaving even one out will keep this from working.

              Is there a way to have the number in the daySerial field reset to 0 when a new day starts?

              Do you want the first record created on a given day to be numbered 0?

              If so, put -1 in the auto-enter tab's data box.

              I also left out a minor formatting detail:

              Define a third field, as text to get your final results:

              dateTag & daySerial

              should read:

              dateTab & Right ( "0" & daySerial ; 2 )

              to add in a leading zero for records where daySerial is less than 10.

              • 4. Re: Customized Number Format...
                philmodjunk

                I played with a demo file and I had to remove the 0 in the data box option (clear the check box) before it worked. It seemed necessary in the first demo file where I had something similar, but had to remove that option here.

                • 5. Re: Customized Number Format...
                  hopkinsprinting

                  BINGO!  It all appears to be working now!

                  Thank you so much!
                  hp Laughing