A invoice line amount can be negative providing there are other Invoice lines that are positive and the Invoice itself is not set negative by the credit amount.
Multi-line invoices require a series of SQL statements to complete a single invoice. In the example below we are creating a 3 line invoice using three SQL INSERT commands that are run in sequence without breaking the QODBC connection. 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. In the sequence below, you should note that the value of "FQSaveToCache" is set to 1 or TRUE for the first two line item insert statements, and then it is set to 0 or FALSE for the final statement.
A TRUE setting of "FQSaveToCache" instructs QODBC to take the values from your INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 or FALSE, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection, and saved as a batch into QuickBooks.
QODBC maintains a connection for each application using the driver, and this cache is specific for each connection, so multiple applications or users of QODBC will not interfere with the cached transactions of other users. There is no set limit to the number of lines that can be cached for a single transaction, other than what QuickBooks would limit you to.
Since the data on the INSERT statement is being cached and not written to QuickBooks, some application tools (most notably Microsoft Access) will re-check that the data was saved properly to the target database by running a SELECT statement following a successful INSERT statement. In this example, this re-check will fail.
To get around this, use a pass-thru query (Append Query) and ignore any errors in the processing of the transaction, except for the last one. After the final line has been saved and the record is inserted into QuickBooks, then you can do your own validation by SELECTing the results of the Invoice number you just created in the Invoice and InvoiceLines tables to see that it was inserted correctly.
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', 'QODBCNeg1', '250000-933272656', 'Building permit Additional Room',
100.00000, 100.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', 'QODBCNeg1', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 1)
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', 'QODBCNeg1', '250000-933272656', 'Less Council Rebate',
-50.00000, -50.00, '20000-999022286', 0)
Once these three SQL INSERT commands have been run in sequence (without breaking the QODBC connection), QODBC will generate the following Invoice in the Sample Rock Castle Construction company file in the USA edition of QuickBooks 2006.
Note: See Line 3 of the invoice below for line item discount of $50.00 :-
To locate the ListIDs for the customer, item and salestaxcode to use in your own invoice, run these commands using VB Demo:-
For the CustomerRefListID:-
SELECT ListID, Type, FullName, IsActive FROM Entity
For the InvoiceLineItemRefListID:-
SELECT ListID, Type, FullName, IsActive FROM Item
For the InvoiceLineSalesTaxCodeRefListID:-
SELECT ListID, Name, "Desc", IsActive FROM SalesTaxCode
Please Note: All regions outside the United States should use TaxCode instead of SalesTaxCode.
|
