Struggling with OpenBravo POS again and this time the issue I had was that my Ticket IDs (used for Receipt Numbers) didn’t auto_increment as they were supposed to do, and instead duplicated.
I’m not entirely sure how this problem started. If I had to come up with a theory I’d guess that it was because I manually deleted a few ticket entries from my OpenBravo POS database. I’ve also been flicking on and off the FOREIGN_KEY_CHECKS, another likely cause.
Whatever the cause, the ticket numbers started duplicating. In itself not a big problem, because aside from the TICKETS.TICKETID in the OpenBravo POS database, the TICKETS.ID field is absolutely unique and thus your receipts are still separated.
In fact, the only purpose I can so far deduce for TICKETS.TICKETID is for human reference to any particular receipt.
Anyway, when the Ticket IDs start to duplicate the receipt numbers no longer gets printed on the receipts for one, and it also screws up refunds as the OpenBravo POS system always shows only the 1st receipt in a series of duplicates, regardless of many duplications you have.
I wasted plenty of time searching for a solution using search terms like “openbravo pos ticket ids dont auto increment“, “openbravo pos ticketsid duplicate“, “ticketsid problem openbravo pos“, “openbravo pos tickets.ticketid same“, etc. Of course, it seems like nobody else has ever had this problem or has ever written about it.
During my explorations I noticed in the Terminal window (on Mac), OpenBravo POS uses “SELECT LAST_INSERT_ID()” at the end of a sale. I know from previous research that last_insert_id() is session dependent.
Separately I read in an unrelated forum post that there is a table, TICKETSNUM, with only one field, ID, which stores the last ticket’s number for in between sessions. I had a peek at mine, and it had no numerical value.
I also noticed that the duplication only occurred on receipts recorded in the same session. If I logged out and back in again, it would once again increment, but for that session, only by 1.
Not Really A Solution
At first I set the TICKETS.TICKETID to auto_increment, which I thought would solve the problem. It didn’t, as the duplication still occurred. I guess it has something to do with the SQL that OpenBravo uses for the insert.
Next I tried to force the field to be unique by creating a unique index for the column. This failed famously, because now all OpenBravo did was say that an error occurred, because the ticket number already exists. It still processed the transaction, it just didn’t record a ticket number for it.
The Real Solution
What solved it in the end was to update the TICKETSNUM.ID field with the last inserted ticket’s id.
After that, not only did the receipt number increment without duplicating, but the TICKETSNUM.ID field was also incremented each time, which is how it is supposed to work.
And that’s how I solved the OpenBravo POS Ticket ID Duplicate Problem.