2 Replies Latest reply on May 19, 2015 6:40 AM by philmodjunk

    Auto-enter serial skips id number (deleted record)

    KylerPhilips

      Title

      Auto-enter serial skips id number (deleted record)

      Post

      Hello, 

      How can I prevent the auto-enter serial number on an id field skipping a sequential number when a record is deleted. For instance, I have ID: 1,2,3  - I delete record with ID: 3 the next id generated is 4 and not 3? Hope that makes sense

        • 1. Re: Auto-enter serial skips id number (deleted record)
          SteveMartino

          You really shouldn't.  It will cause big problems, orphaned related records and most likely disaster. 

          I assume you are saying you have records--1,2,3,4,5  If you delete record 2, you want the next created record to be 2, and the one after that to be 6?

          If you have a good reason, and this has come up before, you should leave the auto entered serial number alone, and create a second record id number that isn't used in any relationship for this task

          Some people feel they have to have all the records sequentially numbered, and others have issues where they need an audit trail to show every record created.

          If its just with data entry, you could change the auto enter to "on commit" instead of "on creation", then if you delete a record before its committed, the numbers stay sequential.  This is also usually not recommended, especially in the case where more then one person could be creating records on the same table.

          • 2. Re: Auto-enter serial skips id number (deleted record)
            philmodjunk

            For audit purposes, it is sometimes necessary to create a "gap" free series of values. If this is a system where more than one user can create these records at the same time, your best bet is to never delete a record, but to set a value in a field to mark it as "Void". (additional steps can clear fields to keep numeric values from affecting report totals.)

            It sometimes helps to set up a "new record" layout where all data is entered into global fields. A "save" button on this layout runs a script that creates the new record and transfers the values from the global fields to fields in the newly created record. This allows a user to "cancel" the new record creation process simply by not clicking "save". A "cancel" button can run a script to clear the fields.