Items

Create, update, delete, and query QuickBooks Items.

Table Specific Information

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 Items are Id, TimeModified, FullName, Type, and IsActive. TimeModified may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. FullName may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example:

SELECT * FROM Items WHERE TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND FullName LIKE '%12345%'

Insert

When inserting the Item, specify the Type and Name fields. Depending on the Type, other columns may also be required in the insertion. See the list below to see which columns are required for special cases:

FixedAsset: Requires Name, Type, AcquiredAs, PurchaseDesc, and PurchaseDate.
SalesTaxGroup: Requires Name, Type, and at least one SalesTax Line Item. SalesTaxGroups must be inserted via the ItemLineItems table.

Columns


Name	Type	ReadOnly	References	ColumnSize	Description
ID [KEY]	String	True		255	The unique identifier.
Name	String	False		31	The name of the item.
FullName	String	True		159	Full item name in the format parentname:name if the item is a subitem.
Type	String	False		100	The type of item. This is required to have a value when inserting. The allowed values are Unknown, Service, Inventory, NonInventory, Payment, Discount, SalesTax, SubTotal, OtherCharge, InventoryAssembly, Group, SalesTaxGroup, FixedAsset. The default value is ALL.
Account	String	False		1000	Account name the item is associated with.
AccountId	String	False	Accounts.ID	255	Account Id the item is associated with.
COGSAccount	String	False		1000	Cost of Goods Sold account for the item.
COGSAccountId	String	False	Accounts.ID	255	Cost of Goods Sold account for the item.
AssetAccount	String	False		1000	Inventory asset account for the item if it is an inventory item.
AssetAccountId	String	False	Accounts.ID	255	Inventory asset account for the item if it is an inventory item.
Class	String	False		1000	A reference to the class of the item.
ClassId	String	False	Class.ID	255	A reference to the class of the item.
DateSold	Datetime	False			Indicates the date an asset was sold. This is required for fixed asset items. It is not used for any other types of items.
PurchaseDate	Date	False			Indicates date asset was purchased. This field is required for the fixed-asset item type. It is not used by any other item type.
ItemCount	Integer	False			The number of line items associated with the inventory assembly.
ParentName	String	False			The parent name of the item if the item is a subitem.
ParentId	String	False	Items.ID	255	The parent Id of the item if the item is a subitem.
Description	String	False		5000	A description of the item.
Price	Double	False			The price for the item.
PricePercent	Double	False			A price percent for this item (you might use a price percent if, for example, you are a service shop that calculates labor costs as a percentage of parts costs). If you set PricePercent, Price will be set to zero. Not used for payment, sales tax, or subtotal items.
AverageCost	Double	True			The average cost of the item.
IsActive	Boolean	False			Whether the item is active or not.
PurchaseCost	Double	False			Purchase cost for the item.
PurchaseDescription	String	False		5000	Purchase description for the item.
ExpenseAccount	String	False		1000	Expense account for the item.
ExpenseAccountId	String	False	Accounts.ID	255	Expense account for the item.
PreferredVendor	String	False		1000	Preferred vendor for the item.
PreferredVendorId	String	False	Vendors.ID	255	Preferred vendor for the item.
QuantityOnHand	Double	True			Quantity in stock for this inventory item.
QuantityOnOrder	Double	True			The number of these items that have been ordered from vendors, but not received.
QuantityOnSalesOrder	Double	True			The number of these items that have been ordered by customers, but not delivered.
InventoryDate	Date	False			The date when the item was converted into an inventory item.
ReorderPoint	Double	False			Quantity at which the user will be reminded to reorder this inventory item.
Barcode	String	False		50	Barcode for the item.
TaxCode	String	False		3	Reference to a sales tax code predefined within QuickBooks.
TaxCodeId	String	False	SalesTaxCodes.ID	255	Reference to a sales tax code predefined within QuickBooks.
IsTaxIncluded	Boolean	False			Determines if tax is included in the transaction amount. Available in only international editions of QuickBooks.
PurchaseTaxCode	String	False		3	Reference to a purchase tax code predefined within QuickBooks. Only available in international versions of QuickBooks.
PurchaseTaxCodeId	String	False	SalesTaxCodes.ID	255	This is a reference to a purchase tax code predefined within QuickBooks. Available in only international versions of QuickBooks.
PartNumber	String	False		31	The manufacturer's part number for the item. This property requires QBXML Version 7.0 or higher and is only applicable to inventory and noninventory item types.
PaymentMethodName	String	False		100	The method of payment: check, credit card, etc.
PaymentMethodId	String	False	PaymentMethods.ID	255	The method of payment: check, credit card, etc.
TaxRate	Double	False			The percentage rate of tax.
TaxVendorName	String	False		1000	The VENDOR or tax agency to whom taxes are due.
TaxVendorId	String	False	SalesTaxItems.ID	255	The VENDOR or tax agency to whom taxes are due.
SpecialItemType	String	False		50	The type of the item when the value of item type is Unknown. Calling Add on such an item will result in an error, however Get or GetByName can get any item without causing an error. The allowed values are FinanceCharge, ReimbursableExpenseGroup, ReimbursableExpenseSubtotal.
VendorOrPayeeName	String	False		50	Name of the vendor from whom this asset was purchased.
IsPrintItemsInGroup	Boolean	False			If true, a list of this group's individual items their amounts will appear on printed forms.
SalesExpense	String	False			Any expenses that were incurred during the sale of a fixed asset. This is applicable only if the asset has been sold.
AssetAcquiredAs	String	False		3	Indicates whether this item was new or used when the business acquired it. If AssetAcquiredAs is left blank, it will not be sent in the request. The allowed values are New, Old.
AssetDescription	String	False		5000	Description of the asset.
AssetLocation	String	False		50	Where the asset is located or has been placed into service.
AssetPONumber	String	False		30	The purchase order number associated with this asset.
AssetSerialNumber	String	False		5000	The serial number of the asset.
AssetWarrantyExpires	Date	False			The date when the warranty for this asset expires.
AssetNotes	String	False		5000	Additional information about the asset.
AssetNumber	String	False		10	The number used by the QuickBooks Fixed Asset Manager to identify this asset.
AssetCostBasis	Decimal	False			The total cost of the fixed asset. This can include the cost of improvements or repairs. This amount is used to figure depreciation.
AssetDepreciation	Double	False			The amount the fixed asset has lost in value since it was purchased, as of the end of the year.
AssetBookValue	Double	False			A reasonable estimate of the sales value of the fixed asset, as of the end of the year.
UnitOfMeasure	String	False		31	Name of the units that the item is measured in. Requires QBXML Version 7.0 or higher.
UnitOfMeasureId	String	False	UnitOfMeasure.ID	255	Id of the units that the item is measured in. Requires QBXML Version 7.0 or higher.
Sublevel	Integer	True			The number of ancestors this item has.
LineAggregate	String	True		5000	An aggregate of the line item data.
CustomFields	String	False			Custom fields returned from QuickBooks and formatted into XML.
EditSequence	String	True		16	An identifier for this copy of the object.
TimeModified	Datetime	True			When the item was last modified.
TimeCreated	Datetime	True			When the item was created.

Power BI Connector for QuickBooks

Items

Table Specific Information

Select

Insert

Columns