Published on June 5, 2022.

Over the past year, the amount of work that I've done for NetSuite clients who are in the manufacturing sector has increased significantly. In the past, the majority of my work was for clients in the wholesale distribution sector.

One of the many interesting and subtle differences that this shift has caused has to do with the type of items that I am working with. By that, I mean a shift from standard inventory items, drop ship items, and special order items, to item groups and assembly items. If you're unfamiliar with the many different types of items that NetSuite supports, then you might find this post from May 2019 to be helpful. In it, I discuss assembly items, kits, and item groups.

In this post, I'm going to share some SuiteQL queries that involve assembly items, including one that you can use to get an assembly item's components.

Assembly Items

Let's start with a simple query that returns a sample of assembly items.

SELECT TOP 10
	'<a href="/app/common/item/item.nl?id=' || Item.ID || '" target="_item_' || Item.ID || '">View</a>' AS ItemLink,
	Item.ID,
	Item.ItemID, 	
	Item.Description
FROM
	Item
WHERE
	( Item.ItemType = 'Assembly' )
ORDER BY
	Item.ItemID

This is a simple query that returns the first 10 assembly items, sorted by Item ID. Note that the ItemType for assembly items is "Assembly."

The ItemLink is derived by concatenating a few string values with the item ID. I discussed this technique in a post from January titled "Add Links to SuiteQL Query Results. Notice that when creating the link, I'm assigning a "target" attribute that is also based on the item ID. This allows the user to open each item in its own tab / window, where each tab / window has its own ID.

Assembly Item Components

NetSuite stores information about an assembly item's components in a table named AssemblyItemMember. This is a join table between the item record of the assembly itself, and the item records that represent the assembly item's components.

Here's a query that returns assembly item's components for a given assembly item, based on the assembly item's record ID.

SELECT
	'<a href="/app/common/item/item.nl?id=' || AssemblyItemMember.Item || '" target="_item_' || AssemblyItemMember.Item || '">View</a>' AS MemberItemLink,
	AssemblyItemMember.LineNumber,	
	AssemblyItemMember.Item AS MemberItemID,
	BUILTIN.DF( AssemblyItemMember.Item ) AS MemberItemItemID,	
	AssemblyItemMember.Quantity AS MemberItemQty,
	BUILTIN.DF( AssemblyItemMember.MemberUnit ) AS MemberItemUOM,
	AssemblyItemMember.ItemSource AS MemberItemSource,
	MemberItem.ItemType AS MemberItemType,
	MemberItem.Description AS MemberItemDesc
FROM
	AssemblyItemMember
	INNER JOIN Item AS MemberItem ON
		( MemberItem.ID = AssemblyItemMember.Item )
WHERE	
	( AssemblyItemMember.ParentItem = 999999 )
ORDER BY
	AssemblyItemMember.LineNumber

In the query, I'm joining the record in the AssemblyItemMember join table to the item record that it represents, and I'm doing this so that I can get a few values from the item record - including the item type, description, and so on.

However, there are a few columns in the AssemblyItemMember join table that are interesting and that I've included in the results. For example, there's a line number, the quantity of the component item that is needed for the assembly, the unit of measure (because we can't assume that the unit of measure is "eaches"), and the item source.

Regarding item source (i.e. "AssemblyItemMember.ItemSource"), most of the time you'll see "STOCK" as the value. However, depending on the nature of the items that you're assembling, you might also see a value of "Phantom."

A "phantom" is essentially a sub-assembly that is not stocked, and is assembled when it's needed in order to assemble a parent assembly item. If you're using SuiteQL to generate a bill of materials ("BOM") for an assembly item, then be sure to keep this in mind. To get the complete BOM for an assembly, (i.e. the BOM for the assemblyand all its sub-assemblies), then you'll need to run multiple queries and essentially "drill down" into the sub-assemblies.

Get An Assembly Item and Its Components In A Single Query

We can combine the previous queries to get an assembly item and its components in a single query.

SELECT
	'<a href="/app/common/item/item.nl?id=' || AssemblyItem.ID || '" target="_item_' || AssemblyItem.ID || '">View</a>' AS AssemblyItemLink,
	AssemblyItem.ID AS AssemblyItemID,
	AssemblyItem.ItemID AS AssemblyItemItemID, 	
	AssemblyItem.Description AS AssemblyItemDec,
	'<a href="/app/common/item/item.nl?id=' || AssemblyItemMember.Item || '" target="_item_' || AssemblyItemMember.Item || '">View</a>' AS MemberItemLink,
	AssemblyItemMember.LineNumber,	
	AssemblyItemMember.Item AS MemberItemID,
	BUILTIN.DF( AssemblyItemMember.Item ) AS MemberItemItemID,	
	AssemblyItemMember.Quantity AS MemberItemQty,
	BUILTIN.DF( AssemblyItemMember.MemberUnit ) AS MemberItemUOM,
	AssemblyItemMember.ItemSource AS MemberItemSource,
	MemberItem.ItemType AS MemberItemType,
	MemberItem.Description AS MemberItemDesc
FROM
	Item AS AssemblyItem
	INNER JOIN AssemblyItemMember ON
		( AssemblyItemMember.ParentItem = AssemblyItem.ID )
	INNER JOIN Item AS MemberItem ON
		( MemberItem.ID = AssemblyItemMember.Item )
WHERE	
	( AssemblyItem.ID = 999999 )
ORDER BY
	AssemblyItemMember.LineNumber

Again, keep in mind that if you need the components of any phantom assemblies, then you'll have to run the same query for each of the phantom assemblies. As of this post, we have no way to recursively call a query in SuiteQL.

Wrapping Up

In this post I've shared a few SuiteQL queries to help you get information on assembly items and their components. As always, I hope you find the queries to be helpful.

One final note: The final query has been added to the SuiteQL Query Tool's shared "Remote Library." To locate it, open the library and do a search for "Assembly Item."