Query1 and then Query2 must be run without breaking the QODBC connection. If you run each query, for example, in MS Access, each query will execute as a seperate connection. That's why Query2 fails with 'ODBC Call Failed'. Some products like FileMaker also execute a seperate connection when each query is executed.
To get around this you can create the Invoice in one query and then add additional invoice lines using seperate connections later on, 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 use were you have broken your QODBC connection:
SELECT TOP 1 TxnID from INVOICE where CustomerRefListID='470001-1071525403' order by TIMECREATED desc
The TxnID for the new 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 three lines:
|
