Re: sorting invoice numbers by numerical order

[Chris Travers <..hidden..>]

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
> before producing the html page, and the other was to use javascripting
> to do the same.  My question is about numbers, though, not letters.
> For my installations, when sorting by invoice number, my numbers go like
> this:
>
> 1
> 10
> 100
> 101
> 102
> 103
> 104
> 105
> 106
> 107
> 108
> 109
> 11
> 110
> 111
>
> and so on.  Is there a way to make it so invoices 11-99 comes *before*
> 100??

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

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.

Brest Wishes,
Chris Travers