6 Replies Latest reply on Feb 17, 2016 4:47 AM by coherentkris

    Correct Auto-enter Serial numbers...


      How do I correct Auto-enter Serial numbers when I delete records?


      For instance, if I create a new invoice, it automatically has a new number e.g. 0234.  If I delete that invoice and then create a new invoice, it now has 0235 but I want it to have 0234 because I deleted 0234.

        • 1. Re: Correct Auto-enter Serial numbers...

          There is a function called "setnextserialvalue"

          Be careful though, if you already have related values against that serial number, e.g. line items, and if they haven't been deleted then they will be connected to your next invoice.

          I prefer to use a separate stored invoice number than the serial value.  That way I have better control over the serial number and there's less likelihood of bugs creeping into the system caused by manipulating a key that is used in relationships.

          • 2. Re: Correct Auto-enter Serial numbers...

            You probably should not use auto-enter serials for this, but rather have a scripted mechanism that assigns the invoice # ONLY if you change its status to "ready to go" (or whatever status makes sense).

            • 3. Re: Correct Auto-enter Serial numbers...

              I don't auto-correct. I just VOID an invoice (or other object/type) that is sequential. In many accounting systems this may be the standard. Keep minimal information:

              ID (as is - not necessarily the invoice #, but is unique)


              Date of transaction/record (so you know when)

              any field required: N/A or VOID or 0 or something to not affect calculations elsewhere.

              The blank out other fields, so you know that the sequential transaction is valid, but also voided.



              • 4. Re: Correct Auto-enter Serial numbers...

                The best choice depends on your database being shared on a server and used by n users, or being run by only one user. What is your setup ?

                • 5. Re: Correct Auto-enter Serial numbers...

                  At the moment it's just for me, but I intend on expanding the user base to my partner in the near future.


                  I think I will have a separate field for invoice number and one for serial number.

                  • 6. Re: Correct Auto-enter Serial numbers...

                    You should ask yourself whats the function/significance of the invoice number and why is an out of sequence number unacceptable?

                    If all you need is to have a UIN for an invoice then the sequence is needs only be unique.


                    If you need an unbroken sequence then i would heed Wim's advice for a scripted approach and Bev's advice about voiding but not deleting invoices.