Knowledgebase
[QODBC-Desktop] How to create Multiple Invoices with Multiple Line Items using Access 2003 and QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

Troubleshooting: How to create Multiple Invoices with Multiple Line Items using Access 2003 and QODBC

 

Problem Description 1

     I use MS Access 2003 to generate calculated totals for invoices that I need to post into QB.

     Using an Append Query I have been able to successfully upload Invoices, however, all the line items show up on one invoice and the first customer appears in the invoice header info even though there are 4 customers total.

     I have one Append Query that uploads the Line Items to the InvoiceLine Table and a second Append Query that uploads the Header information to the Invoice table (which I receive an error when I run it but the invoice turns up in QB anyway). I'm using an Auto number field to create the Invoice Number (RefNumber) - could the error be because they are different data types?

     What do I have to do to create multiple invoices with multiple line items?

Note: The primary rule is to save the data to the child record first. The child record for each parent/child pair has all of the data required by the parent record included in it.
  Please do not the close connection between the Insert of Child / Header & sp_lastinsertID otherwise, you will not be able to get last inserted ID.

Solutions 1 - Tutorial about Creating Invoices

     Try looking at the links below:

     Inserting invoice lines built from external data using MS Access

     How to create Invoices using QODBC

     A detailed tutorial on how to create a multi-line invoice

Problem Description 2

     The first article looks like the invoice is already assigned a TxnID in QuickBooks and the second option has the steps I followed to create the invoice but I still end up with only one invoice with all the line items in it. My ListID fields are being taken directly from QuickBooks.

SQL View of the 1st Access Query I created (to append line items)

INSERT INTO InvoiceLine ( TxnDate, CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, FQSaveToCache, RefNumber )
SELECT [1-PostedInvoiceTotals].InvoiceDate, [1-PostedInvoiceTotals].CustomerRefListId, [1-PostedInvoiceTotals].InvoiceLineItemRefListID, [1-PostedInvoiceTotals].Name, [1-PostedInvoiceTotals].GrandTotal, 1 AS Qty, [1-PostedInvoiceTotals].FQSaveToCache, [1-PostedInvoiceHeaderTotals].InvoiceNumber
FROM [1-PostedInvoiceHeaderTotals] INNER JOIN (LocalCustomer INNER JOIN [1-PostedInvoiceTotals] ON LocalCustomer.ListID = [1-PostedInvoiceTotals].CustomerRefListId) ON [1-PostedInvoiceHeaderTotals].CustomerRefListId = [1-PostedInvoiceTotals].CustomerRefListId;

NOTE: CustomerRefListID should not be used here

Second Query (to post the Header Info):

INSERT INTO Invoice ( RefNumber, TxnDate, CustomerRefListID, CustomerRefFullName, ARAccountRefListID, BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressAddr4, BillAddressCity, BillAddressState, BillAddressPostalCode, ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressAddr4, ShipAddressCity, ShipAddressState, ShipAddressPostalCode )
SELECT [1-PostedInvoiceHeaderTotals].InvoiceNumber, [1-PostedInvoiceHeaderTotals].InvoiceDate, [1-PostedInvoiceHeaderTotals].CustomerRefListId, [1-PostedInvoiceHeaderTotals].CustomerRefFullName, [1-PostedInvoiceHeaderTotals].ARAccountRefListID, [1-PostedInvoiceHeaderTotals].BillAddressAddr1, [1-PostedInvoiceHeaderTotals].BillAddressAddr2, [1-PostedInvoiceHeaderTotals].BillAddressAddr3, [1-PostedInvoiceHeaderTotals].BillAddressAddr4, [1-PostedInvoiceHeaderTotals].BillAddressCity, [1-PostedInvoiceHeaderTotals].BillAddressState, [1-PostedInvoiceHeaderTotals].BillAddressPostalCode, [1-PostedInvoiceHeaderTotals].ShipAddressAddr1, [1-PostedInvoiceHeaderTotals].ShipAddressAddr2, [1-PostedInvoiceHeaderTotals].ShipAddressAddr3, [1-PostedInvoiceHeaderTotals].ShipAddressAddr4, [1-PostedInvoiceHeaderTotals].ShipAddressCity, [1-PostedInvoiceHeaderTotals].ShipAddressState, [1-PostedInvoiceHeaderTotals].ShipAddressPostalCode
FROM [1-PostedInvoiceHeaderTotals];

I WANT 4 invoices but in Quickbooks only 1 is created. I'm trying to create Multiple Invoices with Multiple Line Items in each invoice.

Solutions 2 - Syntax Related

     When we create an invoice with 3 lines we use 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true "1") followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately. The Invoice table only shows one row of each invoice. To see all the invoice lines and all the lines in an invoice you need to be looking at the InvoiceLine table.


Problem Description 3

     When I look at the InvoiceLine Table, QuickBooks has applied the first customer number to all of the Line Items, even though there were 4 different customer numbers (that match customer in QB) in the data. (I guess that's why I get only one invoice). How do I tell it to create one invoice per customer?

Solutions 3 - Sample Query

Example 1

     Simply do single line invoices as a single SQL INSERT with all the CUSTOMER detail and FQSaveToCache FALSE (0):

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ClassRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '30000-933272658', 'QODBCClass2', '250000-933272656',
'Building permit Additional Room', 100.00000, 100.00, '20000-999022286', 0)

    When FQSaveToCache is set false, or the header insert is executed (after lines have been cached), the cache lines of the invoice will get written.

Example 2

     The last example will give you 29 invoices, while the other time you got 1 invoice with four lines? So you need to check the FQSaveToCache logic in your append queries. For example for two invoices with two lines in each I do:

insert InvoiceLine FQSaveToCache 1 <<< Invoice 1 line 1
insert InvoiceLine FQSaveToCache 1 <<< Invoice 1 line 2
insert Invoice <<< header sets FQSaveToCache 0

Invoice 1 gets written to QuickBooks

insert InvoiceLine FQSaveToCache 1 <<< Invoice 2 line 1
insert InvoiceLine FQSaveToCache 1 <<< Invoice 2 line 2
insert Invoice <<< header sets FQSaveToCache 0

Invoice2 gets written to QuickBooks

See: Using ClassRefListID when inserting invoice lines for a better example and how to add a line to an existing invoice.

Additional Notes

     You can create any number of invoices you wish with a range of invoice lines using a sequence of SQL statements. You don't need to use the Invoice header and you can do your inserts just using the InvoiceLine table like this:

insert InvoiceLine FQSaveToCache 1<<< Invoice 1 line 1
insert InvoiceLine FQSaveToCache0 <<< Invoice 1 line 2

Invoice 1 gets written to QuickBooks

insert InvoiceLine FQSaveToCache 1 <<< Invoice 2 line 1
insert InvoiceLine FQSaveToCache 1 <<< Invoice 2 line 2
insert InvoiceLine FQSaveToCache 1 <<< Invoice 2 line 3
insert InvoiceLine FQSaveToCache0 <<< Invoice 2 line 4

Invoice2 gets written to QuickBooks

instead

Keywords: Using ClassRefListID when inserting invoice lines

(170 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).