To understand the process for everyone, I will do a simple one line estimate example, make it a Sales Order and then make it into a Invoice.

FIRST TO CREATE THE ESTIMATE
The following SQL statement will create a new estimate:

INSERT INTO "EstimateLine" ("CustomerRefListID", "RefNumber", "EstimateLineItemRefListID",
"EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('470001-1071525403', '201', '250000-933272656',
'Building permit No 201', 100.00000, 100.00, '20000-999022286', 0)

which results in the following estimate in the QuickBooks 2006 Premier USA Edition - Sample Rock Castle Construction company file:

NEXT, TO CREATE  A SALES ORDER FROM A ESTIMATE
When we're ready to create the Sales Order we can read the EstimateLine table and insert it into the SalesOrderLine table like this:

INSERT INTO "SalesOrderLine" ("CustomerRefListID", "RefNumber", "Memo", "SalesOrderLineItemRefListID",
"SalesOrderLineDesc", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID",
"FQSaveToCache")
Select "CustomerRefListID", "RefNumber", {fn CONCAT('Estimate ', "RefNumber")} as "Memo","EstimateLineItemRefListID",
"EstimateLineDesc", "EstimateLineRate", "EstimateLineAmount", "EstimateLineSalesTaxCodeRefListID",
"FQSaveToCache" from EstimateLine where "RefNumber"='201' and "EstimateLineSeqNo"=1

Note: This is one complete SQL statement, for multiple estimate lines you would set FQSaveToCache to 1 instead (using: 1 as "FQSaveToCache") and loop the EstimateLineSeqNos until the last one setting FQSaveToCache to 0.

The Estimate now appears as a Sales Order in QuickBooks with "Estimate 201" in the memo field the same way QuickBooks creates a Sales Order from a Estimate.

NOW LET'S CREATE  A INVOICE  FROM THE SALES ORDER !
When we're ready to create the Invoice we can read the SalesOrderLine table and insert it into the InvoiceLine table like this:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", "Memo", "InvoiceLineItemRefListID",
"InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache")
Select "CustomerRefListID", {fn CONCAT('I', "RefNumber")} as "RefNumber",
{fn CONCAT('Sales Order ', "RefNumber")} as "Memo","SalesOrderLineItemRefListID",
"SalesOrderLineDesc", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID",
"FQSaveToCache" from SalesOrderLine where "CustomerRefFullName" ='Lamb, Brad:Room Addition' and "RefNumber"='201'
and "SalesOrderLineSeqNo"=1

Note: This is one complete SQL statement, for multiple estimate lines you would set FQSaveToCache to 1 instead (using: 1 as "FQSaveToCache") and loop the SalesOrderLineSeqNos until the last one setting FQSaveToCache to 0.

The Sales Order now appears as a Invoice in QuickBooks but is unlinked to the Sales Order line at this stage.

NOW LET'S CREATE  A LINKED INVOICE  FROM THE SALES ORDER !
If you are using QuickBooks 2007 (USA version) you can now create linked Sales Order invoices. When we're ready to create the Invoice we can read the SalesOrderLine table and insert it into the InvoiceLine table as a linked Invoice instead like this:

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineLinkToTxnTxnID", "InvoiceLineLinkToTxnTxnLineID",
"FQSaveToCache")
Select "CustomerRefListID", {fn CONCAT('SOLink', "RefNumber")},
"TxnID", "SalesOrderLineTxnLineID",
0 as "FQSaveToCache" from SalesOrderLine
where "CustomerRefFullName" ='Lamb, Brad:Room Addition' and "RefNumber"='201'
and "SalesOrderLineSeqNo"=1

Note: This is one complete SQL statement, for multiple estimate lines you would set FQSaveToCache to 1 instead (using: 1 as "FQSaveToCache") and loop the SalesOrderLineSeqNos until the last one setting FQSaveToCache to 0.

which results in the following linked Invoice in the QuickBooks 2007 Premier USA Edition - Sample Rock Castle Construction company file:

and because there was only one line in the example Sales Order, the Sales Order has also been marked, "INVOICED IN FULL".

How can we request this feature to be added to qbXML please? It is crucial for our integration efforts. If you have already requested for this feature addition, when can we expect it? Is there any estimated date of release please? Thank you. Piyush

It's now confirmed that the ability to create Invoices (InvoiceAdd) from Sales Orders will be available in the USA Editions of QuickBooks 2007. 

See: QuickBooks SDK 6.0 for U.S. editions of QuickBooks 2007 for more information.

Since I only have the QuickBooks Pro 2008 version, I don't have access to Sales Orders.

Studying the NOW LET'S CREATE  A LINKED INVOICE  FROM THE SALES ORDER !  can I correctly assume that if I add the sales order number to the InvoiceLineLinkToTxnTxnLineId field during the InvoiceLine insert, (since that field is not updateable) that the SO will be linked to Invoice?

I guess there is no way that I can test this <groan>.

Thanks for your assistance.

Jay Talbott

As you don't have access to Sales Orders using QuickBooks Pro 2008, just create the invoice!

See: How do I create Invoices?