Published on March 20, 2024.

One of the most popular blog posts that I've written about NetSuite is also one of the oldest posts that I've written. It's Understanding Assembly Items, Kits, and Item Groups from May of 2019.

In terms of SuiteQL, I get a lot of questions about what I refer to in that post as the "complex" item types. That is, item types where an item consists of other items, including Assembly Items, Kits, and Item Groups.

In this post, I'm going to share some queries that answer questions that I've received regarding Item Groups.

As I wrote back in 2019, an Item Group is a collection of items that you might want to sell together. The example that I gave is a group consisting of a printer, a cable, ink cartridges, and a service contract. When you add an Item Group to a transaction, NetSuite will automatically add lineitems for the Item Group's components (which NetSuite refers to as "member items"). The user can choose to update the quantities of those lineitems, remove them, and so on.

Identifying Item Groups

To get started, a good query to run is one that will help you determine if you have any Item Groups in your NetSuite instance. You can identify an Item Group by querying the Item table and filtering on the ItemType column. The ItemType value for Item Groups is "Group."

So to get a count of the number of Item Groups in your instance, you can run this simple query.

SELECT
	COUNT(*) AS ItemCount
FROM
	Item
WHERE
	ItemType = 'Group'

Assuming that you have any Item Groups in your instance, you can use this query to get a few of the recently added Item Groups.

SELECT TOP 10
	ID,
	ItemID
FROM
	Item
WHERE
	ItemType = 'Group'
ORDER BY
	ID DESC

Member Items

One of the questions that I get asked frequently about Item Groups is, "How can I get the items that an Item Group consists of?"

Again, NetSuite refers to those items as "member items," and they're stored in a table named ItemMember.

This query can be used to get the member items for an Item Group based on the Item Group's internal ID.

SELECT
	ItemMember.Item,
	BUILTIN.DF( ItemMember.Item ) AS ItemID,
	ItemMember.Quantity,
	ItemMember.MemberUnit
FROM
	ItemMember
WHERE
	ItemMember.ParentItem = 681127

I'm using the BUILTIN.DF function to get the ItemID of each member item, and that saves me the trouble of joining to the Item table to get that value.

However, if you want to get additional information about each member item, you will need to join to the Item table. Here's a revised version of that query that shows the join.

SELECT
	ItemMember.Item,
	MemberItem.ItemID,
	MemberItem.Description,
	ItemMember.Quantity,
	ItemMember.MemberUnit	
FROM
	ItemMember
	INNER JOIN Item AS MemberItem ON
		( MemberItem.ID = ItemMember.Item )
WHERE
	ItemMember.ParentItem = 681127

I've aliased the joined table, so that I can refer to it as "MemberItem." And I've added the description of the member item to columns being returned.

Item Group Prices

One of the interesting things about Item Groups is that they don't have prices assigned to them. Instead, an Item Group's price is essentially the sum of the prices of its member items. That can make getting an Item Group's price via SuiteQL a little challenging.

To get Item Group's price, we need to get the prices for each of the member items. To do that, we need to join from the member item, to its related item, and from there, to the ItemPrice table. In this example, I'm going to get the List Price for the Item Group.

Let's start with this query, which is a revision to the query shown above.

SELECT
	ItemMember.Item,
	MemberItem.ItemID,
	MemberItem.Description,
	ItemMember.Quantity,
	ItemMember.MemberUnit,
	ItemPrice.Price AS UnitPrice,
	( ItemPrice.Price * ItemMember.Quantity ) AS ExtPrice
FROM
	ItemMember
	INNER JOIN Item AS MemberItem ON
		( MemberItem.ID = ItemMember.Item )
	LEFT OUTER JOIN ItemPrice ON
		( ItemPrice.Item = MemberItem.ID )
		AND ( ItemPrice.IsInactive = 'F' )
		AND  ( ItemPrice.PriceLevelName = 'List Price' )
WHERE
	ItemMember.ParentItem = 681127

I've added a join to the ItemPrice, and specifically to the related "List Price." This gives me access to the "UnitPrice."

It's possible for the quantity of a member item to be greater than 1. Therefore, I'm multiplying the unit price by the quantity of the member item, to get the extended price.

That gives you everything you need to get the Item Group's list price. You'd simply sum up the "ExtPrice" values of the rows.

If you want to get an Item Group's list price directly, you could use a query like this.

SELECT
	ItemGroup.ItemID,
	(
		SELECT
			SUM( ( ItemPrice.Price * ItemMember.Quantity ) )
		FROM
			ItemMember
			INNER JOIN Item AS MemberItem ON
				( MemberItem.ID = ItemMember.Item )
			LEFT OUTER JOIN ItemPrice ON
				( ItemPrice.Item = MemberItem.ID )
				AND ( ItemPrice.IsInactive = 'F' )
				AND  ( ItemPrice.PriceLevelName = 'List Price' )
		WHERE
			( ItemMember.ParentItem = ItemGroup.ID )
	) AS ListPrice
FROM
	Item AS ItemGroup
WHERE
	ItemGroup.ID = 681127

I've taken the previous query, modified it slightly, and used it as a correlated subquery to get the Item Group's list price.

Wrapping Up

I hope you've found the SuiteQL queries that I've shared in this post to be helpful. In a future blog post, I'll share some additional queries that discuss Item Groups, including one that you can use to determine the potential quantity of an Item Group that can be fulfilled based on the quantity available of its member items.