Troubleshooting - How to get Bank Reconciliation details using QODBC
How to get Bank Reconciliation details using QODBC
I've not had success identifying any field/flag that identifies the Bank Reconciled status. Is this information available thru QODBC? Or has not been made available?
I’d like to be able to pull bank reconciliation data from QuickBooks (using QODBC) and be able to identify cleared/not cleared bank account transactions. It’s easy enough to pull from the Transaction table all bank account transactions (debits and credits) which if you order by date, is very much like a bank statement. But I’ve not been able to determine the CLEARED status of each transaction. This would be very useful for a client of mine that I’m building a supplemental (to QuickBooks) bank account management/forecasting application. Is there any way for me to determine this thru QODBC?
The Bank Reconciliation details are not available through the Intuit SDK so they are not available 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 application in SDK, QODBC could not do it either.
You can refer ClearedStatus table which is used to clear check using QODBC.
"ClearedStatus" table is Insert Only table. It is used for Insert purpose only.
You can clear check using QODBC by inserting TxnId & check status in "ClearedStatus" table. If you want to clear check using QODBC, Please refer below steps:
Please open Check register in QuickBooks & locate transaction which you want to update check status.
For Example, I want to update check status of Bill Payment:
To locate the TxnID of the "BILLPMT" check I ran:
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.