Published on July 8, 2021.
A NetSuite client recently contacted me, and asked for help with a query involving items abd bins. She needed to identify items that were in stock and stored in bins that were not the item's primary bin.
Here's the query that I provided to her.
SELECT Item AS ItemD, BUILTIN.DF( Item ) AS ItemName, Bin AS BinID, BUILTIN.DF( Bin ) AS BinName, OnHand FROM ItemBinQuantity WHERE ( PreferredBin = 'F' ) AND ( OnHand > 0 ) ORDER BY Item
As you can see, this is a simple query, involving a single table. Everything that my client needed was available in the ItemBinQuantity table, including the item ID, the bin ID, the quantity of the item in the bin, and a column indicating if the bin is "preferred" (i.e. the "primary" bin). I used the BUILTIN.DF function to provide the item and bin names, thus avoiding the need to join to the Item and Bin tables.
Do you have a question about this query, or SuiteQL in general? Feel free to contact me and I'll do my best to help.
Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on NetSuite, including mobile apps, Web portals, Web APIs, and more.
I'm the developer of several popular NetSuite open source solutions, including the SuiteQL Query Tool, SuiteAPI, and more.
I founded SuiteStep, a NetSuite development studio, to provide custom software and AI solutions - and continue pushing the boundaries of what's possible on the NetSuite platform.
Copyright © 2025 Tim Dietrich.