Receipt Numbering using Autoincrement

When you are creating invoices or receipts you need use a continuous numbering system in order that you have a proper audit trail of all of the invoices or receipts issued. Possibly the easiest way to do this if you are storing the details in a mySQL database is to use an autoincrement field so that the continuous numbering is taken care of for you. There are a number of issues though that you need to consider in order to maintain the integrity of your data when you do this.

The first concern is with regard to the accidental creation of duplicated receipts with consecutive or near consecutive numbers. One thing you don't want to happen is for someone to reload a web page and accidentally generate a second receipt for the same thing.

The receipting system I set up using autoincrement receipt numbers is processing annual renewals and so there should be a significant difference in the receipt numbers issued for the same renewal in subsequent years. I therefore set up a test to see if the information relating to the renewal matches any of the last five receipts issued before creating a new receipt.This number of past receipts to check is low enough to ensure that there should not be a legitimate match within that number of receipts and that any match found clearly indicates that the entry is a real duplicate. It is also high enough to allow for a small number of receipts being generated from other browsers before the page is reloaded to generate a potential duplicate. The exact number of past receipts that you should test depends mainly on how many people are likely to be trying to create receipts at the same time. With the particular system I wrote the situations where two people are generating receipts at the same time is rare and so checking the last five receipts seemed to be a reasonable number to check. If the volume of updates were higher I'd increase the number of prior invoices to check.

The one situation this still doesn't cover though is where requests for the same receipt are made from two separate browsers at the same time where the check of prior receipts for the second request is made before the first request has inserted its receipt. This is a much rarer situation than the one that the prior test catches but is still one I needed to cater for in order to maintain the integrity of the numbering. While the earlier test actually prevents a duplicate from being created in the first place this situation can only be detected once the duplicates have been created. The integrity of the numbering needs to be maintained and so deleting a duplicate once created is not an option. Instead where this situation occurs and consecutive receipts for the same thing are detected the lower numbered receipt of the two is automatically flagged as void.

Even with these tests in place it is still possible for a receipt to be created that shouldn't have been and so the system also provides a period in which an already created receipt can be updated and marked as void.This process involves a manual update to the receipt and is only allowed up until the receipts have been listed and reconciled after which no further changes are permitted.

The actual processing that you require when you set up an autoincrement receipt or invoice field may differ from the requirements I had with this particular one but you are going to need some sort of processing in place to handle possible duplications.(both accidental and deliberate). Now what I have listed above may sound complicated and you might think that there is an easier way of handling things by not using an autoincrement field.

The problem with using a field that isn't autoincrement for your receipt or invoice numbers is that there is no way you can easily ensure that there a generated receipt or invoice doesn't end up being overwritten by another. The problem is that without the autoincrement you have no way to ensure that each record added gets the next available number. If you look up the maximum number currently stored and add one to it then no matter how short a time there is between the lookup and the write there is the possibility of another record already having been written during that time using the number. The code you'd need to test for that and to then further increment the number for the new record would be more complex than the duplicate code testing needed with an autoincrement. Autoincrement solves the problem of the insertion gap by doing the insert first and then telling you what number it used for the insert instead of you getting the number first and then doing the insert. Also getting the next number first doesn't really do anything to resolve any of the duplication issued - you'd still need all the same checks for duplicate processing regardless of how you generate your receipt or invlice numbers and so generating them using autoincrement at least eliminates some of the possible problems for you.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow