Published on October 19, 2020.

I recently completed a project for a new NetSuite client that involved their warehouse operations. The client, a large wholesaler of consumer products, approached me about developing a highly customized, browser-based application to streamline their processes and improve the efficiency of their warehouse teams.

This client stocks inventory in multiple warehouses that are geographically located throughout the United States. They're also making heavy use of NetSuite's bin management capabilities, which allow them to identify specific locations within each warehouse, and keep track of the items in each location.

One of the features that my client requested was the ability for a warehouse staff member to easily locate an item based on an item number. Specifically, they wanted to be able to scan a barcode on a packing list, and provide the user with a list of bins that the product is located in, including the quantity available in each bin.

This was an interesting project for me, as it was the first time that I had an opportunity to make use of the warehouse-related tables that are available via SuiteQL. In this post, I'll share some of what I learned while working on the project, as well as some of the queries that I wrote.

Bins

The primary table that I used for this project was, of course, the Bins table. So the first query that I wrote looked like this.

SELECT
	Bin.ID AS BinID,
	Bin.BinNumber,
	Bin.Memo AS BinMemo
FROM
	Bin
WHERE
	( Bin.Location = 1 )
ORDER BY
	Bin.BinNumber

That query simply provides a list of all bins for the given location, ordered by the bin number.

Bin Contents

NetSuite uses a second table, ItemBinQuantity, to track the items that are stored in each bin. Joining that table back to the bins was necessary to make the item lookup possible. Here's what the query looked like.

SELECT
	Bin.ID AS BinID,
	Bin.BinNumber,
	Bin.Memo AS BinMemo,
	ItemBinQuantity.OnHand,
	ItemBinQuantity.OnHandAvail
FROM
	Bin
	INNER JOIN ItemBinQuantity ON
		( ItemBinQuantity.Bin = Bin.ID )
WHERE
	( Bin.Location = 1 )
	AND ( ItemBinQuantity.Item = 101901 )
ORDER BY
	Bin.BinNumber

As you can see, I've simply joined the Bin and ItemBinQuantity table. This allows for filtering by the item ID, and gives us access to the on hand quantity and quantity available values. In this particular example, I'm querying for the bin information for an item with NetSuite's internal ID of 101901.

Item Information

In addition to being able to lookup items based on NetSuite's internal IDs, which are encoded in the barcodes, my client also wanted to provide their warehouse teams with a function to lookup the bin information by manually entering "friendly" item numbers into the application. They also wanted the app's response to include some basic information about the item itself.

In order to do that, I had to make one additional join to the Item table. Here's the revised query.

SELECT
	Bin.ID AS BinID,
	Bin.BinNumber,
	Bin.Memo AS BinMemo,
	Item.ID AS ItemIDInternal,
	Item.ItemID,
	Item.DisplayName,
	Item.Description,
	ItemBinQuantity.OnHand,
	ItemBinQuantity.OnHandAvail,
	ItemBinQuantity.PreferredBin
FROM
	Bin
	INNER JOIN ItemBinQuantity ON
		( ItemBinQuantity.Bin = Bin.ID )
	INNER JOIN Item ON
		( Item.ID = ItemBinQuantity.Item )		
WHERE
	( Bin.Location = 1 )
	AND ( ( ItemBinQuantity.Item = 101901 ) OR ( Item.ItemID = 'MX9N2LL/A' ) )
ORDER BY
	Bin.BinNumber

The revised query makes it possible to filter on either an internal item ID, or a more user-friendly part number. It also provides access to columns on the Item record, and in this case I'm pulling the item name and description.

Wrapping Up

As I mentioned earlier, this item / bin lookup function is just one of the features that my client requested. The application also provides the warehouse team with the ability to submit inventory adjustments, transfer items between bins, print packing slips, and more.

I hope you find the queries that I've shared in this post to be helpful. If you have any questions, please feel free to contact me.