~~SUPPORT – Transaction line ordering bug~~

[LessPaperCoParticipant]

Hi,

It’s been 9 days since my last post response which outlined exactly what the problem is with OpenSync and how you can fix it. I also emailed Yudel directly and sent an email to Synergration from the contact page — no response. We have been purchasing 1 copy of OpenSync every month for the last while and while we may not be your largest customer we have spent thousands of dollars on this software which is not working properly.

[LessPaperCoParticipant]

Reference to my original post: https://synergration.com/forums/topic/estimate-invoice-line-item-ordering/

This original post was on 4/1/2019 and I only received one or two vague responses on it.

I have clients ready to cancel services with us because they are creating invoices on our software and it is going to QB completely out of order — it’s not using the order they were entered into the DB, not using TxnLineID or SeqNum or anything. I have explained WHY it’s doing this and how you can fix it – it SHOULD be fairly simple in the database query you are making.

Reference to the problem and solution: https://synergration.com/forums/topic/sort-by-txn-line-id-option-not-executing-during-scheduled-task/#post-65347

We have 2 clients ready to go live right now that we need to purchase 2 more copies of OpenSync for but we’re holding off until this bug is fixed or we at least hear SOMETHING back about an ETA, a solution, etc. I have seen Yudel respond to another post or two since my responses so I know somebody is working – why haven’t I received a response?

We also have another client in the next 30 days who will be going live. We don’t want to lose the clients who are pissed off at us already and we don’t want to have to spend days finding another syncing solution – we’d rather give our money to you since your product is generally easy to use. But we need some kind of response, please. What is going on?

[Yudel RosalesKeymaster]

I asked you to do a remote session a month ago. A month after if when you post again. Can you put all the issue in one only post? You have so many posts. It is a litter confusing.

[LessPaperCoParticipant]

Yudel – you didn’t ask me to do a remote session you asked Michael to do a remote session. There shouldn’t be a need for a remote session – the fix should be fairly simple.

THE PROBLEM
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.

[Author]

Posts