SalesOrderLineItems
Manages individual line items in QuickBooks Sales Orders, supporting creation, updates, deletion, and queries for order management.
Table Specific Information
SalesOrders may be inserted, queried, or updated via the SalesOrders or SalesOrderLineItems table. SalesOrders may be deleted by using the SalesOrders 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 only be used with the equals or = comparison. The available columns for SalesOrders are Id, Date, TimeModified, ReferenceNumber, CustomerName, and CustomerId. 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 SalesOrderLineItems WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND ReferenceNumber LIKE '%12345%'
Insert
To add a SalesOrder, specify the Customer and at least one Line Item. All Line Item columns can be used for inserting multiple Line Items for a new SalesOrder transaction. For example, the following will insert a new SalesOrder with two Line Items:
INSERT INTO SalesOrderLineItems#TEMP (CustomerName, ItemName, ItemQuantity) VALUES ('Cook, Brian', 'Repairs', 1) INSERT INTO SalesOrderLineItems#TEMP (CustomerName, ItemName, ItemQuantity) VALUES ('Cook, Brian', 'Removal', 2) INSERT INTO SalesOrderLineItems (CustomerName, ItemName, ItemQuantity) SELECT CustomerName, ItemName, ItemQuantity FROM SalesOrderLineItems#TEMP
Inserting Into an Existing SalesOrder
To add a SalesOrderLineItem to an existing SalesOrder, specify the SalesOrderId, the Item's name, and the Item's Quanitiy. For example:
INSERT INTO SalesOrderLineItems (SalesOrderId, ItemName, ItemQuantity) VALUES ('SalesOrderId', '01Item1', 1)
Columns
Name | Type | ReadOnly | References | ColumnSize | Description |
ID [KEY] | String | True | 255 |
A unique identifier combining the sales order ID and the line item ID, formatted as SalesOrderId|ItemLineId. | |
SalesOrderId | String | False |
SalesOrders.ID | 255 |
The unique identifier of the sales order associated with this line item. |
ReferenceNumber | String | False | 21 |
The reference number assigned to the transaction, used for tracking and customer communication. | |
TxnNumber | Integer | True |
An internal transaction number assigned to the sales order, distinct from the Quickbooks generated ID. | ||
CustomerName | String | False | 1000 |
The name of the customer associated with the sales order. This value is required when inserting data. | |
CustomerId | String | False |
Customers.ID | 255 |
The unique QuickBooks ID of the customer linked to the sales order. |
Date | Date | False |
The date when the sales order was created or last modified. | ||
ShipMethod | String | False | 1000 |
The shipping method used for the sales order, such as FedEx or USPS. | |
ShipMethodId | String | False | 255 |
The unique identifier of the shipping method. | |
ShipDate | Date | False |
The date when the items in the sales order are scheduled to be shipped. | ||
Memo | String | False | 5000 |
Additional notes or details about this sales order, often used for internal tracking. | |
Class | String | False | 1000 |
A category or classification for the sales order, such as department or location. | |
ClassId | String | False |
Class.ID | 255 |
The unique identifier for the class associated with this sales order. |
TotalAmount | Decimal | False |
The total amount of the sales order, including all line items. | ||
DueDate | Date | False |
The date by which payment for this sales order is due. | ||
Message | String | False | 101 |
A custom message intended for the customer, such as special instructions or greetings. | |
MessageId | String | False | 255 |
The unique identifier of the message sent to the customer. | |
SalesRep | String | False | 5 |
The initials or name of the sales representative handling the transaction. | |
SalesRepId | String | False |
SalesReps.ID | 255 |
The unique identifier for the sales representative. |
Template | String | False | 100 |
The name of the template used to format the sales order, such as a custom invoice layout. | |
TemplateId | String | False |
Templates.ID | 255 |
The unique identifier for the template applied to the sales order. |
CurrencyName | String | False | 64 |
The name of the currency used for the sales order, such as USD or EUR. | |
CurrencyId | String | False |
Currency.ID | 255 |
The unique identifier for the currency used in the sales order. |
ExchangeRate | Double | False |
The exchange rate applied if the sales order uses a currency different from the home currency. | ||
TotalAmountInHomeCurrency | Decimal | False |
The total amount of the sales order converted to the home currency. | ||
BillingAddress | String | True |
The complete billing address associated with the sales order. | ||
FOB | String | False | 13 |
Indicates the point of transfer of ownership for shipped goods, such as 'Origin' or 'Destination.' | |
BillingLine1 | String | False | 500 |
The first line of the billing address. | |
BillingLine2 | String | False | 500 |
The second line of the billing address. | |
BillingLine3 | String | False | 500 |
The third line of the billing address. | |
BillingLine4 | String | False | 500 |
The fourth line of the billing address. | |
BillingLine5 | String | False | 41 |
The fifth line of the billing address. | |
BillingCity | String | False | 255 |
The city of the billing address. | |
BillingState | String | False | 255 |
The state or province of the billing address. | |
BillingPostalCode | String | False | 30 |
The postal or ZIP code of the billing address. | |
BillingCountry | String | False | 255 |
The country of the billing address. | |
BillingNote | String | False | 41 |
Additional notes related to the billing address. | |
ShippingAddress | String | True |
The complete shipping address for the sales order. | ||
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 or province of the shipping address. | |
ShippingPostalCode | String | False | 30 |
The postal or ZIP code of the shipping address. | |
ShippingCountry | String | False | 255 |
The country of the shipping address. | |
ShippingNote | String | False | 41 |
Additional notes related to the shipping address. | |
Subtotal | Decimal | True |
The gross subtotal for the sales order, excluding tax and payments received. | ||
Tax | Double | True |
The total sales tax applied to this sales order. | ||
TaxItem | String | False | 100 |
A specific sales tax item associated with this sales order. | |
TaxItemId | String | False | 255 |
The unique identifier for the sales tax item. | |
TaxPercent | Double | True |
The percentage of sales tax applied. | ||
PONumber | String | False | 41 |
The purchase order number associated with the sales order. | |
Terms | String | False | 100 |
The payment terms agreed upon for the sales order, such as 'Net 30'. | |
TermsId | String | False | 255 |
The unique identifier for the payment terms. | |
ItemLineId | String | True | 255 |
The unique identifier for the specific line item in the sales order. | |
ItemLineNumber | String | True | 255 |
The sequential number of the line item within the sales order. | |
ItemName | String | False |
The name of the item listed in this line. | ||
ItemId | String | False |
Items.ID | 255 |
The unique identifier for the item in this line. |
ItemGroup | String | False | 100 |
The group or category this item belongs to, if applicable. | |
ItemGroupId | String | False |
Items.ID | 255 |
The unique identifier for the item group. |
ItemDescription | String | False | 5000 |
A detailed description of the item. | |
ItemQuantity | Double | False |
The quantity of the item ordered in this line. | ||
ItemRate | Double | False |
The rate charged per unit of this item. | ||
ItemRatePercent | Double | False |
The discount or surcharge percentage applied to the item rate. | ||
ItemTaxCode | String | False | 3 |
The tax code applied to this item, indicating if it is taxable or non-taxable. | |
ItemTaxCodeId | String | False | 255 |
The unique identifier for the tax code of this item. | |
ItemInvoicedAmount | Decimal | True |
The portion of this item's amount that has been invoiced. | ||
ItemUnitOfMeasure | String | False | 31 |
The unit of measure for the item, such as 'Each' or 'Box.' | |
ItemAmount | Decimal | False |
The total amount for this item, considering quantity and rate. | ||
ItemClass | String | False | 1000 |
The class or category associated with this item. | |
ItemClassId | String | False |
Class.ID | 255 |
The unique identifier for the class associated with this item. |
ItemInventorySiteName | String | False | 1000 |
The inventory site where this item is stored. Requires QBXML version 10.0 or higher. | |
ItemInventorySiteId | String | False | 255 |
The unique identifier for the inventory site. Requires QBXML version 10.0 or higher. | |
ItemSerialNumber | String | False | 5000 |
The serial number of this item, applicable to serialized inventory. Requires QBXML version 11.0 or higher. | |
ItemLotNumber | String | False | 40 |
The lot number of this item, applicable to lot-tracked inventory. Requires QBXML version 11.0 or higher. | |
ItemExpirationDateForSerialLotNumber | String | True |
The expiration date for this item's serial or lot number. Supported from QB Desktop 2023 version 3 and SDK 16.0. | ||
ItemIsManuallyClosed | String | False | 10 |
Indicates whether this line item has been manually closed. | |
ItemOther1 | String | False | 29 |
A custom field for additional details about 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 containing additional metadata for the item. | ||
ItemUOMSetFullName | String | False | 1000 |
The full name of the unit of measure set for this item. | |
ItemUOMSetListID | String | False | 1000 |
The unique identifier for the unit of measure set. | |
ItemIsGetPrintItemsInGroup | String | False | 1000 |
Indicates whether group items and their details should appear on printed forms. | |
CustomerTaxCode | String | False | 3 |
The tax code specific to the customer for this sales order. | |
CustomerTaxCodeId | String | False | 255 |
The unique identifier for the customer's tax code. | |
IsToBePrinted | Boolean | False |
Indicates whether this sales order is flagged for printing. | ||
IsToBeEmailed | Boolean | False |
Indicates whether this sales order is flagged for emailing. | ||
IsManuallyClosed | String | False | 10 |
Indicates whether this sales order has been manually closed. | |
IsFullyInvoiced | Boolean | True |
Indicates whether all items in this sales order have been invoiced. | ||
IsTaxIncluded | Boolean | False |
Indicates whether the tax is included in the amounts for this sales order. | ||
CustomFields | String | False |
Custom fields containing additional metadata for the sales order. | ||
EditSequence | String | True | 16 |
A versioning identifier for the current state of the sales order. | |
TimeModified | Datetime | True |
The date and time when this sales order was last modified. | ||
TimeCreated | Datetime | True |
The date and time when this sales order was initially created. |
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.
Name | Type | Description |
ItemPriceLevel | String |
The price level applied to items in this sales order. QuickBooks does not return this directly. |