Published on February 16, 2021.

Last week a NetSuite developer posted an interesting SuiteQL question to the NetSuite Professionals Slack Community. Specifically, he wanted to know if it is possible to use SuiteQL to get a kit/package item's components.

If you're not familiar with kit/package items, here's a quick overview. I first wrote about them back in May of 2019, and described them as "bundles" of items that are being sold together as if they are one item. A kit's components can be inventory items, assembly items, service items, as well as other kits.

The key to using SuiteQL to get a kit/package item's components is a table named KitItemMember. It is essentially a join table between the kit and its components.

The KitItemMember table has only a few columns, two of which are foreign keys to the Item table. The ParentItem column is the ID of the kit/package item, while the Item column is the ID of the component item. Of course, there's an ID column that serves as a primary key to uniquely identify the KitItemMember record.

There is also a LineNumber column (which indicates the sequential order in which a component was added to the kit/package) and a Quantity column (which indicates the quantity of the component item that is needed for the kit/package).

Here's a query that shows how you can use the KitItemMember table.

SELECT	
	ParentItem.ID AS ParentItemID,
	ParentItem.ItemID AS ParentItemItemID,
	KitItemMember.LineNumber,
	MemberItem.ID AS MemberItemID,
	MemberItem.ItemID AS MemberItemItemID,
	KitItemMember.Quantity AS KitItemMemberQuantity
FROM 
	Item AS ParentItem
	INNER JOIN KitItemMember ON
		( KitItemMember.ParentItem = ParentItem.ID )
	INNER JOIN Item AS MemberItem ON
		( MemberItem.ID = KitItemMember.Item )
WHERE 
	( ParentItem.ID = 7307 )
ORDER BY
	KitItemMember.LineNumber

The query uses two references to the Item table, one aliased as ParentItem (to represent the kit/package) and the other as MemberItem (to represent its components). I've used INNER JOINs to map the ParentItem record to its MemberItem records via the KitItemMember table. I've included the component quantity in the result, as well as the component line number.

When working with SuiteQL, often the biggest challenge is determining what tables are needed. In this case, identifying the KitItemMember table, and then using it to join to the Item table, was the solution.

If you have any questions about this query, please feel free to contact me.