Troubleshooting - How to get Bank Reconciliation details using QODBC

Problem Description:

How to get Bank Reconciliation details using QODBC

I've not successfully identified any field/flag identifying the bank reconciliation status. Is this information available thru QODBC? Or has it not been made available?

I'm building a supplemental (to QuickBooks) bank account management/forecasting application. I want to be able to pull bank reconciliation data from QuickBooks (using QODBC) and be able to identify cleared/not cleared bank account transactions. This would be very useful for a client of mine. It's easy to remove all bank account transacbanks (debits and credits) from the Transaction table, which is very much like a bank statement if you order by date. But I've not been able to determine the CLEARED status of each transaction. Is there any way for me to determine this thru QODBC?

Solutions:

The Bank Reconciliation details are unavailable through the Intuit SDK, so they are unavailable through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.

You can refer ClearedStatus table, which is used to clear checks using QODBC.

The "ClearedStatus" table is Insert Only table. It is used for Insert purposes only.

You can clear check using QODBC by inserting TxnId & check the status in the "ClearedStatus" table. If you want to clear check using QODBC, Please refer to the below steps:

Please open the Check register in QuickBooks & locate the transaction in which you want to update the check status.

For Example, I want to update the check status of the Bill Payment:

To locate the TxnID of the "BILLPMT" check, I ran the following:

SELECT TxnID, AppliedToTxnSeqNo, TxnDate, PayeeEntityRefFullName, Amount FROM BillPaymentCheckLine where RefNumber='294'

Using this TxnID, I can mark the check as cleared by running the following using the INSERT only ClearedStatus table:

INSERT INTO ClearedStatus ("TxnID," "ClearedStatus") VALUES ('39D7-1071529831', 'Cleared')

BTW: The valid values are: |Cleared|NotCleared|Pending|

And the check is now marked as "Cleared" in QuickBooks.