Using MySQL to fix broken "Other Payments"

March 26th, 2008 Leave a comment Go to comments

UPDATE: Astonishingly, this issue has been fixed in Release C.

D. Stewart and Mafalda write: "There is a glitch in Simply 2008 that allows you type 20 characters in the "Invoice/Ref." line, but really is only supposed to allow 15.  So, if you try and go back to look up or adjust an entry (I'm finding this in the payables/make other payment section so far) Simply crashes.  In order to fix this you need to send your data file to them to get fixed, it takes 4-5 business days and you cannot work on the data file until the fixed one is sent back to you.  And after it is sent back to you the only way to prevent it is to not enter more than 15 characters.  There will be no patch coming out for this, it apparently will not be fixed until the 2009 version."

4-5 business days, Sage!?  I figured this out in fifteen minutes!

I took this as an opportunity to figure out how to access Simply Accounting 2008's MySQL database.  Note that this is most definitely NOT supported - be sure and make a backup before you try this, and also be cautious if you use this solution for live data.  I've only used it with test data.

As it turned out, it was very, er, simple.  My firewall already told me that Simply's MySQL server was using port 13540.  (YMMV.) All I had to do was figure out the username and password...which turned out to be 'sysadmin' and my regular password.  A few edits of my phpMyAdmin config file later and I was browsing Simply's 262 tables.

A search of the database found the test invoice number I'd used, 1234567890123456, in the `sRef` field in two tables: `titrec`, and `tventr`.  All I had to do was truncate this to exactly 15 characters.  Problem solved!  I could now open the payments, and even edit them if I liked.

I got curious.  I noticed the `sRef` field in both cases was of type VARCHAR(20).  What would happen if I changed it to VARCHAR(15)?  I got excited when I tried to enter an invoice number with 16 characters and it wouldn't let me, but would allow me to enter a 15-character number.  But, it wouldn't let me view any other payments at all, claiming "This invoice has been cleared from the data; it cannot be adjusted."  Changing back to VARCHAR(20) made it work, which means that somehow the column type is significant.

Okay, Sage.  I think that issue should be ridiculously easy to fix - how 'bout it?  😀

If you would like a reply to your comment, you must leave your email address! We receive dozens of questions every month from people who don't leave us with any way to contact them, so we have no choice but to ignore the question. We try to reply to as many questions as we can, if we know the email address of the person who asked the question. Thanks in advance for writing in :)

Allowed HTML: <b>, <i>, <em>, <strong>. All other < and > will be replaced with &lt; and &gt;.