Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : How do I write Deposits using QODBC?Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC SQL Sample Scripts Forum

 New Topic 
 
 Post Reply 
[1]  
 How do I write Deposits using QODBC? 
 Author   Message 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-21 14:19:10

Deposit inserts are done only to the DepositLine table. The Deposit table is a header table and cannot be inserted into without cached DepositLines transactions.  

Please note: If you enconter the error: [QODBC] error : 3180 – There was an error when saving a deposit line. Quickbook 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 to 2006 and only occurs when you try to do a deposit with the company file in multi-user mode.

Here are two examples:-

DEPOSIT FROM RECEIVE PAYMENTS MADE TO UNDEPOSITED FUNDS:

The first step is to locate the payment TxnID made to undeposited funds. To do this you run the following query in VB Demo:

SELECT TxnID, CustomerRefFullName, RefNumber, Amount FROM ReceivePaymentToDeposit

 

The second step is to confirm the exact name of the Bank Account you want to deposit the payment into. To do this you run the following query in VB Demo:

SELECT ListID, Name FROM Account where Name = 'ABC Bank'

Now 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 VB Demo: 

INSERT INTO DepositLine (DepositLinePaymentTxnID,
DepositToAccountRefFullName,TxnDate,FQSaveToCache)
Values ('EDB-1197676347','ABC Bank',{d'2007-12-15'},0)

If you matched the details correctly you will receive the "Query Executed. No results returned" OK message!

DEPOSIT MADE DIRECTLY FROM THE DEPOSIT WINDOW:

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'2005-02-17'},0)

Use the columns stored procedure in VB Demo to get the EXACT table schema rules for the QuickBooks Company file you have open. The last few columns have Queryable, Updateable, Insertable, Required_On_Insert ie:-

sp_columns DepositLine

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 your 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 then write all the SQL inserts as multiple lines for the one deposit (header row record).

Currently there is no DepositMod (or TxnMod) available in SDK5.0 used by USA versions of QuickBooks 2006 and DepositMod doesn't appear to have made the SDK6.0 new features list used by USA versions of QuickBooks 2007.

This means that you cannot use QODBC to add lines to an existing Deposit. One workaround is to DELETE the Deposit first (using the TxnID) and then re-create the whole deposit using the new values.

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-03-04 19:27:53

DEPOSIT CASH FROM A EXTERNAL POS SYSTEM:

To deposit cash you need to locate the List IDs for your posting.

For example, if:

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

then the insert statement looks like this:

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

Note: Never use DepositTotal, always use DepositLineAmount.

 

  Top 
  Aaron 
  
 Group: Members 
 Posts: 9 
 Joined: 2006-04-20 
 Profile
 Posted : 2006-05-08 11:24:06
The error "The payment has been changed" when in Multi-user mode continues to be a problem in QB 2006. 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to