Create, update, delete, and query QuickBooks POS Sales Orders.
Table Specific Information
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 SalesOrders WHERE Associate LIKE '%George%' AND TimeModified >= '1/1/2014' AND TimeModified < '2/1/2014'
To create a new Sales Orders record, the CustomerListId field is 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 SalesOrderItems table as # columns. Note that ItemListId is required when adding an item.
The following example will insert a new Sales Order with two items:
INSERT INTO SalesOrders (CustomerListId, SalesOrderNumber, ItemsAggregate) VALUES ('-9876543210987654321', 'SO12345', '<SalesOrderItems> <Row><ItemListId>-1000000000000000001</ItemListId><ItemPrice>15.00</ItemPrice><ItemQty>1</ItemQty></Row> <Row><ItemListId>-1000000000000000002</ItemListId><ItemPrice>25.00</ItemPrice><ItemQty>1</ItemQty></Row> </SalesOrderItems>')
Any field that is not read-only can be updated.
When updating a Sales Order record, items can be added or modified via the ItemsAggregate column.
To modify an existing item in a Sales 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 of 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 SalesOrders SET ItemsAggregate='<SalesOrderItems> <Row><ItemTxnLineId>1</ItemTxnLineId><ItemSize>L</ItemSize></Row> <Row><ItemTxnLineId>2</ItemTxnLineId></Row> </SalesOrderItems>' WHERE TxnId='-1234567890123456789'
Add New Item Example: Existing items remain intact when adding new items.
UPDATE SalesOrders SET ItemsAggregate='<SalesOrderItems> <Row><ItemListId>-1000000000000000003</ItemListId><ItemCost>40.00</ItemCost><ItemQty>1</ItemQty></Row> </SalesOrderItems>' WHERE TxnId='-1234567890123456789'
The unique identifier, generated by QuickBooks POS.
A reference to the customer.
The employee making the adjustment.
The balance remaining on the order.
Name of the employee taking the sales order. Note that this value is autofilled with the logged-in employee name if logins are required.
The sum of all deposits received, less any deposits already used.
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.
Enter a percentage discount applicable to the transaction. Discounts are applied to the subtotal. Entering a discount percentage causes the Discount field to be automatically calculated.
Optional field to provide instructions to the vendor regarding the order. You can add up to two lines of text. This text prints on sales orders, but is not displayed on-screen.
The number of line items in the request that added the transaction to QBPOS.
An aggregate of the line item data, which can be used for adding a transaction and its line items.
The price level discount given to the customer. Note that you cannot assign both a discount percentage and a price level to a customer.
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.
The quantity of the line item being ordered or transferred.
This is a unique number assigned by QBPOS to the sales order at creation.
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.
The type of the customer order document. Possible values are SalesOrder, Layaway, WorkOrder, and WebOrder.
In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether changes to the customer information were successfully updated. Possible values are Modified, Sent, and Acknowledged.
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.
The tax amount, returned in the response to inserting a non-held transaction.
The tax category (tax location).
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.
The total amount, after discounts and fees are applied. This value is updated when a voucher that references the transaction is updated.
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.
The percentage of the total sales order quantity that has not yet been received. This value is updated when a voucher that references the purchase order is updated.
A salutation, such as Mr., Mrs., etc.
The first name of the customer as stated in the address info.
The last name of the customer as stated in the address info.
The name of the company of the customer.
The primary telephone number for the customer.
An alternate telephone or fax number for the customer.
An alternate telephone or fax number for the customer.
Street address of the billing address of the customer.
The second line of the street address of the billing address of the customer.
City name for the billing address of the customer.
State name for the billing address of the customer.
Postal code for the billing address of the customer.
Country for the billing address of the customer.
The name used to identify the shipping address of the customer.
The company name of the shipping address of the customer.
The recipient's full name of the shipping address of the customer.
The primary telephone number for the shipping address of the customer.
Street address of the shipping address of the customer.
The second line of the street address of the shipping address of the customer.
City name for the shipping address of the customer.
State name for the shipping address of the customer.
Postal code for the shipping address of the customer.
Country for the shipping address of the customer.
The shipping method used to send merchandise to a customer.
Indicates the amount to be paid for shipping.
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).
Custom fields returned from QuickBooks POS and formatted into XML.
When the sales order was created.
When the sales order was last modified.