Hi,

I am looking into building a system to record deposits through the use of queries instead of by hand due to the large number of deposits required by my company. I would simply like to know what needs to be changed on the "back end" to record a deposit? To explain myself better... I believe to enter a SalesReceipt, you need to enter the data for your SalesReceipt, and your SalesReceiptLine, and you need to modify the customer table if any changes are necessary.

I would like to get a similar idea of the basics of what needs to be done to record a deposit, and any information you can provide about this feature.

Thank you for your time

Ben

I'm sorry, i'm not understanding what this information is meaning. To explain myself further, we are currently hand inputting our deposits from our SalesReceipts, but this is a very time consuming job to handle with the amount of deposits that are going through our system. I need to replace the hand work of taking the money from our sales receipt, and depositing it to our account to show that we have received the money for the order. After some looking around i found that there is a DepositLine and a Deposit table in QB, could it possibly be as simple as inserting a line into those 2 tables with the new deposit information and this transfers the SalesReceipt from the holding account to the paid account?

I will look further into the information that you have provided me with, but i had that question to begin with.

Thanks again

Ben

When creating Sales Receipts you need to nomiate the DepositToAccountRefListID bank account for the receipts of the payment (funds) as per: How do I create a Sales Receipt using QODBC?. QuickBooks Sales Receipts are essentially a invoice with the payment in the one transaction. After that you just need to do the banking reconcilation. I suggest you do the process manually using the QuickBooks User Interface first and then lookup the QODBC processes for those steps to automate them.

If you want to bank the money first and then raise the invoice, do the: How to I handle Customer credits on account? procedure instead.

OK, I have a different question now...

When pulling our SalesReceiptLine table... the QODBC just sits there reading the table for over 1hr... I have tried to speed this process up by only pulling 9 fields from it, and also narrowing by the TimeModified column... but even using all of this to speed it up, this doesn't help the initial "Load time" for it to read what is in the SalesReceiptLine (I am assuming this is what its doing)... The Driver Status panel shows that it is "Waiting On QuickBooks" for about half of the time, and then it shows "Find First Record" the other half. With my narrowed query, i pull the data a lot faster after it finally finds the first record... but even if i was just pulling 1 record this would take over an hour. Is there something i am doing wrong?

I am only pulling these Fields: SalesReceiptLineItemRefFullName, SalesReceiptLineQuantity, SalesReceiptLineRate, SalesReceiptLineAmount, TxnDate, RefNumber, PaymentMethodRefFullName, TimeCreated, TimeModified.

Please let me know if there is anything that would help to make this run faster... This seems to be getting worse the more transactions we get in the SalesReceiptLineTable, but there needs to be a way around it as no new SalesReceipts means no more business.

Thank you for your help

Ben

Your SELECT statements needs a WHERE clause to only extract what you need, for example, for Sale Receipts this month:

select * from SalesReceiptLine  WHERE Txndate >= {d '2007-12-01'}

or for a particular transaction:

select * from SalesReceiptLine  WHERE TxnID = '36F4-1071511667'

Ok, new problem... The Where Clause makes the query go plenty fast when i hand input the range (eg. WHERE TxnDate >= {d '2007-12-01'}, but when i use a function to try and pull the date dynamicly, it goes back to the old slow speed... I also tested this in VBDemo to just SELECT {fn CURDATE()}  as Date FROM SalesReceiptLine, which pulled a new line of today's date for every line in the table (apprx. 120,000). So it seems to me like the CURDATE function would not make a very good where clause if i were to speculate. Is there a workaround for this? I have come up with an idea for one.. but i'm not sure if it will work. Does QODBC support nested queries? I was thinking something like this would speed it up: SELECT * FROM SalesReceiptLine WHERE TxnDate >= (SELECT Top 1 {fn CURDATE()} FROM SalesOrder)... or from any other table with very few records so that i doesnt need to look through a lot of them.

Any help on this would be appreciated

Thank you for your time

Ben

Just use:

select * from SalesReceiptLine  WHERE Txndate >= {fn CURDATE()}

Note: You must also use QODBC v8.00.00.240 (or higher), see: Release 8.0.0.235 Fixes

I've tried that, and thats the new problem that i'm having... When i hand input the parameters, it takes something like 30seconds for the "Waiting On QuickBooks" and then another 30 seconds to "Find First Record"... then it pulls all of the applicable. But for some reason when i use the function, it takes somewhere around 10mins for each, and then pulls them. I would assume that means that it is something with the function CURDATE().. Is there a way to speed up the initial time to "Wait On Quickbooks" and "Find First Record"?

Thanks
Ben
BTW... I'd like to thank you for all of your help with this project, It is a very nice thing to have somebody to go to that has the answers :)

Yes, do:

select * from SalesReceiptLine  NOSYNC WHERE Txndate >= {fn CURDATE()}

Nothing seemed to change... My time estimates in my last post were off though... When hand inputting, its still around 30 seconds, but with the CURDATE() Its 20-30mins just "Waiting On QuickBooks". I am not using the optimizer, i was under the impression that the NOSYNC was to flag to not use the data from the optomizer, but to pull straight from QB... somewhat like UNOPTIMIZED.

Any other suggestions?

Thanks,
Ben

Sorry if you don't use the QODBC optimizer, then you're going have to put up with: "Waiting On QuickBooks".  We gave up on QuickBooks three years ago and put the optimizer in the QODBC v6 release. The QODBC optimizer is a desktop SQLite engine.

I suggest you look at: How do I setup the QODBC Optimizer? Where are the Optimizer options?  The QODBC optimizer has brought operations that took over three days for some customers down to 30 minutes.

Ok,
When we insert our SalesReceipts, the Account that its deposited to is an Undeposited funds account. From there we take our statement and match up the totals and the RefNumbers and then we deposit it to our regular account. I think i have figured out the logic of how this is handled by QB, but i can't seem to make it work. From everything that i think i've figured out to here it seems that all i would need to do to take this transaction from the undeposited funds account to our normal account, it seems that i simply need to insert into the DepositLine Table with the values that are show as required by this table here: http://doc.qodbc.com/qodbc/20/tables/table_detail_us7a13.html?details_id=28&tn_us=TRUE.

The problem i'm having with my logic is that when i run the insert query, no matter if its from VBDemo or SQL... it runs successfully, but nothing happens. No funds are transferred and nothing is shown in my deposit detail report.

Please tell me if i am on the right track and/or what i'm doing wrong.

Thank you,
Ben

OK,
I have the logic figured out to be able to insert 1 Deposit, but for some reason it won't let me do multiples. I have a Query that runs and puts all of my data into a table. From there i have a .dts package that runs to insert the data into QB. This works perfect with everything set for just 1 deposit, with FQSaveToCache set to 0. Now when i try and insert a muli-line deposit, with FQSaveToCache = 1 on everything except the last line in my table which has the FQSavetoCache = 0 to commit the deposit. When i do this, the insert doesn't work. Do they have to be looped inserts 1 at a time? Or is there somethign that i am doing wrong? I am inserting these fields: DepositLinePaymentTxnID, DepositToAccountRefFullName, TxnDate and FQSaveToCache.

 I am sure that it is something small as the query works perfect for a 1 line deposit.
Thank you for your help!
Ben

Sorry about that,
I am afraid i may be attempting to insert this backwards as i can't seem to get the linked server figured out for SQL 2005. I am using an old SQL 2000 dts package to actually insert the values that i have set in my table. I have the values in the table set as follows:
DepositLinePaymentTxnID = TxnID From Matching ReceivePaymentToDeposit Record
DepositToAccountRefFullName = 'ABC Bank'
TxnDate = {d' 2007-12-31'}
FQSaveToCache = 0

When i run my query to populate the table, and i set it to insert just one deposit line... and then run my DTS package to insert, everything works fine. When i set my query to insert multiple lines (i tested with 2 lines) with FQsavetocache set to 1 for the first line, and set to 0 for the last... and then run my DTS package, the package runs with no errors, but nothing is recorded to QB.

I have tested various ways of trying to accomplish this... and the only one that seems to work successfully is to run 2 seperate insert statements in VBDemo. My train of thought from there is that if you can run 2 insert statments seperately (one after the other) then i could loop my dts package to just insert them one at a time. This again failed.

I hope that was what you were looking for

Thanks
Ben

Tom,
How would i go about purchasing phone support to get this issue resolved? I have way too much time involved in this already and i think its time to just pay for support to be able to have this resolved immediately. Is the phone support a number that i call, or is it on a call-back only basis?

Thank you for your help
Ben

SQL Server isn't the environment to test your insert statements, please use VBDemo to test the inserts manually first before attemping them in your DTS Package. This will allow you to see the errors easier. Go to QODBC Setup Screen, press the "Messages" tab page. Click on the "Review QODBC Messages" and "Review SDK Messages" and review messages produced by the QuickBooks qbXML SDK interface for a clearer idea on what you are doing wrong.

This isn't something that a callback can solve.

OK,
This is what my QODBC log is showing when i run the DTS package and have 2 records in my table... the first with FQSavetoCache = 1 and the last with 0. When i run these in VBDemo, everything works fine, but to make it work with a 2 line deposit i have to have 2 seperate insert statements. I'm not sure how i could accomplish the same effect with a dts package:

IsAService: True
SQL Statement: INSERT INTO "QODBC"."DepositLine" ( TxnDate,DepositToAccountRefFullName,Memo,DepositLinePaymentTxnID,DepositLineMemo,FQSaveToCache) VALUES(?,?,?,?,?,?)
Error parsing complete XML return string (8)
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="6.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
        <DepositAddRq requestID = "1">
            <DepositAdd defMacro = "TxnID:59C8A13A-DF4B-4A51-B525-4969D42E46CD">
                <TxnDate>2007-12-13</TxnDate>
                <DepositToAccountRef>
                    <FullName>Bank of Durango</FullName>
                </DepositToAccountRef>
                <Memo>Dec 13 2007 12:00AM</Memo>
                <DepositLineAdd>
                    <PaymentTxnID>84D62-1196795131</PaymentTxnID>
                    <Memo>103-9780843-3143452</Memo>
                </DepositLineAdd>
                <DepositLineAdd>
                    <PaymentTxnID>86653-1196883606</PaymentTxnID>
                    <Memo>002-7567052-0557016</Memo>
                </DepositLineAdd>
            </DepositAdd>
        </DepositAddRq>
    </QBXMLMsgsRq>
</QBXML>

Tom,
After running some more tests i believe i have the logic figured out. Is there a limit on how many lines can be in a deposit? Either from QB or from how many QODBC can handle with the FQSavetoCache method. This system i'm building is to import approx. 8000 deposits per batch, so i'd like to just have one lump deposit instead of little batches. Also, i'm sure that the only wait time in an insert statement would be QuickBooks lag, but is there anything i can do to speed up the process?

Thank you for all of your help,
Ben

OK,
After using the tools you mentioned below i have gotten my system to work somewhat. I have now tested up to 50 deposits at a time, but the problem i am having is this: When i insert certain records, i am getting this error message: "The Given Record Number Is Not In The Payments To Deposit List". To help to identify the problem, let me walk through the steps that i have in matching and selecting the TxnID.
1. Import the file of Transactions that have been deposited to our bank account (This is coming from outside, so these are what i am attempting to input via a program)
2. I pull the ReceivePaymentsToDeposit table from QB (I am only pulling the following fields: TxnID, CustomerRefFullName, Amount)
3. I pull our SalesReceiptLine Table (With the following Fields: RefNumber, CustomerRefFullName)
4. Now i match everything together with the following logic: Get the matching RefNumber from SalesReceiptLine WHERE CustomerRefFullName = ReceivePaymentToDeposit.CustomerRefFullName, and then match the refnumber out of the SalesReceiptLine to our first file that i imported (step 1.)
5. I now append that to a new table and include all of the required information for the import (DepositLinePaymentTxnID, DepositToAccountRefFullName, TxnDate and FQSaveToCache)

My question is, why is it giving me the error that the record number is not in the payments to deposit list? As i am pulling the ReceivePaymentsToDepositList Fresh before each attempt at inserting a deposit. Is there maybe another table involved? I will provide with the exact syntax used if needed, but hopefully you can help me from the breif summary provided.

Your prompt reply is appreciated,
Ben

Sorry, I'm completely at a loss why you are doing this. Normally the SalesReceipt insert will deposit the funds to the account you nominate (if doesn't have to be the Undeposited funds account) :-

insert into salesreceiptline (CustomerRefListID, TemplateRefListID, TxnDate,
IsPending, DueDate, IsToBePrinted, DepositToAccountRefListID,
BillAddressAddr1, BillAddressAddr2, BillAddressAddr3,
BillAddressCity, BillAddressState, BillAddressPostalCode,
SalesReceiptLineItemRefFullName, SalesReceiptLineDesc,
SalesReceiptLineRate, SalesReceiptLineAmount,
SalesReceiptLineSalesTaxCodeRefListID)
values ('AC0000-1197757899', '90000-933290726', {d'2006-05-25'},
0, {d'2006-05-25'}, 0, '80000-933270541','Joe Mama Inc 2',
'123 Main St.', 'Suite 450', 'Chicago', 'IL', '60602',
'Support Incident', 'Priority Support Incident 10 Pack Subscription',
275.00, 275.00, '20000-999022286')

Tom,
Sorry for the confusion. Here's what we have going on: When we insert our SalesReceipts, we insert them into an "Undeposited Funds" account. This is so that we can reconcile them to make sure that its really showing in our account before we deposit the transaction into our regular checking account. This is our double check to make sure that the funds we are showing in our checking account are the ones that have literally been deposited into our account.

After entering the SalesReceipts we would normally hand match the transactions with the ones from our Bank Statement and then deposit them into our Account. With the amount of transactions that we need to deposit, we are not making any headway. (we have approx 8000 transactions to deposit every 2 weeks).  To work with our accounting system, we need to have things work this way.

With that said, I have the system built to insert the deposits needed. I only have a few questions about what would work best for QODBC:
1. It seems from my testing that QODBC works best with smaller batches (25 to 50 lines at a time) is there any way to speed things up for larger batches? It would be the fastest to our accounting department if these were done in large batches.
2. When does the RecievePaymentsToDeposit Table get updated? This seems to be a table that is populated by SalesReceipts that are in our undeposited funds account. The question would be, do i need to run a Delete statement to clear the transactions that i've already deposited, or will it update its self after a given time?
3. I have noticed in my testing that i can't insert the DepositLineMemo, or the Memo field in DepositLine, is there a way to do this or am i just stuck with the defaults?

Thank you for your time and patience,
Ben

QODBC does support batches of up to 500 transactions at one time, see: Are there any special stored procedures that can assist me in programming? 

I've looked at both the Memo and DepositLineMemo columns in th DepositLine table and they are shown as insertable using QuickBooks 2007 or 2008.

The ReceivePaymentToDeposit table provides a list of Payments Received to Undeposited Funds, ready for Deposit and is a read only table.

As stated earlier, I don't have a QODBC procedure for banking reconcilations, but on seeing this, it looks like it's now possible. I will have to reseach this a little further and develop a tutorial for others to use.

Last question (at least a hope :-) ),
To run the stored procedures referenced in the link you provided... do you have to have a linked server? Or is there some way for me to trigger these from within an insert statment given by a dts package?

Thanks,
Ben