PurchaseOrders

Handles QuickBooks Purchase Orders, supporting creation, updates, deletion, and queries for supplier management.

Table Specific Information

Purchase orders may be inserted, queried, or updated via the PurchaseOrders or PurchaseOrderLineItems tables. PurchaseOrders may be deleted by using the PurchaseOrders table.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for PurchaseOrders are Id, Date, TimeModified, VendorName, and VendorId. TimeModified and Date may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. ReferenceNumber may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM PurchaseOrders WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'

Insert

To add a PurchaseOrder, specify the Vendor and at least one Line Item. The ItemAggregate columns may be used to specify an XML aggregate of Line Item data. The columns that may be used in these aggregates are defined in the PurchaseOrderLineItems table and it starts with Item. For example, the following will insert a new PurchaseOrder with two Line Items:

INSERT INTO PurchaseOrders (VendorName, ItemAggregate) 
VALUES ('A Cheung Limited', 
'<PurchaseOrderLineItems>
<Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row>
<Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row>
</PurchaseOrderLineItems>')

To insert subitems, set the ItemName field to the FullName of the item; for example, '<Row><ItemName>Subs:Carpet</ItemName><ItemQuantity>0</ItemQuantity></Row>'

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	A unique identifier for the purchase order, formatted as PurchaseOrderId, used to track this transaction in the system.
VendorName	String	False		1000	The name of the vendor to whom this purchase order is issued. Either VendorName or VendorId must be provided when inserting.
VendorId	String	False	Vendors.ID	255	The QuickBooks ID of the vendor to whom this purchase order is issued. Either VendorId or VendorName must be provided when inserting.
VendorMessage	String	False		99	A message or note included for the vendor, typically instructions or additional information.
ReferenceNumber	String	False		21	The reference number for the transaction, used for tracking or cross-referencing purposes.
TxnNumber	Integer	True			A unique transaction number, separate from the Quickbooks generated ID, for additional identification.
Date	Date	False			The date when the purchase order was created or recorded.
DueDate	Date	False			The date by which payment for this purchase order is due.
ShipMethod	String	False		1000	The method of shipping for this purchase order, such as 'Ground' or 'Air'.
ShipMethodId	String	False	ShippingMethods.ID	255	The QuickBooks ID associated with the selected shipping method.
ExpectedDate	Date	False			The date when the items on the purchase order are expected to arrive.
Memo	String	False		5000	A memo or note related to the purchase order, used for internal tracking or documentation.
Class	String	False		1000	The class assigned to this transaction, typically for categorization or reporting purposes.
ClassId	String	False	Class.ID	255	The QuickBooks ID of the class assigned to this transaction.
Terms	String	False		100	The payment terms for this purchase order, such as 'Net 30' or 'Due on Receipt'.
TermsId	String	False		255	The QuickBooks ID of the payment terms applied to this transaction.
TotalAmount	Decimal	True			The total amount for this purchase order, including all items and taxes.
Template	String	False		100	The name of a template applied to this purchase order for formatting or customization.
TemplateId	String	False	Templates.ID	255	The QuickBooks ID of the template applied to this purchase order.
CurrencyName	String	False		64	The name of the currency used for this purchase order. Requires QBXML version 8.0 or higher.
CurrencyId	String	False	Currency.ID	255	The QuickBooks ID of the currency used for this purchase order. Requires QBXML version 8.0 or higher.
ItemCount	Integer	True			The number of line items included in this purchase order.
ItemAggregate	String	False		5000	A representation of all line-item data for adding purchase orders with their items.
IsFullyReceived	Boolean	True			Indicates if all items in the purchase order have been received and no items are manually closed.
IsManuallyClosed	String	False		10	Specifies whether the purchase order is manually closed.
IsToBePrinted	Boolean	False			Indicates if the purchase order is marked for printing.
IsToBeEmailed	Boolean	False			Indicates if the purchase order is marked to be emailed.
IsTaxIncluded	Boolean	False			Determines if the line item amounts include tax.
SalesTaxCodeName	String	False		3	The name of the sales tax code applied to this purchase order.
SalesTaxCodeId	String	False	SalesTaxCodes.ID	255	The QuickBooks ID of the sales tax code applied to this purchase order.
FOB	String	False		1000	Freight on board: the location from which the goods are shipped.
VendorAddress	String	True			The complete address of the vendor as recorded in QuickBooks.
VendorLine1	String	False		500	The first line of the vendor's address.
VendorLine2	String	False		500	The second line of the vendor's address.
VendorLine3	String	False		500	The third line of the vendor's address.
VendorLine4	String	False		500	The fourth line of the vendor's address.
VendorLine5	String	False		41	The fifth line of the vendor's address.
VendorCity	String	False		255	The city in the vendor's address.
VendorState	String	False		255	The state in the vendor's address.
VendorPostalCode	String	False		30	The postal code in the vendor's address.
VendorCountry	String	False		255	The country in the vendor's address.
VendorNote	String	False		41	A note associated with the vendor's address for additional context.
ShipToEntityName	String	False		1000	The name of the entity (customer, vendor, or employee) to whom the shipment is directed.
ShipToEntityId	String	False		255	The QuickBooks ID of the entity (customer, vendor, or employee) to whom the shipment is directed.
ShippingAddress	String	True			The complete shipping address returned by QuickBooks.
ShippingLine1	String	False		500	The first line of the shipping address.
ShippingLine2	String	False		500	The second line of the shipping address.
ShippingLine3	String	False		500	The third line of the shipping address.
ShippingLine4	String	False		500	The fourth line of the shipping address.
ShippingLine5	String	False		41	The fifth line of the shipping address.
ShippingCity	String	False		255	The city in the shipping address.
ShippingState	String	False		255	The state in the shipping address.
ShippingPostalCode	String	False		30	The postal code in the shipping address.
ShippingCountry	String	False		255	The country in the shipping address.
ShippingNote	String	False		41	A note associated with the shipping address for additional context.
ExchangeRate	Double	False			The exchange rate used to convert the transaction's currency to the home currency.
Other1	String	False		25	A predefined custom field from QuickBooks for additional information.
Other2	String	False		29	Another predefined custom field from QuickBooks for supplementary details.
CustomFields	String	False			Custom fields formatted as XML and returned by QuickBooks.
EditSequence	String	True			A version identifier used for tracking changes to this purchase order.
TimeModified	Datetime	True			The date and time when the purchase order was last updated in QuickBooks.
TimeCreated	Datetime	True			The date and time when the purchase order was created in QuickBooks.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.