JDBC Driver for QuickBooks POS

Build 22.0.8462

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.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462