Creating a unique numbering scheme - SQLite

[quailcreek]

Hi,
I have a question regarding creating a unique numbering scheme. For instance, a unique invoice number/ID. I was thinking about creating it in LC and saving it into the DB. Is it better and easier to creating it using the DB? I thought about doing INV-15-0001 where 15 is the year and then having the ability to create 9999 invoices in a year.

If I set the InvoiceNumber below to AUTOINCREMENT, can I have it start with 0001? If so maybe I could then use it to generate the rest of the number in LC.

Code: Select all

CREATE TABLE MyInvoices(InvoiceID INTEGER PRIMARY KEY AUTOINCREMENT,InvoiceNumber NUM,EventDate DATE,InvoiceDate DATE

Thanks for the help!

[phaworth]

Hi,
You can only use AUTOINCREMENT on primary key fields in SQLite. I usually just use the primary key value as the invoice number but if you want ti to be formatted the way you mentioned, you'll need to do it within LC.
Pete

[quailcreek]

Hi Pete,
Thank you. That makes my decision pretty easy. I'll just use the primary key as the base for building the invoice number.

[MaxV]

Hi,
I already worked with invoices. The primary key should be used only for internal purpose.
You should also create a human intelligible number that that you reset each fiscal year.
For example:

• ID, human ID
• 1, 2015-1
• 2, 2015-2
• 3, 2015-3
• 4, 2016-1

In my nation you have to number invoices starting from 1 each fiscal year.

[quailcreek]

Thanks Max. That's pretty much what I'm doing.