Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105

    Unanswered: Sequencial, Unique Number tied to header, Locking Q?

    I have a situation where for a given customer, their invoices need to be sequentially numbered, without gaps.

    Customer A Invoice 1,2,3,4 ...
    A-1
    A-2
    A-3
    A-4

    Customer B:
    B-1
    B-2

    etc.

    The issue is 2 people creating an invoice for the same customer at the same time. Currently I don't assign the Invoice number until the user hits 'Save'. At that time I query for max(invoiceno) against the customer key and simply add 1. it's the last operation prior to saving against the backend.

    If 2 users hit Save at the exact time, I'm getting the same (duplicated) Invoice Number.

    What suggestions/techniques do you have to resolve this? Would "locking" of the customer record and storing the last highest invoice there play a part?

    Thanks,

    Peter
    Last edited by Pdiotte; 09-23-06 at 18:53.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Put a unique constraint on the combination of CustomerID and InvoiceNumber.
    If two users actually save records at the exact same time, one of the records will error out, at which time your interface can resubmit it for a new invoice number.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Blindman:

    I did what you said and it works great. Thanks for a simple solution!

    Peter

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    May I ask if the sequential numbers without gaps requirement still applies if one of the records gets deleted? If so should this not be derived at runtime (or alternatively accounted for in a trigger - not my preference I might add)?

    If not then presumably this number needs to be derived by code rather than inputted by the user (in case the user enters an out-of-sequence number)?

    Just throwing in a couple of the issues that typically arise with this sort of thing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2005
    Location
    Milwaukee, WI
    Posts
    105
    Good Question.

    A couple items:

    1. For this app, deletes are not allowed. A "Void" flag approach is used.
    2. If there was a gap - that is actually acceptable, just not preferred. The hard requirement is that there aren't any duplicate customer+invoice numbers. The "no gap" made my explaining above easier, but isn't a hard requirement.
    3. The app requires VCR buttons on the record to find the next highest record. Even though we shouldn't see gaps, I coded it within the app so it can handle a gap of up to 10 records missing.

    Peter

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pdiotte
    Good Question.
    Thanks!

    Glad Blindman's solution covered all your bases.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •