Troubleshooting: Updating JournalEntry Records via QODBC shows Error, Transaction not in balance

Instruction:

Under QuickBooks when saving a Journal Entry record, the Debit total should match with Credit total.

Even thou you are not changing the amount, and just updating other information on Credit/Debit entry, You may encounter "Transaction not in balance":

UPDATE JournalEntryDebit Line SET journaldebitlineaccountreflistid='8000008F-1197954502', journaldebitlineaccountreffullname='Note Payable - Bank of Anycity', journaldebitlineamount=35000 where txnid='5E40-1197715323'

This query will return with an error like this:

Solution:

To make Journal Entry update query work, you should add FQSaveToCache field into your update query, and Updated BOTH
JournalEntryDebitLine &  JournalEntryCreditLine

When FQSaveToCache is set to value 1, all changes will be stored in a cache instead of directly sending to QuickBooks, and when it is set to 0, all updates in the cache will be sent to QuickBooks in one time.

Please modify the query like this:

UPDATE JournalEntryDebitLine SET journaldebitlineaccountreflistid='8000008F-1197954502', journaldebitlineaccountreffullname='Note Payable - Bank of Anycity', journaldebitlineamount=35000, FQSaveToCache =1 where txnid='5E40-1197715323'

UPDATE JournalEntryCreditLine SET journalcreditlineaccountreflistid='8000008F-1197954502', journalcreditlineaccountreffullname='Note Payable - Bank of Anycity', journalcreditlineamount=35000, FQSaveToCache=0 where txnid='5E40-1197715323'

Please notice that I added FQSaveToCache field in my update query and set the first value to 1 and last value to 0 so that the update can be done at the same time to avoid imbalance problem.

Here is the data in QuickBooks before I run the update:

Here is the data in QuickBooks after I run the update: