Knowledgebase
[QODBC-Desktop] How to write Deposits using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Two Examples of Writing Deposits

Instructions

Note: Deposit inserts are done only to the DepositLine table. The Deposit table is a header table that cannot be inserted without cached DepositLine transactions.

Troubleshooting Note

     If you encounter the error below:

     [QODBC] Error: 3180 – There was an error when saving a deposit line. QuickBooks error message: The Payment has been changed. (#10053).

     Switch your company file to Single User Mode. This is a "confirmed by Intuit" bug in QuickBooks 2004 through 2006 and only occurs when you try to do a deposit with the company file in multi-user mode.

     Below are two examples.

DEPOSIT FROM RECEIVE PAYMENTS MADE TO UNDEPOSITED FUNDS

Locate the Payment TxnID

      The first step is to locate the payment TxnID made to undeposited funds. To do this, you run the following query in QODBC Test Tool:

      SELECT TxnID, CustomerRefFullName, RefNumber, Amount FROM ReceivePaymentToDeposit


Note: 
 QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

 

Confirm the Exact Name of the Bank Account

      The second step is to confirm the exact name of the Bank Account you want to deposit the payment. To do this, you run the following query in QODBC Test Tool:

      SELECT ListID, Name FROM Account where Name = 'Company Savings Account'

Receive payment made to Undeposited Funds

     You're ready to receive the payment made to undeposited funds using the TxnID and Bank Account Name found above. To do this, you run the following query in QODBC Test Tool:

     INSERT INTO DepositLine (DepositLinePaymentTxnID, DepositToAccountRefFullName,TxnDate,FQSaveToCache)
    Values ('8BFF-1481801824','Company Savings Account',{d'2021-12-20'},0)

     You will receive the "Executed Successfully" message if your above input details are correct.

DEPOSIT MADE DIRECTLY FROM THE DEPOSIT WINDOW

Query in QODBC Test Tool 

     INSERT INTO DepositLine (DepositLineAccountRefFullName,DepositLineAmount,DepositLineCheckNumber,
     DepositLineEntityRefFullName,DepositLineMemo,DepositLinePaymentMethodRefFullName,
     DepositToAccountRefFullName,Memo,TxnDate,FQSaveToCache)
     Values ('Interest Income',2000,'PANDA4567','2V:Panda Industries','Panda
     memo','Check','BankNorth','Deposit',{d'2021-02-17'},0)

Table Schema Rules for DepositLine

Note: Use the columns stored procedure in QODBC Test Tool to get the EXACT table schema rules for the QuickBooks Company file you have open. The last few columns have Queryable, Updateable, Insertable, and Required_On_Insert i.e.

     sp_columns DepositLine

FQSaveToCache

     Multi-line inserts require a series of SQL statements to complete a single deposit. The key to this process is the field named "FQSaveToCache". This field is not part of the table but is used as a flag to the QODBC driver to cache the inserts until you're ready to commit all the inserts. The value of "FQSaveToCache" is set to 1 or TRUE for the insert SQL statements for the first lines in the deposit, and then it is set to 0 or FALSE for the final SQL statement, the last line of your multi-line deposit.

     The 0 or False "FQSaveToCache" value tells QODBC to write all the SQL inserts as multiple lines for the one deposit (header row record).

 

DEPOSIT CASH FROM AN EXTERNAL POS SYSTEM

Locate List IDs

     To deposit cash, you must locate the List IDs for your posting. For example:

     3C0000-1106175506 is GL Bank Account List ID
     3E0000-1130003331 is AR Account List ID

Insert Statement

     INSERT INTO "DepositLine" ("TxnDate", "DepositToAccountRefListID", "Memo", "DepositLineAccountRefListID", "DepositLineAmount") VALUES ({d'2021-10-27'}, '3C0000-1106175506', 'POS Cash Deposit', '3E0000-1130003331', 5.50)

Note: Never use DepositTotal, always use DepositLineAmount.

Keywords: accountlistid, accountname missing stored procedure, Receive, how to fix things after you dd an error on your reference while depositing, how to deposit a payment without an invoice

(167 vote(s))
Helpful
Not helpful

Comments (2)
Neil McAllister
11 April 2016 07:40 PM
So then how do you post a refund amount? An error comes up same a depositLine amount may not be negative. fkg6nsy
Jack
12 April 2016 06:52 AM
Hi,

Please refer:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/994/0/refunds-with-qodbc
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2675/45/troubleshooting---how-to-give-refund-to-existing-creditmemo-using-qodbc

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).