Published on December 22, 2020.

Back in October, I wrote about a NetSuite project that I had been working on that involved developing a custom Web app for their warehouse team. In that post, I shared a few SuiteQL queries that I had developed as part of the project.

Recently, that client came back to me with a request for a new function. They wanted to add a function that could be used to quickly show all empty bins in a specified location.

As a follow-up to that original post, I thought I'd share the SuiteQL query that I developed for this new function. Here it is.

SELECT
	BUILTIN.DF( Bin.Location ) AS BinLocation,
	Bin.BinNumber
FROM
	Bin
	LEFT OUTER JOIN ItemBinQuantity ON
		( ItemBinQuantity.Bin = Bin.ID )
WHERE
	( Bin.Location = 16 )
GROUP BY
	BUILTIN.DF( Bin.Location ),
	Bin.BinNumber
HAVING
	SUM( ItemBinQuantity.OnHand ) = 0
ORDER BY
	BUILTIN.DF( Bin.Location ),
	Bin.BinNumber

Let's take a look at the query, and start with the WHERE clause. I'm indicating that I want the result to only include bins in location 16. Note that if you wanted all empty bins, regardless of their location, you would simply remove the WHERE clause.

In the FROM clause, I'm joining records in the Bin table to the ItemBinQuantity table, and using a LEFT OUTER JOIN to do so. More on that in a moment.

I'm using a GROUP BY clause so that the resulting rows are grouped by the Location and the Bin Number. For the Location, I'm using the BUILTIN.DF function so that the name of each bin is returned, instead of their numeric IDs. By grouping the rows, only one row will be returned for each location / bin number combination.

I'm using a HAVING clause to apply a filter to the rows after they have been grouped. In this case, I'm indicating that I only want rows returned where the sum of the on-hand quantities of the items assigned to the bin is zero. In other words, I only want bins included in the list if the items assigned to it are out of stock.

This brings me back to the FROM clause, and the use of a LEFT OUTER JOIN to join the ItemBinQuantity and Bin tables. I'm using a LEFT OUTER JOIN so that bins that do not have any items assigned to them are included in the list. Had I used an INNER JOIN, those bins would not have been included in the result.

The SELECT and ORDER BY clauses are straightforward. Again, I'm using the BUILTIN.DF function to get the bin names.

I hope you find this query to be helpful. If you have any questions about SuiteQL, please feel free to contact me.