4 Replies Latest reply on Sep 12, 2014 9:09 AM by philmodjunk

    Find missing serial numbers

    gavjuan

      Title

      Find missing serial numbers

      Post

      I discovered today that there are invoice numbers missing in our accounting DB. The number is set to auto-enter Serial, Unique. Looking back over the past 6 months I manually found 6 numbers missing. Only 2 people have access to the system and both of us are sure nothing has been deleted. Is there a simple formula to search for theses numbers? There are c. 4,000 records, manual seach would be laborious.

       

        • 1. Re: Find missing serial numbers
          philmodjunk

          You could set up a script that shows all records and sorts them by this field. It could then loop through the record and either stop each time a "gap" in the sequence is located or it could log that value in a list in a variable or global field.

          • 2. Re: Find missing serial numbers
            philmodjunk

            What we do here is assign an invoice number only when the transaction is completed by printing the invoice. A different auto-entered serial number functions as the primary key. I then set up access permissions that prevent users from deleting records that have been printed (have an invoice number). They can "mark" one Void, but they can't delete and that helps ensure a gap free sequence of invoice numbers that keep auditors and bosses happy.

            • 3. Re: Find missing serial numbers
              gavjuan

              Thank you for the advice - I will prevent deletion of records (although we are both adamant we never have) for the future but keep the auto-enter on creating NEW records as it suits us that way. Looking at a script to sort and look for gaps as you suggest.

              • 4. Re: Find missing serial numbers
                philmodjunk

                There are a number of ways to accidentally delete a record. Investigating that can be a challenge. A common issue to look out for is what you do when you start a new record and then need to cancel the transaction. That might be where you get a "gap" in your sequence. But you can also set up delete options in Manage | Database | Relationships such that when you delete a record in table 1, all records in table 2 that are linked to it are also deleted. And this can even be a "chain reaction" sort of thing where multiple records delete from multiple tables all by deleting a single record.

                And ctrl-E, the windows short cut for delete record is almost the same as Shift E, what you might use data entry. There should be a warning dialog that you are about to delete the record, but if you are in a hurry...