[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: sorting invoice numbers by numerical order
- Subject: Re: sorting invoice numbers by numerical order
- From: Chris Travers <..hidden..>
- Date: Tue, 16 Feb 2010 08:10:22 -0800
On Mon, Feb 15, 2010 at 9:58 PM, Bob Miller <..hidden..> wrote:
> Hello list,
> I hope you are all having a pleasant day...
> There was some chat a while ago with regards to sorting by description,
> and the two suggestions were to sort the query in the sql statement
> to do the same. My question is about numbers, though, not letters.
> For my installations, when sorting by invoice number, my numbers go like
> and so on. Is there a way to make it so invoices 11-99 comes *before*
If all invoice numbers include only numbers instead of letters, you
could alter the SQL query to order by the invoice number converted to
a numeric datatype. The problem though is that invoice numbers can
include letters, so consequently they are sorted in the db as text
strings. Otherwise an invoice like 110-V would cause such a query to
It would be possible to change the sort by adding some Perl code but
this could cause other side effects.
> I am also curious if there is going to be (or if there already is) a way
> to enforce no duplicate invoice numbers in 1.3?
in psql, run:
CREATE UNIQUE INDEX ar_invnumber_u ON ar(invnumber);
If you have duplicate numbers and just want to change them going forward:
SELECT max(id) from ar;
Suppose this tells you 10034
CREATE UNIQUE INDEX ar_invnumber_u ON ar(invnumber) where id > 10034;
To enforce AP uniqueness, you would probably have to:
CREATE UNIQUE INDEX ap_invoice_u ON ap(invnumber, vendor_id);
Simply because two vendors could issue the same invoice.