Numerical sorting out of order - can't sort by invoice number

[swinghosting]

Perhaps elsewhere, but at least on the invoices page, when you sort by invoice #, they are out of order. "5, 3, 192, 187, 290…" This is NOT a "feature request," as anything created sequentially with sequential numbers should obviously be able to be displayed sequentially.

 

This is one of those really annoying things about WHMCS where I just stare dumbly and go, "Really? ..... Really?"

[brian!]

on the client or admin side?

[WHMCS John]

Hi,

The invoices are sorted by the unique ID value. Have you perhaps assigned different Numbers to the invoices?

[swinghosting]

I believe I changed the invoice # on one or two a few years ago, but there are several that sort to the top inappropriately. It does look like they are the only ones. Regardless, clearly they should sort by the id #, rather than some invisible value.

[brian!]

Hi John,

 

I think there is something to what swinghosting is saying with this as i've seen this with our whmcs setup too.

 

posted below is a merged screenshot of two different client accounts - the top half is sorted by invoice number descending (high->low), the lower is again sorted by invoice number but in ascending order (low->high).

 

as you can see, neither are sorted purely in invoice number order - i'm not sure of the SQL query the page is using, but it looks to be sorting on "status" and then "id", rather than just on "id"...

 

the screenshot below is using the latest "default" template (but same thing happens on any custom theme I use)... i'm sure this problem has existed for a long while - sorting the invoices on the admin side seems fine, so I assume that's not using the same SQL query.

 

we did change our invoice numbering when we took WHMCS live, but that shouldn't matter - they're just numbers in the database and I can't see any issues when looking at the tblinvoices table... e.g if #1070 and #1154 are sorted correctly, why isn't #1133 ?

 

 

for my custom template, I can slightly get around the issue by removing the sort option from the "Invoice Number" field - sorting by invoice date generally sorts the invoice numbers correctly - although if there are a few invoices generated on the same day, they might not be shown in the correct order... thankfully, i've never had a client comment about this yet!

 

even so, it might be worth taking a closer look at to see if there is an issue.

[swinghosting]

My issue regards the admin.

[brian!]

how weird - the admin side is fine on ours, only the client side is affected... sounds like it's the opposite for you.

 

out of interest, does each block of invoice numbers (5,3), (192, 187) and (291, 290, 289) have the same status, e.g Cancelled, Paid and Unpaid ?

[swinghosting]

Status doesn't appear to enter into the equation.