6 Replies Latest reply on Jun 5, 2013 2:08 PM by philmodjunk

    Auto Enter Serial ID With Current Date Mod



      Auto Enter Serial ID With Current Date Mod


           I've done some searching and have yet to find any information if this is possible....

           Can I add a Get(CurrentYear) Function to the auto enter serial - on creation - next value area?

           So instead of an ID# being generated as:  ORD-0001  with next increment 1  ( ID# : ORD-0001,  ORD-0002.... )

           Have it as such :    ORD- (Get(CurrentYear)) & "-"  with next increment 1

           Which would look like ( ID#: ORD: ORD-2013-1,    ORD-2013-2.....)


           I know I could just manually change the value area from "ORD-2013  to  ORD-2014" but I was hoping this could be done automatically so that if I create a locked kiosk solution I can restrict user access to the ID fields.


        • 1. Re: Auto Enter Serial ID With Current Date Mod

               Couple of thoughts:

               1. You should restrict access to the ID in all cases, not just kiosk mode.  SerialID is autoenter and never touched again.

               2. If you want it incremented that way, you should have two fields, the serial ID that you never touch, and another calc field that has your formula above.

               It would be better to use your serial increment as your linkage field and have another field trapping the creation date.  You can then go back an look at things later and get you information without messing with the key fields.  Tinkering with key fields is a weakness in any system...they should be the hidden link in the background that no one ever looks at.

          • 2. Re: Auto Enter Serial ID With Current Date Mod

                 Thanks Ninja

                 that is what I thought.  Are you saying that if I want people to search for Invoices by the invoice number I should have another field that is a calculation that adds the year and the "hidden" auto serial.

                 I've just been using the auto enter serial as the Invoice / Order number etc.

            • 3. Re: Auto Enter Serial ID With Current Date Mod

                   You can do it either way...

                   The "best practice" is to leave the ID fields being used for relational keys alone...touching them risks damaging the function of your dbase.

                   If you want a second field that also increments, set up a second auto-serial field (InvoiceNumberOnly)

                   Then use either an autoenter calculation (not sure if this will time right) into another field for InvoiceNumber which is Get(CurrentYear) & "- ORD -" & Table::InvoiceNumberOnly

                   That way you have the cocatenated field you want, and you can use an autoenter serial number...but without messing with your key fields.

                   If this doesn't time out right (ie the serial isn't populated yet thus you cant use it in a calc), you could set this field via script trigger at some time after record creation instead... using the same calculation (result = text).


                   Hmmmm.....yeah, I reread your post and I could have just said "yes" ;)

              • 4. Re: Auto Enter Serial ID With Current Date Mod

                     But note that with a "do not touch" serial number the sequence will not restart with each new year, it will keep incrementing with each new record regardless of the year.

                     Never the less, I agree with Ninja that such a serial number is what you should use for the primary key field that uniquely identifies each record and is used as a match field in relationships to other tables.

                     If you need this identifier for other purposes--such as to satisfy specific user requirements, you can set up a separate serial ID field that is part of a calculation that produces this value that you can reset to 1 at the start of each new year--but don't use it as a match field in relationships. Just keep this field in the parent record so that it can be used where needed.

                     The actual primary key field need not be visible on any layout to which the average user has access.

                • 5. Re: Auto Enter Serial ID With Current Date Mod

                       Is it possible to make a "serial number"  not a primary key serial,   reset to 0000  automatically when the Current Date turns Jan 1 ?  Unless this is what you meant Phil by a calculation.

                  • 6. Re: Auto Enter Serial ID With Current Date Mod

                         There's a script step that can reset the serial number field's next serial value setting to a new value. This can be done by script or manually through Manage | Database | fields | Field Options