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 I need to post into QB.
Using an Append Query, I have been able to upload Invoices successfully. However, all the line items show up on one invoice, and the first customer appears in the invoice header info even though there are four customers.
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 create multiple invoices with multiple line items?
Note: The primary rule is first to save the data to the child record. The child record for each parent/child pair has all the data required by the parent record. Please do not close the connection between the Insert of Child / Header & sp_lastinsertID; otherwise, you will not be able to get the 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 ended 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 one is created. I'm trying to develop Multiple Invoices with Multiple Line Items in each invoice.
Solutions 2 - Syntax Related
When we create an invoice with three 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 invoices and lines in an invoice, you need to look 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 four 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
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 to 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; the other time, you will get one 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 the following:
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 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 by 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
|