Tuesday, February 28, 2012

How to set a custom sequential Receipt Number

When I made a Point Of Sale system for a business unit, I was faced with the requirement that the system should be able to produce an unique receipt number. At a glance, i've just wonder to take advantage on AUTO INCREMENT feature as a primary key (in this case I use MySQL database).  But the requirements (demanded by the business owner) is different of course. They asked for something like this:

  • Receipt Number should have the YYYYNNNN format, where YYYY is the year of the transaction, while NNNN is the number of transactions
  • Receipt Number will have to be reset when it comes into the new year of transaction, for example if the last number in 2012 was 20120986, then the system must reset the number at the beginning of the year into 20130001

of course, here,  I can't use the AUTO INCREMENT  feature anymore, also i can't  use the "record count" method  (since i can't guarantee validity of records count)

To overcome this I've created a function to generate receipt number, with a method to find maximum number of recorded receipt number's through SQL query, and then compared the YYYY information on resulted receipt number with the current year. If it found different then the year (YYYY) is converted into current year and the serial number are reset to '0001 '.

If it was the same year, the serial number will be added by 1 then added again with 10000. (so if the sequential is 55, then 55 + 10000 = 10055, but we only have to take four characters (start from 2) as a String according to requirement to produce 0055. Simple right?

Here's the function that I create (in Visual Basic)

    Private Function GetNoBukti() As String
    Dim Skr As String
    Dim RcdAkhir As String
    Dim urut As Integer
    Dim Curryear As Integer
    Dim RS As ADODB.Recordset
    Dim SQL As String
        Set RS = New ADODB.Recordset
        SQL = "SELECT IF(MAX(id_bukti) IS NOT NULL, MAX(id_bukti), '0000') AS rcd FROM penjualan_header"
        RS.CursorLocation = adUseClient
        RS.Open SQL, conn, adOpenDynamic, adLockOptimistic
        RcdAkhir = RS!rcd
        Curryear = Year(Now)
        urut = Val(Mid(RcdAkhir, 5, 4))
        If (Mid(RcdAkhir, 1, 4) < Curryear) Or (RcdAkhir = "0000") Then
            GetNoBukti = Curryear & "0001"
            GetNoBukti = Curryear & Mid(10000 + urut + 1, 2, 4)
        End If
    End Function

As on the SQL string described above, if the record was not found in transaction table (result NULL) then I give the dummy sequent '0000 'to handle a condition where the new system was first installed

What do you think? Do you have a more effective way? Feel free to share here


  1. Quite interesting and nice topic chosen for the post. I would collect more information about this topic.
    Gateway Laptops


Leave your comments with attitude please. This blog does DOFOLLOW :-)