For the past year I have been generating invoice numbers with the date followed by a sequence number and it’s been working great.
However, I wanted to have a way to quickly identify a client from an invoice number, so I created a unique 6 letters ID Number for each client that now sits between an entity identifier (F), the date (ymd) and the 4 digits sequence number. The generating has been working as intended, but I now have some issues with sorting, as if IN would not consider the last characters as the more important for setting the order.
For instance, here is a screenshot of my most recent invoice numbers (I hid the client IDs):
Note how all invoice numbers from 2021 are properly sorted according to the last numbers, regardless of the first characters (the date). The ones from 2022, on the other hand, seem to follow a different logic - I get, from latest to oldest: 3109, 3108, 3110, 3107 and 3106 - with Invoice 3110 clearly not sorted properly, as it should be on top.
If you hadn’t hidden the client IDs we would probably see why. It’s not just the last four digit sequence number that’s used for sorting, it’s the entire invoice number.
Last years numbers are correctly sorted, because each new invoice with the next higher sequence number also has the same or a later date than the previous one.
I you had used “F”, followd by $date, then $sequence number and put the client ID last sorting would probably work (still assuming you’d not create invoices out of date order).
Sorting by creation date instead of invoice number should probably give you the sort order you are looking for.
Alright, on second thought I guess these look random enough that I can post them. Here they are:
I see! I’m still not getting the logic, though!
You’re right! I tried it and it works. I might just change to that naming scheme I guess.
That sorting priority works on my OS (Windows) because the “created date” there includes a timestamp, so logically every new invoice with a new sequence number will have a more recent timestamp, hence sorting by date is getting all sequence numbers in numeric order.
Unfortunately, IN seems to only record the actual date of creation of the entity and not the exact time (well, not in the frontend that is - because the precise timestamp of creation is recorded in the database (here is a screenshot from phpmyadmin)):
So, unless IN uses those timestamps in the date sorting, my issue won’t be fixed this way as currently all the invoices created on the same day are treated equally and it seems like then the next sorting step it takes is going back to alphanumerical order, which ends up displaying the exact same order as my first screenshot.
@hillel, would it be feasible to include the timestamps under the hood when sorting by date in the admin UI? Just a thought, because there are other workarounds to my issue.
@hillel, oh my bad, you’re right! I didn’t realize that there was a “created at” column separate from the “date” column. I tried adding it and it indeed shows the full date and time, which sorts the invoices in the proper order I needed.
