How to add a prefix from the database

Applications Forms
#1

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.

#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.

#4

[QUOTE=rr;18509]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.[/QUOTE]

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.