shadow

OpenBravo POS Ticket ID Duplicate Problem

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.

3 Comments

  • kevin

    20 December 2012 at 22:45

    Hey, am having the same problem, my receipts are not showing any numbers and on the edit sales they all have the same number 0, please help am new in coding

  • Christian

    18 February 2013 at 20:36

    Hi, sorry for my english, i have the same problem but i dont know how to apply the solution that you show, can you post the code and the comments step by step?? i'll really appreciate it

  • 1Earth

    21 February 2013 at 10:33

    Hi Christian, unfortunately this happened so long ago, I can't remember the exact steps.

    You have to go into your Database to do this, and you may or may not be using MySQL.
    1. Find out what your last Ticket ID was – look at the table TICKETS and the field TICKETID – there may be duplicates or blank fields, so work it out properly.
    2. Then take that Ticket ID and update the table TICKETSNUM and field ID with it. After that it should work.

    Take note, this post is 2 years old, so things may have changed in the OpenBravo database and may or may not work as it did when I wrote this post.

    Good luck.

Post a Comment

%d bloggers like this: