Published on August 21, 2021.

A NetSuite client recently reached out to me with a couple of interesting SuiteScript and SuiteQL questions. This client is a wholesale distribution business that is in the process of implementing NetSuite OneWorld with multiple subsidiaries and multiple inventory locations.

When setting up an item, my client assigns it to one or more of their subsidiaries. By doing so, the item can only be referenced on transactions for one of those subsdiaries.

My client actually had two questions about this. First, they wanted to know how to assign an item to multiple subsidiaries via SuiteScript. And second, they wanted to know how to query NetSuite to get the item / subsidiary assignments.

Assinging An Item to Multiple Subsidiaries

Assigning an item to multiple subsidiaries programmatically via SuiteScript is actually quite easy to do. Instead of setting the Subsidiary field to the ID of a single subsidiary (as an integer value), you set it as an array of subsidiary IDs (i.e. an array of integers). For example, here's a SuiteScript snippet that shows this technique:

item.setValue( { fieldId: 'Subsidiary', value: [4, 7] } );

So in that example, you'd be assigning the item to the two subsidiaries whose IDs are 4 and 7.

Querying for Item Subsidiary Assignments

With regards to querying for the item / subsidiary assignments, the data that is needed is stored in a join table named "itemSubsidiaryMap." It is one of the most basic tables that I've come across, with only two fields: Item (the ID of the item) and Subsidiary (the ID of the subsidiary that the item is assigned to).

So, given NetSuite's internal ID for an item, you would query for its subsidiaries like this:

SELECT 
	* 
FROM 
	itemSubsidiaryMap 
WHERE 
	Item = 1841

And the result (as it appears in the SuiteQL Query Tool) will look something like this:

Suppose that, instead of (or in addition to) the subsidiary IDs, you want the subsidiary names. For whatever reason, you cannot use the BUILTIN.DF function on the itemSubsidiaryMap.Subsidiary field to get the corresonding subsidiary name. So instead, you have to join to the Subsidiary table like this:

SELECT 
	itemSubsidiaryMap.Subsidiary AS SubsidiaryID,
	Subsidiary.Name AS SubsidiaryName
FROM 
	itemSubsidiaryMap 
	INNER JOIN Subsidiary ON
		( Subsidiary.ID = itemSubsidiaryMap.Subsidiary )
WHERE 
	Item = 1841

Querying for Inventory Location Data

I mentioned that my client also maintains inventory in multiple locations. NetSuite maintains location-specific item information, such as quantity on hand, quantity in transit, reorder points, last inventory count, and so on, in a table named "inventoryItemLocations."

To query for an item's location-specific information, you'd use a query like this:

SELECT
	*
FROM
	inventoryItemLocations 
WHERE
	Item = 1021

Summary

In this post, I introduced two item-related mapping tables: itemSubsidiaryMap, and inventoryItemLocations. Those tables are very helpful in cases where you want to use SuiteQL to query a NetSuite account where multiple subsidiairies and/or multiple inventory locations are involved. I also showed a simple technique for assigning an item to multiple subsidiaries via SuiteScript.

I hope you found this post to be helpful. As always, let me know if you have any questions about it. Or better yet, join us over on the NetSuite Professionals Slack Community.