3 Replies Latest reply on Apr 24, 2015 4:29 PM by philmodjunk

    Renumber serial numbers

    GaryLewis

      Title

      Renumber serial numbers

      Post

      I have created a database that auto assigns a serial number each time a new account is entered. I imported records from an older database, and it resulted in a gap in account numbers - they go from 1 - 224, and then start again at 500. Currently there are well over 1000 accounts in the database. I would like to write a script that renumbers all the accounts, starting with 1 and eliminating the gap, but my attempts thus far haven't been successful. Can anyone help me with a script to sequentially renumber the existing auto-entered account numbers?

        • 1. Re: Renumber serial numbers
          philmodjunk

          Pull up all your numbers in a found set. Use Replace Field Contents with its serial number option and it can renumber the entire set, but:

          WARNING:

          If this account number field is used to link your account records to records in other tables, such renumbering will pretty much make a hash out of your database by screwing up the links to your related records data.

          • 2. Re: Renumber serial numbers
            RickWhitelaw

            Since auto entered serial numbers, if they are used as match fields, should be meaningless. The fact that you're even able to see that there are gaps in the numbering indicates to me that something's amiss. The fact that there are gaps should mean nothing.

            • 3. Re: Renumber serial numbers
              philmodjunk

              That would depend on how Gary Lewis is using these numbers. For auditing purposes, it's often necessary to show some kind of ID on documents and often the presence of a "gap" creates issues with the auditors. They might not even be match fields used in a relationship though by the description, it would seem that they very likely are used to match to related records--such as transactions logged against each account.