Dynamic Document ID's without Maintaining Counters!

dynamicid.jpg
 

Introduction

 

I am sure if any of us who have been dealt with a commercial business application project (Inventory Control, Financial Accounting etc.) must have been challenged with a mechanism to produce "Automatic Document ID" for any of the modules involved. The typical example would be to have "Sales Invoice IDs" to be generated automatically with pattern {yy-nnnnn}, were yy->last two digits of year and nnnnn->00001 incrementing counter.

 

The most typical solution to this issue would be to have a "counter" maintained in a Table or some Configuration file then update it every time a new record has been created. For many situations this could be a way to go. Then comes the time when you are further challenged with how about doing the same thing without having the headache of maintaining the counter!

 

I assume the reader of this article is comfortable with understanding of T-SQL and ADO.NET C# environment.

 

Challenge

 

I was having a conversation with a friend last week and he asked me if I can help him with something like this. Yes you got it right the issue was exactly as mentioned in the title of this article. The guy was looking to generate document id without the trouble of maintaining the counter. His query took me back to 7 years down the memory lane when one of my clients asked me similar feature in one of the Accounting system I did for them.

 

It all started like this: my client started a new business stream and they wanted to generate Invoices for this new department, however the trouble they face was to keep generating the Invoices, even when the Financial year is closed, that means they can still generate the Invoice of last fiscal year!

 

Some thing like this:

 

For year 2006 - Latest Invoice ID:

06-01230

 

Now, if they want to generate Invoice belong to last year then without disturbing the current numbers system must find out last year continuation number and start from there:

 

For year 2005 - Latest Invoice ID:

05-21233

 

This is all they wanted to achieve it without maintaining any counter, however, they did agree to supply information regarding which year Invoice they want 2005 (past) or 2006 (current).

 

Solution

 

The solution I discussed with my friend has helped him as it did helped me in past, I thought why not share this discussion with rest of the community... may be this would help someone in need or just another interesting read, and also I got myself an excuse to write my very first ever article!;)

 

I am dividing the solution in two parts, first part will be the Stored Procedure and second part would be a simple windows forms C# application to demonstrate the technique.

 

Stored Procedure:

 

I am using the "Northwind" database from Sql Server 2000. Please run the following script to create a dummy table called "Invoice", which we will use it to store our dynamically crated document ids.

 

 

if exists (select * from dbo.sysobjects

          where id = object_id(N'[dbo].[Invoice]')

          and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Invoice]

GO

CREATE TABLE [dbo].[Invoice] (

            [Invoice_id] [varchar] (10)

                              COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [Customer_name] [varchar] (50)

                              COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

            [Invoice_amount] [money] NOT NULL

) ON [PRIMARY]

GO

 

Following is the stored procedure code which will take input from user interface and generate new id and store it inside "Invoice" table.

 

 

CREATE Procedure insert_invoice_record

/*

**  Inserts new record in invoice table with dynamically crated id.

**

**  INPUT - PARAMETERS: Year_flag, Customer_Name, Invoice_Amount

**  OUTPUT - PARAMETERS: Newly created Invoice ID

**  MODIFICATION HISTORY:

**  NAME                DATE             MODIFICATION

**  Asif Sayed   27th March, 2006      Original Version

*/

 @customer_name varchar(50),

 @invoice_amount money,

 @year_flag char(1), 

 @new_invoice_id varchar(10) OUTPUT

AS

 SET NOCOUNT ON

 SET DATEFORMAT dmy

 

  DECLARE @err_code integer

 DECLARE @found_error integer

 DECLARE @err_msg  varchar(1000)

 DECLARE @tmp_invoice_id nvarchar(10)

 DECLARE @tmp_date nvarchar(8)

 SET @found_error = 0

 SET @err_code = 0

 -- store current year from date on database server

 SET @tmp_date = (SELECT RIGHT(CAST(year(getdate()) AS nvarchar(4)),2))

   

 -- check for year flag (P-Past, C-Current) to be used

 IF (@year_flag) = 'P'

 BEGIN

   -- if year has zero in front minus 1 year from next digit

   IF (LEFT(@tmp_date, 1)) = '0' 

      SET @tmp_date = '0' + CONVERT(NVARCHAR(2),

                         CONVERT(INTEGER, LEFT(@tmp_date,2)) - 1) + '-'

   ELSE

      SET @tmp_date=CONVERT(NVARCHAR(2),CONVERT(INTEGER, @tmp_date) - 1) + '-'

   END

 ELSE

     SET @tmp_date = @tmp_date + '-'

 

 -- find max of invoice ids counter from current table to be used to crate

 -- new id

 SET @tmp_invoice_id = (SELECT MAX(SUBSTRING(invoice_id, 4, 5) + 1)

                        FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%'))

 

 -- if this is first invoice record then start counter with ....1 else

 -- whatever the most recent counter

 IF @tmp_invoice_id IS NULL

    SET @tmp_invoice_id = '00001'

 ELSE

    SET @tmp_invoice_id = replicate('0',5-LEN(@tmp_invoice_id)) +

        @tmp_invoice_id

 

 -- store new invoice id to output param

 SET @new_invoice_id = @tmp_date+@tmp_invoice_id

 

 -- check if any other user has already utilized the newly acquired

 -- invoice id

 IF EXISTS (SELECT invoice_id

  FROM Invoice

  WHERE UPPER(invoice_id) = UPPER(@new_invoice_id))

 BEGIN

    SET @err_msg = '* Invoice ID: ' + @new_invoice_id +

       ' already exists!, please try saving again!' + CHAR(13)

    SET @found_error = 1

 END

 

  -- if error found skip insert

 IF (@found_error = 1)

  GOTO Exception

 

 -- Insert the record in invoice table with new id

 INSERT INTO Invoice (invoice_id, customer_name, invoice_amount)

        VALUES (@new_invoice_id, @customer_name, @invoice_amount)

 

 -- make a final check to see if any other error happend during process

 SET @err_code = @@ERROR

 IF (@err_code <> 0)

 BEGIN

   SET @err_msg = 'Error ' + CONVERT(VARCHAR(20), @err_code) 

       + ' occurred while Generating Invoice Record'

   GOTO exception

 END

 

 RETURN 0

 

 exception:

    RaisError ('Creating Invoice: %s',  16, 1, @err_msg)

    RETURN -1

GO

 

Following code can be used to test the stored procedure using SQL Enterprise Manager:

 

 

DECLARE @new_invoice_id varchar(10)

 

-- following will create invoice for past year

EXEC insert_invoice_record 'test customer', 12.22, 'P',@new_invoice_id OUTPUT

PRINT @new_invoice_id

 

-- following will create invoice for current year

EXEC insert_invoice_record 'test customer', 12.22, 'C',@new_invoice_id OUTPUT

PRINT @new_invoice_id

 

How it works!

 

If you look at the stored procedure code carefully, it does following to get to new invoice code:

 

  1. Identify if Invoice belong to past (05) or current year (06)
  2. Looks for Max number available after "yy-" pattern.
  3. If MAX returns NULL that means it is first entry, hence counter becomes "00001" else it takes the Max number and fills leading "0s" based on counter (in this case 5) length minus length of Max number.

So this way every time a entry is made to Invoice table it will get the most recent counter based on last entered row, that means even if someone physically delete a earlier row from table, it will not affect and will always give most recent invoice id with latest counter.

 

How about more patterns?

 

Sure! Following pattern talks about a different approach... to get to "mmyy-nnnnn", following changes are required:

 

 

SET @tmp_date = (SELECT (CASE WHEN LEN(MONTH(getdate())) = 1

                         THEN '0' + CAST(MONTH(getdate()) AS nvarchar(2))

                         ELSE CAST(MONTH(getdate()) AS nvarchar(2)) END)

                     + RIGHT(CAST(year(getdate()) AS nvarchar(4)),2)) + '-'

 

SET @tmp_invoice_id = (SELECT MAX(SUBSTRING(invoice_id, 6, 5) + 1)

                       FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%'))

 

IF @tmp_invoice_id IS NULL

    SET @tmp_invoice_id = '00001'

ELSE

BEGIN

    IF @tmp_invoice_id = '100000'

    BEGIN

        SET @err_msg = 'Only 99999 unique Invoice can be generated for a ' +
                       'given Month!'

        GOTO exception

    END

    ELSE

        SET @tmp_invoice_id = replicate('0',5-LEN(@tmp_invoice_id)) +
                                                             @tmp_invoice_id

    END
END

 

So, as you can see possibilities are endless, we can change the logic as per the requirement and we don't have to worry about taking care of the counter anymore for any of the desired logic of getting a new id!

 

The other uncalled for usage could be, by just looking at the last Invoice number one can see that "these many Invoices generated for that particular month".

 

Let's see through the eyes of ADO.NET

 

Please download the attached code and open it using Visual Studio 2003, make sure you change connection string properly and run the code to see the result for your self. This windows form app was quickly put together to demonstrate the usage of stored proc, it is in no way a quality work for example to check the numeric value in Invoice Amount textbox, so I assume you will be a "good user" to enter numeric values in that data entry control :)

 

Code behind save button looks like this:

 

 

//declare connection string

string cnString = @"Data Source=(local);Initial Catalog=northwind;" +

                   "User Id=northwind;Password=northwind";

 

//use following if you use standard security

//string cnString = @"Data Source=(local);Initial Catalog=northwind;

//                    Integrated Security=SSPI";

 

//declare connection and command

SqlConnection saveCnn = new SqlConnection(cnString);

SqlCommand saveCmd = new SqlCommand("insert_invoice_record", saveCnn);

 

try

{

    //open connection

    saveCnn.Open();

 

    //configure command

    saveCmd.CommandTimeout = 90;

    saveCmd.CommandType = CommandType.StoredProcedure;

 

    //create parameters and add it to command object

    //parameter for customer name

    SqlParameter parCustomerName = new SqlParameter("@customer_name",

         SqlDbType.VarChar, 50);

    parCustomerName.Value = txtCustomerName.Text.Trim();

 

    //parameter for invoice amount

    SqlParameter parInvoiceAmount = new SqlParameter("@invoice_amount",

         SqlDbType.Money);

    parInvoiceAmount.Value = Convert.ToDouble(txtInvoiceAmount.Text.Trim());

 

    //parameter for last year flag

    SqlParameter parYearFlag = new SqlParameter("@year_flag", SqlDbType.Char,

         1);

    if (chkLastYear.Checked == true)

        parYearFlag.Value = "P";

    else

        parYearFlag.Value = "C";

 

    //parameter to get invoice id as output

    SqlParameter parInvoiceID = new SqlParameter("@new_invoice_id",

        SqlDbType.VarChar, 10);

    parInvoiceID.Value = "-1";

    parInvoiceID.Direction = ParameterDirection.Output;

 

    saveCmd.Parameters.Add(parCustomerName);

    saveCmd.Parameters.Add(parInvoiceAmount);

    saveCmd.Parameters.Add(parYearFlag);

    saveCmd.Parameters.Add(parInvoiceID);

 

    //execute command to create invoice

    saveCmd.ExecuteNonQuery();

 

    //get new id and display in invoice id textbox

    txtInvoiceID.Text = parInvoiceID.Value.ToString();

 

    //close the connection

    saveCnn.Close();

 

    MessageBox.Show("Invoice Record created with ID: " + txtInvoiceID.Text);

}

catch (Exception ex)

{

    //display error message in case something goes wrong

    MessageBox.Show("Following Error found while creating Invoice Record: " +

                    ex.Message);

}

finally

{

    //check to see if conneciton is still open the close it

    if(saveCnn.State == ConnectionState.Open)

    {

        saveCnn.Close();

    }

}

 

How good is this solution?

 

Well, as it goes with any solution which we arrive at, it has its own share of pros and cons.  The only potential issue which I can see is a very "rare" chance of getting the same id generated for more then one user, in other words, Concurrency!  I did some testing on this by generating ids 1/10 of every second with five concurrent processes, but was not able to generate a single instance of duplicate.  I would encourage any of you if you get a chance do give this code a shot to see if you can produce the duplicate scenario.  The remedy in case duplicate happens, if you notice in code final stage the stored procedure do check for it and ask the user to try saving the record again.  One can also put an additional check there to see if duplicate happened the without bothering the user try one more attempt at getting the id and use it to save in table.  Lastly, the proposed solution is for a typical business application which will be running on desktop client server environment, I am not even remotely considering that this code will be used to generate ids in an environment where a million plus hits are happening every other second.

 

About... blah blah

 

This is my first attempt to post an Article; hope I did justice to it. I have always taken it on chin when it comes to constructive criticism. So, if you feel you like to pass on any comment, please do so I would love to hear it. If you like to praise my work then don't be shy, I like praising too;)

 

Disclaimer: Just like to say this... don't held me liable if you use what we discussed here and it results in LOSS of any form or shape, and if you got PROFIT then lest share it ;) who doesn't like profit anyway .... :) just kidding.

 

I originally posted this article on codeproject.com, posting here again so that more folks can have a look at it and share their say with me.