Announcement

Collapse
No announcement yet.

How to add a prefix from the database

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to add a prefix from the database

    How to add a prefix from the database?
    At the forum I found this solution to manually add a prefix or suffix:

    {field_name} = {field_name}.'%'; or {field_name} = '%'.{field_name};

    But how to add a prefix from the one table and then add the year and the number of invoice?
    Example result: INV-2013-0010 (INV- is stored in table user_invoice_prefix), 2013 is actual year and 0010 is invoice number (auto increment)
    Is auto increment a good idea or not for the invoice number?
    What happens after the transition year in 2014?
    In 2014, I need a new number starting with INV-2014-0001

    prefix is in one table - user_profile ---> user_invoice_prefix
    I have to add the entry to the table customer_invoice ---> invoice_number

    Thanks for all the advice in advance.
    Rik

  • #2
    If you set an autonumbering on your field in your database then this is not a correct solution if you want to have a new sequence on every new year. But if you have more people creating invoices then the invoicenumber may not interfere with eachother and you are not allowed to create a gap if someone cancels the creation of the invoice for any reason (at least in our laws).

    I would create the following in the onbeforeinsert event (conceptual, not real code):

    asume i have a table invoices with invoiceyear and invoicenumber

    select max(invoicenumber) as m from invoices where invoiceyear = ##current year
    if result is empty then
    invoicenumber =1
    else
    invoicenumber = result + 1

    Then insert the data on the current year.

    Hope that the idea is clear. This way the numbering will start at one at every new year.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      The above solution almost always works fine. But when youhave a heavy traffic site it may fail:
      user 1: does the select max and gets number 2000 but the insert code executed yet
      user 2: does the select and then also gets number 2000

      Hence avoid these constructions and simply use sequences.

      Comment


      • #4
        Originally posted by rr View Post
        The above solution almost always works fine. But when youhave a heavy traffic site it may fail:
        user 1: does the select max and gets number 2000 but the insert code executed yet
        user 2: does the select and then also gets number 2000

        Hence avoid these constructions and simply use sequences.
        I agree. But it's common use to have invoicenumbers this way. So you need to use protective code to redo when the key is already there due to some other proces.
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment

        Working...
        X