SuiteQL: Items

Introduction

Getting items in SuiteQL is mostly straightforward.

NetSuite will group all item types into a single table called Item. This includes all inventory items, assembly items, service items etc.

Item Fields

The Item table has a lot of fields.

Here are some of the most common ones:

Column Name	Description	Type
itemid	The item number	String
displayname	The item display name	String
primaryunitstype	The units type of an item	Integer (internal id)
purchaseunit	The purchase units of an item	Integer (internal id)
…Other Units	Other units such as sale, consumption etc.	Integer (internal id)
cost	The purchase price of an item	Decimal
costingmethod	The costing method used e.g. Average, Standard, FIFO etc.	String
itemtype	The item type (more information about item types below)	String
usebins	Whether the item uses bins or not	Boolean
cogsaccount	The COGS account of the item	Integer (internal id)
…Other Accounts	The other accounts of the item	Integer (internal id)

Getting the Item Type

The Item has a field called itemtype.

Let’s see what it’s about:

SELECT DISTINCT itemtype FROM Item

This returns:

ItemType
OthCharge
Discount
Group
Assembly
NonInvtPart
InvtPart
…

The fields are human readable-ish, but are clearer when you run use BUILTIN.DF:

SELECT DISTINCT BUILTIN.DF(itemtype) as itemtype FROM Item

Now it’s a bit clearer:

ItemType
Other Charge
Discount
Item Group
Non-inventory Item
Inventory Item
…

This won’t return all the information you need. When creating an item in the UI, you can create a service for purchase, service for sale, and service for resale.

This information is stored on the subtype field.

SELECT DISTINCT
  BUILTIN.DF(itemtype) as itemtype,
  subtype
FROM Item
  WHERE itemtype = 'OthCharge'

This returns:

ItemType	SubType
Other Charge	Sale
Other Charge	Purchase
Other Charge	Resale

We also need to know whether and inventory item or assembly is a lot numbered item, serialized item, or neither. This can be found on the islotitem and isSerialItem fields.

SELECT DISTINCT
  BUILTIN.DF(itemtype) as itemtype,
  islotitem, isserialitem
FROM Item
  WHERE itemtype = 'InvtPart'

This returns:

ItemType	IsLotItem	IsSerialItem
Inventory Item	F	F
Inventory Item	F	T
Inventory Item	T	F

We now have enough information to get the item type.

Let’s create a SuiteScript function that takes an item internal id and returns the item type.

I’m using the function BUILTIN_RESULT.TYPE_BOOLEAN to convert the T/F values to true/false.

This is SuiteScript 2.1. Make sure to load the query and error modules.

function getItemType(id){
  const theQuery = `
    SELECT
      BUILTIN.DF(itemtype) as itemtype,
      subtype,
      BUILTIN_RESULT.TYPE_BOOLEAN(isserialitem) as isserialitem,
      BUILTIN_RESULT.TYPE_BOOLEAN(islotitem) as islotitem,
    FROM Item
    WHERE id = ${id || 0}
  `; // I don't want the query itself to fail if an id is not provided,
  //so I'm using 0 as a default value which won't match any item.

  const result = query.runSuiteQL(theQuery).asMappedResults()[0]
  // This previous line will return all items that match
  // which can be 1 or 0 depending on the id;

  if(!result) throw error.create({
    name: 'ITEM_ID_NOT_FOUND',
    message: `Item ${id} not found`
  });

  const {itemtype, subtype, islotitem, isserialitem} = result;

  const islotitemprefix = islotitem? 'Lot Numbered ' : '';
  const isserialitemprefix = isserialitem? 'Serialized ' : '';
  const subtypesuffix = subtype?  ' For ' + subtype : '';

  return  islotitemprefix + isserialitemprefix + itemtype + subtypesuffix;
  // Note that at most  one prefix will be.

}

Item Cost

The field item.cost just tracks the field Purchase Price on the item record. This is not the cost that NetSuite uses to calculate the GL impact of a transaction.

Getting the cost that NetSuite uses to calculate the GL impact depends on the costing method. If using average costing, the cost is stored on the averagecost field. If using standard costing, the cost is stored on a separate table called AggregateItemLocation. This is because an item can have different costs depending on the location.

This table has three fields that we need: item, location, and cost.

Let’s join the AggregateItemLocation table to the Item table at a location called “Main Warehouse” :

SELECT
  Item.itemid,
  AggregateItemLocation.cost
FROM Item
  INNER JOIN AggregateItemLocation ON Item.id = AggregateItemLocation.item
WHERE BUILTIN.DF(AggregateItemLocation.location) = "Main Warehouse"

This will return the item id and cost for all items at the Main Warehouse location. Note that the cost might be null if your item doesn’t have the appropriate inventory revaluations for the location.

Conclusion

This is a very brief introduction to the Item table. Many of your queries will have to join the item table. In particular, if you want to get the quantity of an item in the purchase units you’ll have to join the item table to look at the purchaseunit field.

Next time, we’ll look at units of measure and how to convert them in SuiteQL. This is especially relevent in SuiteQL because many fields are stored in the items primary units of measure base unit.