[QODBC-Desktop] How to create a Sales Order using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM
An Example of Creating a Sales Order
Note: This example creates one Sales Order with two order lines. Note how the FQSaveToCache field is set to True except on the last line.
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)
Second (Last) Line
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)
Related Data Description
CustomerRefListID is the existing Customer ListID found by using the following:
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:
Sample Data Description for SalesOrderLineItemRefListID
In my case, my Australian QuickBooks 2004 sample file returned the following items:
In the Sales Order inserts above, I used the following Inventory Items:
Description: "4m Steel Ladder" ListID: '90000-1045537150' (in Row 17 above) and
SalesOrderLineSalesTaxCodeRefListID is the SalesTaxCode ListID found by using:
Select * from SalesTaxCode
Note: Outside USA, SalesOrderLineSalesTaxCodeRefListID is SalesOrderLineTaxCodeRefListID instead to make as follows.
Select * from TaxCode
I would like to inform you that SalesOrderLine table is a child table SalesOrder table. When you add/update records in SalesOrderLine table it will automatically add/update to SalesOrder table. So you need to add/update InvoiceLine table.
Did I ran those two lines incorrectly?
Multi-line query require a series of SQL statements to complete a single Transaction.
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.
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.
The “FQSaveToCache” should be 1 or 0. For multiple lines insert you sould set FQSaveToCache to 1 instead (using: 1 as "FQSaveToCache") and loop the query until the last one setting FQSaveToCache to 0. For single line insert, if you want to use “FQSaveToCache”, you should set it to 0.