PurchaseOrderLineItems

Manages individual line items in QuickBooks Purchase Orders, supporting creation, updates, deletion, and queries for procurement tracking.

Table Specific Information

PurchaseOrders 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, ReferenceNumber, 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 PurchaseOrderLineItems 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. All Line Item columns and can be used for inserting multiple Line Items for a new PurchaseOrder transaction. For example, the following will insert a new PurchaseOrder with two Line Items:

INSERT INTO PurchaseOrderLineItems#TEMP (VendorName, ItemName, ItemQuantity) VALUES ('A Cheung Limited', 'Repairs', 1)
INSERT INTO PurchaseOrderLineItems#TEMP (VendorName, ItemName, ItemQuantity) VALUES ('A Cheung Limited', 'Removal', 2)
INSERT INTO PurchaseOrderLineItems (VendorName, ItemName, ItemQuantity) SELECT VendorName, ItemName, ItemQuantity FROM PurchaseOrderLineItems#TEMP

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	A unique identifier for the line item, formatted as PurchaseOrderId\|ItemLineId, providing specific reference to each line item within a purchase order.
PurchaseOrderID	String	False	PurchaseOrders.ID	255	The unique identifier of the purchase order to which this line item belongs.
VendorName	String	False		1000	The name of the vendor for this purchase order. Either VendorName or VendorId must be provided when adding a new purchase order.
VendorId	String	False	Vendors.ID	255	The QuickBooks ID of the vendor for this purchase order. Either VendorId or VendorName must be provided when adding a new purchase order.
VendorMessage	String	False		99	A custom message or instructions included for the vendor, such as shipping or delivery notes.
ReferenceNumber	String	False		21	The reference number for tracking or identification of the transaction associated with this purchase order.
TxnNumber	Integer	True			A unique transaction number assigned by QuickBooks, separate from the system-generated ID.
Date	Date	False			The creation date of the transaction. Overwrites pseudo-columns StartDate and EndDate if used in a WHERE clause.
DueDate	Date	False			The date when payment for the purchase order is due.
ShipMethod	String	False		1000	The shipping method specified for the purchase order, such as 'Ground' or 'Air'.
ShipMethodId	String	False	ShippingMethods.ID	255	The QuickBooks ID corresponding to the selected shipping method.
ExpectedDate	Date	False			The anticipated arrival date for the shipment.
Memo	String	False		5000	Notes or comments related to this transaction, typically for internal use.
Class	String	False		1000	The classification of this transaction for categorization or reporting purposes.
ClassId	String	False	Class.ID	255	The ID of the class assigned to this transaction for categorization or reporting.
Terms	String	False		100	The payment terms associated with this purchase order, such as 'Net 30'.
TermsId	String	False		255	The QuickBooks ID for the payment terms associated with this transaction.
TotalAmount	Decimal	True			The total monetary value of the purchase order.
Template	String	False		100	The name of a pre-existing template applied to this transaction for formatting or standardization.
TemplateId	String	False	Templates.ID	255	The QuickBooks ID of the template applied to this transaction.
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 ID of the currency used for this purchase order. Requires QBXML version 8.0 or higher.
CustomFields	String	False			Custom fields associated with the purchase order, returned as formatted XML.
ItemLineId	String	True		255	A unique identifier for this specific line item within the purchase order.
ItemLineNumber	String	True		255	The sequential number of the line item within the purchase order.
ItemName	String	False			The name of the item being ordered.
ItemId	String	False	Items.ID	255	The QuickBooks ID of the item being ordered.
ItemGroup	String	False		100	The name of the item group, if this item is part of a predefined group of line items.
ItemGroupId	String	False	Items.ID	255	The QuickBooks ID of the item group, if this item is part of a predefined group of line items.
ItemDescription	String	False		5000	A descriptive note about the item being ordered.
ItemCustomer	String	False		1000	The name of the customer or job associated with this item, if applicable.
ItemCustomerId	String	False	Customers.ID	255	The QuickBooks ID of the customer or job associated with this item, if applicable.
ItemUnitOfMeasure	String	False		31	The unit of measure for the item, selected from the item's predefined units. Requires QBXML version 7.0 or higher.
ItemQuantity	Double	False			The number of units of the item being ordered.
ItemRate	Double	False			The unit price or rate for the item being ordered.
ItemAmount	Decimal	False			The total monetary value for this line item.
ItemReceivedQuantity	Double	False			The number of units of the item that have already been received against this purchase order.
ItemClass	String	False		1000	The classification of the item for categorization or reporting purposes.
ItemClassId	String	False	Class.ID	255	The QuickBooks ID of the class assigned to the item.
ItemInventorySiteLocationId	String	False		31	The ID of the inventory site location where the item is stored. Requires QBXML version 10.0 and Advanced Inventory.
ItemInventorySiteLocationName	String	False		255	The name of the inventory site location where the item is stored. Requires QBXML version 10.0 and Advanced Inventory.
ItemIsManuallyClosed	String	False		10	Indicates whether the item line is manually closed (no longer active).
ItemPartNumber	String	False			The manufacturer's part number for the item.
ItemServiceDate	Date	False			The date when the service related to the item is performed.
ItemOther1	String	False		29	Additional custom field for the item. Requires QBXML version 6.0 or higher.
ItemOther2	String	False		29	Another custom field for the item. Requires QBXML version 6.0 or higher.
ItemCustomFields	String	False			Custom fields specific to this line item, returned as formatted XML.
IsFullyReceived	Boolean	True			Indicates whether all items in the purchase order have been received.
IsManuallyClosed	String	False		10	Indicates whether the entire purchase order is manually closed (no longer active).
IsToBePrinted	Boolean	False			Specifies whether the purchase order is marked for printing.
IsToBeEmailed	Boolean	False			Specifies whether the purchase order is marked for emailing.
IsTaxIncluded	Boolean	False			Indicates whether the amounts in the line items 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 ID of the sales tax code applied to this purchase order.
FOB	String	False		1000	The freight-on-board location indicating the shipping point or destination.
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 of the vendor's address.
VendorState	String	False		255	The state of the vendor's address.
VendorPostalCode	String	False		30	The postal code of the vendor's address.
VendorCountry	String	False		255	The country of the vendor's address.
VendorNote	String	False		41	Additional notes or comments about the vendor.
ShipToEntityId	String	False		255	The ID of the entity (customer, vendor, or employee) receiving the shipment.
ShipToEntityName	String	False		1000	The name of the entity (customer, vendor, or employee) receiving the shipment.
ShippingAddress	String	True			The complete shipping address as recorded in 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 of the shipping address.
ShippingState	String	False		255	The state of the shipping address.
ShippingPostalCode	String	False		30	The postal code of the shipping address.
ShippingCountry	String	False		255	The country of the shipping address.
ShippingNote	String	False		41	Additional notes or comments about the shipping address.
ExchangeRate	Double	False			The currency exchange rate applicable to this transaction.
Other1	String	False		25	A predefined custom field available for QuickBooks users.
Other2	String	False		29	Another predefined custom field available for QuickBooks users.
EditSequence	String	True		16	A unique identifier for tracking the version of this record.
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 initially 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.