locked
incrementing an invoice number RRS feed

  • Question

  • Hi,

    what is the best way to increase an invoice number?

    Now I use in the inserted event the invoice id for the number, but I want to start the number every year with zero ( eg. 2011-1234 to 2012-000), but I do not know what's the best and simplest way.

    Thanks.

    Thursday, May 3, 2012 8:46 PM

Answers

  • Hi

    In T-SQL there's a lot of ways to skin this particular cat.  However, I'd state from the get-go that there's a bunch of issues doing this in client-code.  As Simon Jones pointed out, there's issues with managing concurrency in handling this on the client-side.  As such, I'd look to defer the addition of this invoice number to a point as late in the save pipeline as possible.  It seems you're pretty much doing that by using the _inserted method.

    In terms of how I'd derive the number, I'd just use an identity field for an overall PK for your invoices table, then subtract the highest ID value for the year from the invoice ID you're displaying to your users.  You could conceivably use a T-SQL function to do this as part of a computed field, possibly cacheing the values for each year in a lookup table.

    e.g.

    CREATE Table MaxInvoiceIdByYear(
        Year int not null,
        MaxInvoiceId int not null
    )
    GO
    CREATE FUNCTION dbo.fnGetYearInvoiceID
    (
        @Year int,
        @InvoiceId int
    )
    RETURNS varchar(14) 
    AS
    {
        RETURN (
            SELECT CAST(@Year as char(4)) + '-' 
                 + CAST((MaxInvoiceId - @InvoiceId) as varchar(10)
            FROM dbo.MaxInvoiceIdByYear
            WHERE Year = @Year
        )
    }
    GO
    CREATE TABLE Invoice(
        Id int NOT NULL IDENTITY (1, 1),
        InvoiceYear decimal (4,0) NOT NULL DEFAULT DATEPART('YYYY', GetDate()),
        InvoiceId AS dbo.fnGetYearInvoiceId(Year, Id),
        -- Other fields as required
        --
        --
    )
    GO
    /* Add some data to your invoices table */
    INSERT INTO dbo.MaxInvoiceIdByYear (Year, MaxInvoiceId)
    SELECT Year, Max(Id)
    FROM dbo.Invoices
    GROUP BY Year
    GO

    This means that the ID won't be determined until the record is committed to the database, but you'll get a consistent result regardless of any concurrency issues. The only time you'll get an InvoiceID get out of step is when an invoice is actually deleted from the system and the data is compacted using a DBCC CHECKIDENT(Reseed) to re-jig all of the Identity values.

    Upsides: Simple - happens in the database, results are transparent to users, no conversion client side, no "per record" calculations client-side in grids.
    Downsides: Vulnerable to DBCC CHECKIDENT(Reseed)

    HTH


    Jeremy Huppatz
    Managing Consultant
    Solitaire Systems

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    • Proposed as answer by Jeremy Huppatz Sunday, May 6, 2012 11:19 AM
    • Marked as answer by Otomii Lu Friday, May 11, 2012 9:23 AM
    Sunday, May 6, 2012 11:18 AM
  • Hi Miro,

    If you look at my solution below, it actually uses functionality in the database to get around the duplicate invoice issue.

    Anyone developing database applications of even a relatively simple nature should be willing and able to use T-SQL from a query tool such as SSMS (SQL Server Management Studio) - or if you're using SQL Express, SSMS Express - http://www.microsoft.com/en-us/download/details.aspx?id=22985.  If you publish your application to a SQL Express instance, the database should then be persisted in a state which can be used as the basis for attaching external database tables.  More importantly, once you've published, you can then go into the database and edit it to get the functionality you need (as per my post elsewhere on this thread).  T-SQL is a far simpler language than VB - don't be intimidated by it. :)

    I should have added one additional downside to my solution - it pre-supposes the use of an extrinsic database via a SQL Server data source.  In my experience with LightSwitch, extrinsic databases should be used all the time as the intrinsic database is prone to being zapped out of existance in certain circumtances which I still haven't entirely figured out.  If you don't know how to regenerate a replacement intrinsic database you're in for a world of pain when the .MDF file goes missing.

    Cheers


    Jeremy Huppatz
    Managing Consultant
    Solitaire Systems

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    • Marked as answer by Otomii Lu Friday, May 11, 2012 9:23 AM
    Monday, May 7, 2012 3:43 AM

All replies

  • This is an interesting one. I might have a go at trying this over the weekend... but some thoughts.

    Maybe create a lookup table to keep the last reference number, then in the before inserrt event for the record do a query on the lookup table to grab the next number, then build the value by adding the date to it. When doing the grab for the value do a calculation to work out if you need to reset the numbers as the year kicks over.

    Friday, May 4, 2012 1:33 AM
  • Hello!

    incrementing Invoice No. at inserting time might work OK for some, but i know that users like applications to be more flexible,

    so what i did is to add incrementation on screen level, at time when .AddAndEditNew is called, so incremented No. will show to user

    in a text box - if they like it, they will leave it alone, or they will change if that is needed from any reason.

    This will also cover Your dating issue, as when YOu start with 2011-1234, next number will be 2011-1235 and so on...

    When the year chnages, you can edit suggested new number with 2012-0001, and it will count on from there...

    Friday, May 4, 2012 8:07 AM
  • If you do this on the Screen then you better be sure that you've written code to cope with two people both raising invoices at the same time and, if you need to ensure there are no gaps in the sequence, that you can decrement as well as increment the number or fill in a gap left if someone cancels rather than saves their new invoice.

    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Friday, May 4, 2012 8:23 AM
  • Hi,

    my solution is now that I use the invoice inserted method. I use the invoice Id do calculate the invoice number.

    I created a table with a offset for the invoice number. I create the offset if the user resets the number or if the year changes.

    With the offset i can calculate the invoice number.

    For example if I have 1000 invoices in 2011.

    In 2012 I am going to save the offset 1000 of 2011. The Id of the first invoice in 2012 will be 1001, so i am going to subtract 1000 from 1001.

    Offset  2013 = 2011 + 2012 ...

    I think this will be the simplest way for me.

    Thanks.

    Saturday, May 5, 2012 8:16 PM
  • Instead of saving offsets for each year and adding them up, could you not just get the max ID for the previous year?

    EG - IN T-SQL

    SELECT Max(ID) FROM Invoices WHERE Year(InvoiceDate) = Year(GetDate)-1

    This assumes that you don't raise invoices in the previous year after the year has changed. IE you are always up to date with your invoicing. If there is any delay then use the date of the new invoice instead of GetDate.


    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Sunday, May 6, 2012 9:03 AM
  • Hi

    In T-SQL there's a lot of ways to skin this particular cat.  However, I'd state from the get-go that there's a bunch of issues doing this in client-code.  As Simon Jones pointed out, there's issues with managing concurrency in handling this on the client-side.  As such, I'd look to defer the addition of this invoice number to a point as late in the save pipeline as possible.  It seems you're pretty much doing that by using the _inserted method.

    In terms of how I'd derive the number, I'd just use an identity field for an overall PK for your invoices table, then subtract the highest ID value for the year from the invoice ID you're displaying to your users.  You could conceivably use a T-SQL function to do this as part of a computed field, possibly cacheing the values for each year in a lookup table.

    e.g.

    CREATE Table MaxInvoiceIdByYear(
        Year int not null,
        MaxInvoiceId int not null
    )
    GO
    CREATE FUNCTION dbo.fnGetYearInvoiceID
    (
        @Year int,
        @InvoiceId int
    )
    RETURNS varchar(14) 
    AS
    {
        RETURN (
            SELECT CAST(@Year as char(4)) + '-' 
                 + CAST((MaxInvoiceId - @InvoiceId) as varchar(10)
            FROM dbo.MaxInvoiceIdByYear
            WHERE Year = @Year
        )
    }
    GO
    CREATE TABLE Invoice(
        Id int NOT NULL IDENTITY (1, 1),
        InvoiceYear decimal (4,0) NOT NULL DEFAULT DATEPART('YYYY', GetDate()),
        InvoiceId AS dbo.fnGetYearInvoiceId(Year, Id),
        -- Other fields as required
        --
        --
    )
    GO
    /* Add some data to your invoices table */
    INSERT INTO dbo.MaxInvoiceIdByYear (Year, MaxInvoiceId)
    SELECT Year, Max(Id)
    FROM dbo.Invoices
    GROUP BY Year
    GO

    This means that the ID won't be determined until the record is committed to the database, but you'll get a consistent result regardless of any concurrency issues. The only time you'll get an InvoiceID get out of step is when an invoice is actually deleted from the system and the data is compacted using a DBCC CHECKIDENT(Reseed) to re-jig all of the Identity values.

    Upsides: Simple - happens in the database, results are transparent to users, no conversion client side, no "per record" calculations client-side in grids.
    Downsides: Vulnerable to DBCC CHECKIDENT(Reseed)

    HTH


    Jeremy Huppatz
    Managing Consultant
    Solitaire Systems

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    • Proposed as answer by Jeremy Huppatz Sunday, May 6, 2012 11:19 AM
    • Marked as answer by Otomii Lu Friday, May 11, 2012 9:23 AM
    Sunday, May 6, 2012 11:18 AM
  • Simon,

    i have an interesting issue with @INSERTING method, when i create more than 1 INVOICE,

    for example - if i make 3 new Invoices, and hit SAVE after this, all 3 Invoice will get the same number with @Inserting method,

    if i just increment the last one from database?  

    if table is saved after each new row, evertyhing works well...   any fast ideas?

    THANX!

    Sunday, May 6, 2012 7:19 PM
  • Hi,

    this sounds good. But is it possible to use T-Sql in Lightswitch?

    For me it is very simple to use the inserted method and write some vb code to do this.

    With the Offset I do not need any query to calculate the invoice number. I do not know how much time it takes to do this query every time I create a new invoice?

    But I would like to have a good solution, because I think this thing is very important.

    Thanks.

    Sunday, May 6, 2012 10:57 PM
  • Hello again,

    does anyone has problems using Auto-Increment with Insterting method when saving batch of lines?

    one-at-the-time, this works great, but when i save, lets say, 10 Invoices - all 10 will have the same number?

    if i save them one at the time, it works great?

    Sunday, May 6, 2012 11:23 PM
  • Hi

    Kindly take a look on the below thread. May be here you can found how to achieve this in inserting method.

    http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/53b88ccc-7d56-4121-ba65-ad195fa36ed2


    Rashmi Ranjan Panigrahi

    Monday, May 7, 2012 3:22 AM
  • Hi Miro,

    If you look at my solution below, it actually uses functionality in the database to get around the duplicate invoice issue.

    Anyone developing database applications of even a relatively simple nature should be willing and able to use T-SQL from a query tool such as SSMS (SQL Server Management Studio) - or if you're using SQL Express, SSMS Express - http://www.microsoft.com/en-us/download/details.aspx?id=22985.  If you publish your application to a SQL Express instance, the database should then be persisted in a state which can be used as the basis for attaching external database tables.  More importantly, once you've published, you can then go into the database and edit it to get the functionality you need (as per my post elsewhere on this thread).  T-SQL is a far simpler language than VB - don't be intimidated by it. :)

    I should have added one additional downside to my solution - it pre-supposes the use of an extrinsic database via a SQL Server data source.  In my experience with LightSwitch, extrinsic databases should be used all the time as the intrinsic database is prone to being zapped out of existance in certain circumtances which I still haven't entirely figured out.  If you don't know how to regenerate a replacement intrinsic database you're in for a world of pain when the .MDF file goes missing.

    Cheers


    Jeremy Huppatz
    Managing Consultant
    Solitaire Systems

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    • Marked as answer by Otomii Lu Friday, May 11, 2012 9:23 AM
    Monday, May 7, 2012 3:43 AM
  • Jeremy,

    Thanx, i will try this!

    i have just made RIA service, now we shall move to T-SQL :)

    and we started with simple application, but LS is such a great tool that one thing led to another... :)

    Monday, May 7, 2012 1:00 PM
  • Hi Miro,

    You can attach a SQL Server table to a LightSwitch app without having to create a WCF RIA service.  :)  The beauty of my solution is that the table does all the processing just using its own definition.  There's no stored procs required.  The function is just to prevent having to compile the query for every inserted row - the function definition will cache the lookup until there's a stats change. :)

    Cheers


    Jeremy Huppatz
    Managing Consultant
    Solitaire Systems

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Monday, May 7, 2012 1:04 PM
  • yes, it is extremely elegant and robust, one must agree!

    i will definitely try something like that!

    Cheers!

    Monday, May 7, 2012 1:07 PM