Items
Create, update, delete, and query QuickBooks POS 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 Items WHERE QuantityOnHand < 5
Insert
To create a new item record, the DepartmentListId field is required.
INSERT INTO Items (DepartmentListId, Desc1, Cost)
VALUES ('-1234567890123456789', 'T-Shirt', '9.99')
Update
Any field that is not read-only can be updated.
UPDATE Items SET Size='L' WHERE ListId='-1000000000000000001'
Columns
| Name | Type | ReadOnly | Filter Type | Description |
| ListID [KEY] | String | True | Single |
The unique identifier, generated by QuickBooks POS. |
| ALU | String | False | Range |
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. |
| Attribute | String | False | Range |
A field used to describe an item characteristic, typically color, pattern, material, or a second size. This value must be 1-8 characters. |
| COGSAccount | String | False | Range |
The Cost of Goods Sold (COGS) Account for the item. This field is used if you are using QBPOS with QuickBooks. |
| Cost | Decimal | False | Range |
The averaged cost of the current on-hand quantity of the item. Cost is automatically updated by receiving vouchers or manually updated by adjustment memos. |
| DepartmentListID | String | False | Single |
The Id of the department the item is associated with. |
| DepartmentCode | String | True | Range |
A unique, 1-3 character code identifying the department. The department code can be used when defining an item in inventory. The first character usually indicates the broadest classification. |
| Desc1 | String | False | Range |
The principal item description field. This description is printed on receipts. |
| Desc2 | String | False | Range |
Secondary description field, usually used for any additional information to describe this particular item, such as the catalog number of a vendor. Also can be used for additional information at the point of sale. |
| IncomeAccount | String | False | Range |
The Income Account for the item. This field is used if you are using QBPOS with QuickBooks. |
| IsBelowReorder | Boolean | True | Single |
Indicates whether the item is below the reorder point. |
| IsEligibleForCommission | Boolean | False | Single |
Indicates whether the item is eligible for a commission. |
| IsPrintingTags | Boolean | False | Single |
Indicates whether the Print Tags checkbox is selected for this item, that is, whether this item is included in the list of items for which price tags are to be printed. |
| IsUnorderable | Boolean | False | Single |
Indicates whether the item is orderable. Items flagged as Unorderable do not show up on the reorder reminder list when their on-hand number falls below the reorder point. |
| HasPictures | Boolean | True | Single |
Indicates whether the item has pictures to identify it. |
| IsEligibleForRewards | Boolean | False | Single |
Indicates whether the item is eligible for rewards. |
| IsWebItem | Boolean | False | Single |
Indicates whether the item is sold online. |
| ItemNumber | Double | True | Range |
Unique number assigned to the item when it is added to QBPOS. |
| ItemType | String | False | Single |
The type of item being added. Possible values are Inventory, NonInventory, Service, Assembly, Group, and SpecialOrder. |
| LastReceived | Datetime | True | Range |
Date this item was last received into inventory. |
| MarginPercent | Double | False |
The profit, expressed as a percentage of the price. If defined in a department record, this value is used to calculate the prices of new items as they are added to inventory. Note that modifying the margin in inventory causes QBPOS to recalculate your prices and the Markup Percent. | |
| MarkupPercent | Double | False |
The profit, expressed as a percentage over the cost. Works in the same manner as Margin Percent. Making an entry in this field causes QBPOS to recalculate your item prices and margin. | |
| MSRP | Double | False | Range |
Manufacturer's suggested retail price. Reference price that is printed on price tags if defined in inventory. |
| OnHandStore01 | Double | False | Range |
The quantity of the item on hand in Store 1 inventory. |
| OnHandStore02 | Double | False | Range |
The quantity of the item on hand in Store 2 inventory. |
| ReorderPointStore01 | Double | False | Range |
The quantity at which an item should be reordered for Store 1. |
| ReorderPointStore02 | Double | False | Range |
The quantity at which an item should be reordered for Store 2. |
| OrderByUnit | String | False | Range |
The default unit of measure when you order this item from vendors. If you are using multiple units of measure instead of a single unit of measure, you can use one unit of measure to order by (OrderByUnit) and another to sell by (SellByUnit). You can override this by specifying a different unit of measure when you insert a purchase order. |
| OrderCost | Decimal | False | Range |
The current cost paid to the vendor for the item. |
| Price1 | Decimal | False | Range |
The baseline or everyday retail price of the item. |
| Price2 | Decimal | False | Range |
Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences. |
| Price3 | Decimal | False | Range |
Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences. |
| Price4 | Decimal | False | Range |
Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences. |
| Price5 | Decimal | False | Range |
Price2-Price5 are used to specify discount pricing for the item. These values override the PriceLevels in the company preferences. If you do not specify Price2-Price5, QBPOS will apply any markdowns specified in the PriceLevels in the company preferences. |
| QuantityOnCustomerOrder | String | True | Range |
The quantity, in terms of the base unit of measure, of the inventory item that is currently under order by customers and therefore not available. |
| QuantityOnHand | String | True | Range |
The number of units of an item currently in stock. This number is updated by receiving vouchers and sales receipts. |
| QuantityOnOrder | String | True | Range |
The number of units of the item that are currently on order. In multistore configurations, this number is the on-order number for all of the stores. |
| QuantityOnPendingOrder | String | True | Range |
The number of units of the item that are currently pending on order. |
| ReorderPoint | Double | False | Range |
The quantity at which an item should be reordered. |
| SellByUnit | String | False | Range |
The default unit of measure when you sell the item. You can override this by specifying a different unit of measure when you insert a sales receipt. |
| SerialFlag | String | False | Single |
The serial flag indicates whether the user is prompted to enter a serial number when the item is listed on documents. Enter Prompt to be prompted to enter a serial number; enter Optional (default), and the user will not be prompted but can still enter a serial number on the document. |
| Size | String | False | Range |
The size of an item. This field can also be used to describe a second item attribute other than size. 1-8 characters are allowed. |
| StoreExchangeStatus | String | True | Single |
In multistore configurations, if the customer information has changed, this information is updated at the headquarters store. This response field indicates whether customer information was successfully updated or not. Possible values are Modified, Sent, and Acknowledged. |
| TaxCode | String | False | Single |
The tax code that is actually applied at transaction time is the tax code of the tax category that is currently specified as the default tax category in the sales tax preferences. |
| UnitOfMeasure | String | False | Range |
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. Inventory quantities are tracked and reported in terms of this base unit. |
| UnitOfMeasure1 | String | False |
Aggregate value of the first unit of measure. | |
| UnitOfMeasure2 | String | False |
Aggregate value of the second unit of measure. | |
| UnitOfMeasure3 | String | False |
Aggregate value of the third unit of measure. | |
| UPC | String | False | Range |
The UPC/EAN/ISBN of the item. This field must be 13 characters. |
| VendorCode | String | True | Range |
The code assigned to the vendor specified for the item. |
| VendorListID | String | False | Single |
A reference to the vendor. |
| VendorInfo2 | String | False |
Info for the second vendor. | |
| VendorInfo3 | String | False |
Info for the third vendor. | |
| VendorInfo4 | String | False |
Info for the fourth vendor. | |
| VendorInfo5 | String | False |
Info for the fifth vendor. | |
| WebDesc | String | False | Range |
The description of the item for use online. |
| WebPrice | Decimal | False | Range |
The price of the item when sold online. |
| Manufacturer | String | False | Range |
The manufacturer of the item. |
| Weight | Double | False | Range |
The weight of the item. |
| WebSKU | String | True | Range |
The SKU of the item for online use. |
| Keywords | String | False | Range |
Keywords to identify the item. |
| WebCategories | String | False |
Categories to identify the item online. | |
| 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 item was created. |
| TimeModified | Datetime | True | Range |
When the item was last modified. |