Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: how to make invoice number?

    I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". For example, if costumer "Jack" has ordered as the following:
    Code:
    ID   costumer     product     quantity  unitprice    orderdate
    
    12      jack         A            4        10        25/11/2014
    13      jack         B            5         5        25/11/2014
    14      jack         C            1         4        25/11/2014
    15      jack         F            3         2        25/11/2014
    16      Jack         G            4         8        25/11/2014
    17      Jack         H            1         7        25/11/2014
    Then I make a report based on the above table and issue that as the invoice to the costumer, who is jack here.
    How can I make a unique invoice number, which automatically increases sequentially for each invoice? Do I need to add a new column to the table? I also need to be able to look for a specific invoice based on the number, or date.
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if your invoices numbers just need to be unique the use the autonumber datatype. but that doesnt' work if you must have no missing numbers (something accountants/auditors can get very upset about). Also if you are a small trader then havign a numbering system which runs from 1 sequentialy gives your competitors a clear indication of how much or how littel trade you are doing.

    if autonumber doesn't cut if for you then create an function which supplies the next available number. there's plenty of examples on this site and on t'web

    if you need to obfuscate the onvoice number (and there's lots of ways to do that, then you will need to extend that function to apply whatever obfuscation you want to apply.


    prefixing with year and suffixing with month and day is a good'un, or interleaving minth and day as a suffix also works


    eg
    27 dec 2014
    invoice number 3405, lets assume a 6 digit number that is obfuscated

    410034051227
    thats is:-
    2 digit year reversed
    sequential number padded to 6 digits
    1st digit of month (zero padded)
    1st digit of day (zero padded)
    2nd digit of month
    2nd digit of day

    others things:- add a checkdigit
    or think of some equally obtuse way of masking the true invoice number. but make certain your sequential number is clear so that users of the system can quickly identify the correct invocie without the onfuscation
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    132
    Quote Originally Posted by healdem View Post
    if your invoices numbers just need to be unique the use the autonumber datatype. but that doesnt' work if you must have no missing numbers (something accountants/auditors can get very upset about). Also if you are a small trader then havign a numbering system which runs from 1 sequentialy gives your competitors a clear indication of how much or how littel trade you are doing.

    if autonumber doesn't cut if for you then create an function which supplies the next available number. there's plenty of examples on this site and on t'web

    if you need to obfuscate the onvoice number (and there's lots of ways to do that, then you will need to extend that function to apply whatever obfuscation you want to apply.


    prefixing with year and suffixing with month and day is a good'un, or interleaving minth and day as a suffix also works


    eg
    27 dec 2014
    invoice number 3405, lets assume a 6 digit number that is obfuscated

    410034051227
    thats is:-
    2 digit year reversed
    sequential number padded to 6 digits
    1st digit of month (zero padded)
    1st digit of day (zero padded)
    2nd digit of month
    2nd digit of day

    others things:- add a checkdigit
    or think of some equally obtuse way of masking the true invoice number. but make certain your sequential number is clear so that users of the system can quickly identify the correct invocie without the onfuscation
    thank you very much,
    lets suppose i have made a type of auto-number. Now,can you instruct me if how i can assign a number to a series of orders shown in an invoice?
    I mean, in the above example, there should be a number, 13896 for example, assigned to orderID from 12 to 17. If another costumer makes orders with orderID from 18 to 21, the invoice number 13897 will be assigned. i will need in the future to find the orders asigned to any invoice number, for example orderIF from 12 to 17 for invoice number 13896. my question is that how can i find orders assigned to an invoice number.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Wrong way round. Assign orders to an invoice, using an intersection table that identifies what orders comprise this invoice. Logically its the same approach as when creating an order.
    an order exists, it has one or more details of items ordered. Those items ordered are an intersection table identifying the order number ( and through that the address, customer name and so on) and the product table.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    132
    thank you, but i think have lost the subject. i am gonna make a new post with a more clear question.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  6. #6
    Join Date
    May 2012
    Posts
    132

    how to make such a invoice number?

    I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". Can we assign a unique number, named invoicenumber here, for each set of orders hat a costumer puts? Please see this table:
    Code:
    ID   costumer     product     quantity  unitprice    orderdate      invoicenumber
    
    12      jack         A            4        10        25/11/2014          8612
    13      jack         B            5         5        25/11/2014          8612
    14      jack         C            1         4        25/11/2014          8612
    15      jack         F            3         2        25/11/2014          8612
    16      Steve        G            4         8        25/11/2014          8613
    17      Steve        H            1         7        25/11/2014          8613
    17      John         C            1         5        28/11/2014          8614
    17      John         D            3         6        28/11/2014          8614
    17      John         G            1         8        29/11/2014          8615
    17      John         L            1         3        29/11/2014          8615
    17      Jack         C            2         5        29/11/2014          8616
    Actually Id like to know how I can design the invoicenumber field so that they are set automatically, increase sequentially, and unique for a set of orders like the above example.
    Thank you
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    http://www.dbforums.com/gtsearch.php...663j5394787j30

    or use adomain fucntiuon such as dmax (but be aware that you could have problems in a multi user environment)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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