PurchaseOrders

Create, update, delete, and query QuickBooks POS Purchase Orders.

Table Specific Information

The PurchaseOrders table allows you to SELECT, INSERT, UPDATE, and DELETE Purchase Orders within QuickBooks POS.

Select

QuickBooks Point of Sale allows many of the columns to be used in the WHERE clause of a SELECT query. These columns can create either Single or Range filters, as defined in the table schema.

Single Filters

Single filters make direct comparisons by using the = comparison.

Range Filters

Range filters can search only ranges that have an inclusive lower bound, specified by the >= operator, and an exclusive upper bound, specified by the < operator.

To apply a single bound to a string column that has the Range filter type, the = or LIKE operators are available.

To apply a single bound to a numeric or datetime column that has the Range filter type, the >, >=, <, <=, or = operators are available.

SELECT * FROM PurchaseOrders WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert

To create a new Purchase Order record, the PurchaseOrderNumber and VendorListId fields are required. Items are specified via an XML aggregate within the ItemsAggregate column. The columns that may be used in these aggregates are defined in the PurchaseOrderItems table as # columns. Note that ItemListId is required when adding an item.

The following example will insert a new Purchase Order with two items:

INSERT INTO PurchaseOrders (PurchaseOrderNumber, VendorListId, ItemsAggregate) 
VALUES ('PO12345', '-9876543210987654321', '<PurchaseOrderItems>
<Row><ItemListId>-1000000000000000001</ItemListId><ItemCost>10.00</ItemCost><ItemQty>25</ItemQty></Row>
<Row><ItemListId>-1000000000000000002</ItemListId><ItemCost>20.00</ItemCost><ItemQty>25</ItemQty></Row>
</PurchaseOrderItems>')

Update

Any field that is not read-only can be updated.

When updating a Purchase Order record, items can be added or modified via the ItemsAggregate column.

To modify an existing item in a Purchase Order record, the ItemTxnLineId column is required to identify the item. In an update using an ItemsAggregate, specify all ItemTxnLineId values to be kept, as those values not specified will be deleted.

To add a new item, the ItemListId that corresponds to the item to be added must be specified, just as in an insert.

Note that items cannot be both modified and added in the same ItemsAggregate request. Modifying an item, using ItemTxnLineId, takes precedence over added items and thus QuickBooks POS ignores added items and performs the modification of only the existing items.

Modify Items Example: Total three items with ItemTxnLineId values of 1, 2, and 3. Item 1 is updated, Item 2 is left alone, and Item 3 will be deleted.

UPDATE PurchaseOrders SET ItemsAggregate='<PurchaseOrderItems>
<Row><ItemTxnLineId>1</ItemTxnLineId><ItemSize>L</ItemSize></Row>
<Row><ItemTxnLineId>2</ItemTxnLineId></Row>
</PurchaseOrderItems>'
WHERE TxnId='-1234567890123456789'

Add New Item Example: Existing items remain intact when adding new items.

UPDATE PurchaseOrders SET ItemsAggregate='<PurchaseOrderItems>
<Row><ItemListId>-1000000000000000003</ItemListId><ItemCost>30.00</ItemCost><ItemQty>10</ItemQty></Row>
</PurchaseOrderItems>' 
WHERE TxnId='-1234567890123456789'

Columns