Knowledgebase: Syntax
[QODBC-Desktop] How to create a Purchase Order using QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

An Example of creating a PurchaseOrder

Note: This example creates one PurchaseOrder with three lines. Note the FQSaveToCache field, set to TRUE (1) except on the last line.

First Line

     INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
     "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
     "PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
     "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
     "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656',
     'See Attached 1', 1.0, 1.0, 1.11, '580000-1071526281', 1)


Note: 
 VB DEMO is to be used for testing of QODBC SQL queries only and is not a development tool.

Second Line

     INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
     "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
     "PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
     "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
     "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656',
     'See Attached 2', 2.0, 2.0, 2.22, '580000-1071526281', 1)

Third (Last) Line

     INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber",
     "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc",
     "PurchaseOrderLineQuantity", "PurchaseOrderLineRate",
     "PurchaseOrderLineAmount", "PurchaseOrderLineCustomerRefListID",
     "FQSaveToCache") VALUES ('10000-933272655', '1', '250000-933272656',
     'See Attached 3', 3.0, 3.0, 3.33, '580000-1071526281', 0)

Results in QuickBooks

     This results in the following Purchase Order being created by QODBC using the QuickBooks 2006 Premier USA Editionand the Sample Rock Castle Construction company file as an example:

Related Data Description

VendorRefListID is the ListID for the Vendor found by doing :

     Select * from Vendor

PurchaseOrderLineItemRefListID is the existing Inventory Item ListID foundby using:

    Select ListID, FullName, Description, Type FROM Item

PurchaseOrderLineCustomerRefListID is the ListID for the customer found by doing :

     Select * from Customer

 

Note: If you want to create Purchase Order without using CustomerRefListID, then you can remove it from your query because it is optional. Please refer below sample query:

  INSERT INTO "PurchaseOrderLine" ("VendorRefListID", "RefNumber", "PurchaseOrderLineItemRefListID", "PurchaseOrderLineDesc", "PurchaseOrderLineQuantity", "PurchaseOrderLineRate", 
     "PurchaseOrderLineAmount", "FQSaveToCache") 
VALUES ('10000-933272655', '1', '250000-933272656', 'See Attached 1', 1.0, 1.0, 1.11,0)

 

Insert into PurchaseOrder with All Shared Header Information

Note: The FQSaveToCache field, set to True on all the PurchaseOrderLines. Saving the Purchase Order header saves the lines with it. Here is an example creates one Purchase Order with three lines.

First Line

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 1', 1.0, 1.0, 1.11, '580000-1071526281', 1)

Second Line

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-933272656', 'See Attached 2', 2.0, 2.0, 2.22, '580000-1071526281', 1)

Third Line

INSERT INTO "PurchaseOrderLine" ("PurchaseOrderLineItemRefListID",
"PurchaseOrderLineDesc", "PurchaseOrderLineQuantity",
"PurchaseOrderLineRate", "PurchaseOrderLineAmount",
"PurchaseOrderLineCustomerRefListID", "FQSaveToCache") VALUES
('250000-9332 72656', 'See Attached 3', 3.0, 3.0, 3.33, '580000-1071526281', 1)

Shared Header Information

INSERT INTO "PurchaseOrder" ("VendorRefListID", "RefNumber", "Memo",
"IsToBePrinted") VALUES ('10000-933272655', '1', 'Memo Test', 0)

(137 vote(s))
Helpful
Not helpful

Comments (2)
Ken Lyle
31 July 2014 06:04 PM
This post rocks! This is precisely what I was looking for today.
dale jones
18 January 2017 03:10 PM
Agreed - Just got the task of integrating our Access/VBA app to QuickBooks and this will save me hours of development and testing to get the right fields. Excellent example with a real world approach. Thank you
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).