I submitted a couple requests about 11 MONTHS ago and the last I heard is that you were working on it. It’s regarding the line items being sent for invoices, estimates, etc going to QB and coming back from QB out of order. Our clients are extremely frustrated by this ordering. We had to actually stop purchasing OpenSync and go with other softwares for the last year while we waited for this fix. Even moved clients we had on OpenSync to the new softwares. I’d love to be able to start using OpenSync again.
The fix seems to be pretty simple to me – you just have a DB query that is missing “ORDER BY” in it. Here are the previous two threads:
To reiterate what I said on one of those posts:
It seems like whenever we send estimates or invoices to QuickBooks the line items send completely out of order of how they were inserted into the MySQL table. When we send estimates/invoices we: delete all previous line items, enter all of the new items one at a time in order from 1st to last and set the SeqNum also for each of them in incremental order from 1-whatever the last one is.
When they go to QB it appears like some of them are in order but then some are out of order. For instance, for simplicity sake I entered lines 1-18 and they came back in this order: 18, 17, 15, 16, 14, 13, 12, 2, 3, 5, 6, 7, 8, 9, 10, 11, 4, 1
I thought maybe it had something to do with it being reversed because it goes 18-12 but then it goes in the correct order again skipping 1 and 4 and then tacking those onto the end. There was bout 30 line items in this estimate and it keeps going like that, in and out of order.
THE REASON & SOLUTION
After hours of testing I have figured out what the problem is and, unfortunately, there is nothing we can do on our end as users – it’s a problem with how OpenSync queries the database.
OpenSync, when pulling estimatelinedetail, invoicelinedetail, etc is not using any ORDER BY in the query. When not using an ORDER BY it is pulling it in the exact order the MySQL returns (“SELECT * FROM estimatelinedetail WHERE IDKEY=’12345′”)… The problem with this is that MySQL does not return the results in the same order that they’re inserted into the DB. You can never rely on the order of something without having an ORDER BY in the query.
I tested dozens of times and every single time, no matter what I put in SeqNum or TxnLineID columns it ALWAYS came back in the order that is returned when you query the lines without an ORDER BY clause.I have tested and every time I query the estimatelinedetail/invoicelinedetail table without the ‘ORDER BY’ it comes in the exact order that OpenSync collects it as – so it’s definitely missing the ORDER BY in OpenSync’s code when it queries the database.
This should, hopefully, be an easy fix for you. Just have it order by one of those and let us know what which one we should be using.
That is a feature that it was scheduled to be added on this version but unfortunately, Tom didn’t add it. For the next version that is something that will be on it. Sorry for the inconvenience.
Thank you. It’s not really a feature.. it’s a bug.
Regardless we have stopped purchasing copies of OpenSync until this is resolved because not being able to order transaction lines properly is a basic necessity of QB syncing. Hopefully it’ll get added one day and we can continue purchasing copies of OpenSync for our clients.
In the next version that will be added. Sorry for the inconvenience.
Has this been corrected? I see a SeqNum on lines but when I populate it there is no difference in the final order once I sync to QuickBooks.
Not as far as I’m aware. It’s been almost 2 years since I submitted the bug but this company doesn’t care enough to fix it. I even gave them exactly what the problem was with their code – it’s all in the SQL query and not having an “order by” on it. It should be a super simple and quick fix but they don’t care. I’ve since switched to another product which has much better support even if it is a bit more expensive. Good luck
I read through your very detailed description of the issue and suggested resolution. It’s a shame they haven’t acted on it. I myself have had other similar issue with the program that have gone unresolved for the most part with some fixed very, very much later than they should have been. Shame, it is a very useful and unique program.
Appreciate the feedback it will save me some time!
I just emailed you.
The forum ‘OpenSync’ is closed to new topics and replies.