Creating a unique numbering scheme - SQLite

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Creating a unique numbering scheme - SQLite

Post by quailcreek » Fri Oct 09, 2015 12:48 am

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!
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Creating a unique numbering scheme - SQLite

Post by phaworth » Fri Oct 09, 2015 7:19 pm

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
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Creating a unique numbering scheme - SQLite

Post by quailcreek » Fri Oct 09, 2015 7:54 pm

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.
Tom
MacBook Pro OS Mojave 10.14

MaxV
Posts: 1577
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Creating a unique numbering scheme - SQLite

Post by MaxV » Mon Oct 26, 2015 2:08 pm

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.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Creating a unique numbering scheme - SQLite

Post by quailcreek » Tue Oct 27, 2015 12:40 am

Thanks Max. That's pretty much what I'm doing.
Tom
MacBook Pro OS Mojave 10.14

Post Reply

Return to “Databases”