SalesOrderItems

Create, update, delete, and query QuickBooks POS Sales Order Items.

Table Specific Information

Select

QuickBooks POS 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 SalesOrderItems WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'

Insert

Insert can be used to create a new Sales Order record or to add an item to an existing Sales Order record.

To create a new Sales Order record, the PurchaseOrderNumber and VendorListId fields are required.

INSERT INTO SalesOrderItems (CustomerListId, SalesOrderNumber, ItemListId, ItemCost, ItemQty) 
VALUES ('-9876543210987654321', 'SO12345', '-1000000000000000001', 15.00, 1)

To add an item to an existing record, the TxnId column of the Sales Order record that the item is to be added to and the ItemListId are required.

INSERT INTO SalesOrderItems (TxnId, ItemListId, ItemCost, ItemQty) 
VALUES ('-1234567890123456789', '-1000000000000000001', 15.00, 1)

Update

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

Note: Update operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record, and another to update the record with new data.

To perform an update, TxnId and ItemTxnLineId are required fields.

UPDATE SalesOrderItems SET ItemSize='L' WHERE TxnId='-1234567890123456789' AND ItemTxnLineId='1'

Delete

Delete is used to remove an item from the specified record (it will not delete the entire record itself).

Note: Deletion of an item requires that all the wanted items that currently exist in the record be sent in the request, thus deleting those that are not specified. Therefore Delete operations will count as two operations against the QuickBooks POS API. One is required to retrieve the existing record to obtain all the existing ItemTxnLineIds and another to delete the specified item.

To perform a delete, TxnId and ItemTxnLineId are required fields.

DELETE FROM SalesOrderItems WHERE TxnId='-1234567890123456789' AND ItemTxnLineId='3'

Columns


Name	Type	ReadOnly	Filter Type	Description
TxnID [KEY]	String	True	Single	The unique identifier, generated by QuickBooks POS.
CustomerListID	String	False	Single	The reference key to the customer.
Associate	String	False	Range	The employee making the adjustment.
BalanceDue	Decimal	True	Range	The balance remaining on the order.
Cashier	String	False	Range	Name of the employee taking the sales order. This value is autofilled with the logged-in employee name if logins are required.
DepositBalance	Decimal	True		The sum of all deposits received, less any deposits already used.
Discount	Decimal	False	Range	The amount of any discount applicable to the transaction. Discounts are applied to the subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
DiscountPercent	Double	False	Range	Enter a percentage discount applicable to the transaction. Discounts are applied to the subtotal. Entering the Discount Percent causes the Discount field to be automatically calculated.
Instructions	String	False	Range	Optional field to provide instructions to the vendor regarding the order. You can add up to two lines of text. This text prints on purchase orders, but is not displayed on-screen.
ItemsCount	String	True	Range	The number of line items in the insert that added the transaction.
PriceLevelNumber	String	False	Single	The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
PromoCode	String	False	Range	A miscellaneous note on the transaction, usually identifying a special sale. An entry in this field may be required depending on settings in the company preferences.
Qty	Double	True	Range	The quantity of the line item being ordered or transferred.
SalesOrderNumber	String	False	Range	This is a unique number assigned by QBPOS to the sales order at creation.
SalesOrderStatusDesc	String	False		Current status of the order. Possible values are OPEN, CLOSED, or a custom status you have defined in the company preferences. Used to track and filter the progression of orders for viewing and reporting.
SalesOrderType	String	False		The type of the customer order document. Possible values are SalesOrder, Layaway, WorkOrder, and WebOrder.
StoreExchangeStatus	String	True		In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether the customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged.
Subtotal	Decimal	True	Range	The sum of the extended item costs of the transaction before applying any discounts or fees. This value is updated when a voucher that references the transaction is updated.
TaxAmount	Decimal	True	Range	The tax amount, returned in the response to inserting a non-held transaction.
TaxCategory	String	False	Range	The tax category (tax location).
TaxPercentage	String	False	Range	The tax percentage, returned in the response to inserting the transaction. Either a tax amount or a tax percentage can be specified, but not both. The other value and the document total tax amounts will be recomputed to reflect the value you specify.
Total	Decimal	True	Range	The total amount after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
TxnDate	Datetime	False	Range	The date of the transaction. In some cases, if this value is not specified, QBPOS will use the current date or pre-fill TxnDate with the date of the last-saved transaction of the same type.
UnfilledPercent	String	True	Range	The percentage of the total purchase order quantity that has not yet been received. This value is updated when a voucher that references the purchase order is updated.
BillingInformation_Salutation	String	True		A salutation, such as Mr., Mrs., etc.
BillingInformation_FirstName	String	True		The first name of the customer as stated in the address info.
BillingInformation_LastName	String	True		The last name of the customer as stated in the address info.
BillingInformation_CompanyName	String	True		The name of the company of the customer.
BillingInformation_Phone	String	True		The primary telephone number for the customer.
BillingInformation_Phone2	String	True		An alternate telephone or fax number for the customer.
BillingInformation_Phone3	String	True		An alternate telephone or fax number for the customer.
BillingInformation_Street	String	True		Street address of the billing address of the customer.
BillingInformation_Street2	String	True		The second line of the street address of the billing address of the customer.
BillingInformation_City	String	True		City name for the billing address of the customer.
BillingInformation_State	String	True		State name for the billing address of the customer.
BillingInformation_PostalCode	String	True		Postal code for the billing address of the customer.
BillingInformation_Country	String	True		Country for the billing address of the customer.
ShippingInformation_AddressName	String	False		The name used to identify the shipping address of the customer.
ShippingInformation_CompanyName	String	False		The company name of the shipping address of the customer.
ShippingInformation_FullName	String	False		The recipient's full name of the shipping address of the customer.
ShippingInformation_Phone	String	False		The primary telephone number for the shipping address of the customer.
ShippingInformation_Street	String	False		Street address of the shipping address of the customer.
ShippingInformation_Street2	String	False		The second line of the street address of the shipping address of the customer.
ShippingInformation_City	String	False		City name for the shipping address of the customer.
ShippingInformation_State	String	False		State name for the shipping address of the customer.
ShippingInformation_PostalCode	String	False		Postal code for the shipping address of the customer.
ShippingInformation_Country	String	False		Country for the shipping address of the customer.
ShippingInformation_ShipBy	String	False		The shipping method used to send merchandise to a customer.
ShippingInformation_Shipping	Decimal	False		Indicates the amount to be paid for shipping.
CustomFieldsOwnerID	String	False	Multi	Identifies the owner of the CustomFields to be returned in the response. The default value of '0' is sent which refers to a public custom field that is exposed in the QuickBooks POS UI. All other values are GUID's that are created by the owner and are private custom fields (not exposed via the QuickBooks POS UI).
CustomFields	String	False		Custom fields returned from QuickBooks POS and formatted into XML.
TimeCreated	Datetime	True	Range	When the transaction was created.
TimeModified	Datetime	True	Range	When the transaction was last modified.
ItemTxnLineID# [KEY]	String	True		Identification number of the transaction line. TxnLineId is supported as of version 2.0 of the SDK. With QBXML 1.0 and 1.1, TxnLineId is always returned as zero. If you need to add a new transaction line in an update, you can do so by setting the TxnLineId to -1.
ItemListID#	String	False		The unique identifier for the item, generated by QuickBooks POS.
ItemALU#	String	False		Alternate lookup. A user-defined identifier to be used to look up an item. Do not duplicate the Item Number or UPC in this field. This will adversely affect performance for item lookups.
ItemAssociate#	String	False		The employee making the adjustment transaction.
ItemAttribute#	String	False		A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters.
ItemCommission#	Decimal	False		The commission amount for the item, based on the item price and the Associates commission percent, if the item is eligible for commissions.
ItemDesc1#	String	False		The principal item description field. This description is printed on receipts.
ItemDesc2#	String	False		Secondary description field usually used for any additional information to describe this particular item, such as the catalog number of the vendor. Also can be used for additional information at the point of sale.
ItemDiscount#	Decimal	False		The amount of a discount applicable to the transaction. Discounts are applied to the transaction subtotal. Entering an amount causes the Discount Percent field to be automatically calculated.
ItemDiscountPercent#	Double	False		A percentage discount applicable to the item. Discounts are applied to the transaction subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
ItemDiscountType#	String	False		The reason for an item discount.
ItemExtendedPrice#	Decimal	True		The extended price of a line item.
ItemExtendedTax#	Decimal	True		Total tax per line item in the order.
ItemItemNumber#	Double	True		Unique number assigned by QBPOS to the item when it is added.
ItemNumberOfBaseUnits#	Double	True		The base unit, used to track and report the inventory quantity.
ItemPrice#	Decimal	False		The purchase price or sales price of this item.
ItemPriceLevelNumber#	String	True		The suggested price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
ItemQty#	Double	False		The quantity of the line item being ordered or transferred.
ItemQtySold#	Double	True		The quantity of the ordered item that is sold to the customer.
ItemSerialNumber#	String	False		Item serial numbers can be recorded in QBPOS for purposes of warranty tracking or meeting legal requirements.
ItemSize#	String	False		The size of an item. This field can also be used to describe a second item attribute other than size if desired. This field must be 1-8 characters.
ItemTaxAmount#	String	True		The tax amount for the item. Either a tax amount or a tax percentage can be specified, but not both; the other value and the document total tax amounts will be recomputed to reflect the value you specify.
ItemTaxCode#	String	False		The tax code of the item.
ItemTaxPercentage#	String	True		The tax percentage for the item. Either a tax amount or a tax percentage can be specified, but not both; the other value and the document total tax amounts will be recomputed to reflect the value you specify.
ItemUnitOfMeasure#	String	False		If the company is using only a single unit of measure, this specifies the unit of measure for the item. If the company is using multiple units of measure, this specifies the base unit of measure for the item.
ItemUPC#	String	False		The UPC/EAN/ISBN of the item. This field must be 13 characters. If a shorter number is entered, QBPOS uses an appropriate algorithm to lengthen the entry to fill the 13-character field.
ItemWebDesc#	String	True		The description of the item for use online.
ItemManufacturer#	String	True		The manufacturer of the item.
ItemWeight#	Double	True		The weight of the item.
ItemWebSKU#	String	True		The SKU of the item for online use.