I have a loop of various queries that are used to insert orders into quickbooks --- however, some queries are not used on all jobs so I am not always sure where the last insert query will be.

Thanks,

Tom Komin

This example creates one Sales Order with 2 order lines. Note how the FQSaveToCache field is set to True except on the last line. Setting FQSaveToCache to 0 will force the driver to submit the inserts for a Sales Order into QuickBooks.

INSERT INTO "SalesOrderLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "SalesOrderLineItemRefListID", "SalesOrderLineDesc", "SalesOrderLineQuantity", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID","FQSaveToCache") VALUES ('120000-1045537156','C0000-1080110273','1015', '90000-1045537150', '4m Steel Ladder', 1.00000, 1.00000, 150.00, '90000-1045536338', 1)

INSERT INTO "SalesOrderLine" ("CustomerRefListID", "TemplateRefListID","RefNumber", "SalesOrderLineItemRefListID", "SalesOrderLineDesc", "SalesOrderLineQuantity", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID","FQSaveToCache") VALUES ('120000-1045537156','C0000-1080110273','1015', '150001-1045625669', 'Paint Brush:Big', 1.00000, 1.00000, 11.60, '90000-1045536338', 0)

Where:
CustomerRefListID is the existing Customer ListID
found by using "Select * from Customer"
TemplateRefListID is the Sales Order Template ListID
found by using "Select * from Template"
SalesOrderLineItemRefListID is the existing Inventory Item ListIDs
found by using:

In my programming, I don't know which line item will be the last. I have 3 different types of services that it needs to bill for on an invoice but some use the first two but not the last and others use just the 2nd one. I can't use the "FQSaveToCache" set to 0 because I don't know if the insert in going to be used or not. Is there a SQL command to force the driver to submit the insert without adding an additional one or can I add a blank line item that has the FQSaveToCache set to 0 --- if yes, how should the blank line be coded.

Thanks,

Tom

Ok, I gather you have if.... else... conditional statements around which invoiceline you will execute. So the simple answer is to do the invoice header insert at the end, which will force the driver to submit the inserts for the Invoice into QuickBooks.

Here we create an invoice with 3 lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 1', 1.00000,
1.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 2', 2.00000,
2.00, '20000-999022286', 1)

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000,
3.00, '20000-999022286', 1)

INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID", "TxnDate",
"RefNumber", "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity",
"BillAddressState", "BillAddressPostalCode", "BillAddressCountry",
"IsPending", "TermsRefListID", "DueDate", "ShipDate", "ItemSalesTaxRefListID",
"Memo", "IsToBePrinted", "CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403',
'40000-933270541', {d'2002-10-01'}, '1', 'Brad Lamb', '1921 Appleseed
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '10000-933272658',
{d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test', 0,
'10000-999022286')

Is the invoice header necessary in other cases? I seem to be getting my other invoices into quickbooks fine without making a "Insert into Invoices..." query?  I can see the reason for using it to submit the inserts into quickbooks but is it necessary.

Tom

One last question --- hopefully. What is the minimum fields that are required for the Invoice Insert command? I would only like to send the minimum info that is required?

This would be the minimum:

INSERT INTO "Invoice" ("CustomerRefListID") VALUES ('470001-1071525403')