First you need to create the Invoice or locate the invoice you want to add lines too, like this:
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)
Once the invoice is created, we can locate the TxnID using the QODBC stored procedure (as long as we haven't broken our QODBC connection):
SP_LASTINSERTID InvoiceLine
Or if the Invoice already exists, or we want to find it later on, you can locate the TxnID by finding by the RefNumber:
SELECT TxnID from Invoice where RefNumber='QODBCClass2'
The TxnID for the invoice is 5CA9-1197764583, so we can now add additional lines to the Invoice by doing:
INSERT INTO "InvoiceLine" ("TxnID", "ClassRefListID", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID") VALUES ('5CA9-1197764583', '60000-933272658', '250000-933272656', 'Bin Permit Renovations', 200.00000, 200.00, '20000-999022286')
INSERT INTO "InvoiceLine" ("TxnID", "ClassRefListID", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID") VALUES ('5CA9-1197764583', '', '250000-933272656', 'Less Council Rebate', -50.00000, -50.00, '20000-999022286')
The invoice now appears in QuickBooks with different Class values for each line:
See also: Do you have an example on how to modify existing invoices and add lines also? |