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 the Credit total.

Even thou you are not changing the amount and just updating other information on the 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 an error like this:

Solution:

To make the Journal Entry update query work, you should add the 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 the FQSaveToCache field in my update query and set the first value to 1 and the last value to 0 so that the update can be done at the same time to avoid an imbalance problem.

Note: Generally, the field FQSaveToCache is used with the INSERT Statement, but it can work with the UPDATE statement only for these tables. 

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

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