SuiteQL: Useful Tables

Introduction

The SuiteQL series is coming to an end. I have attempted to write somewhat in order with each post building on the previous one. By now you should have a good understanding of what issues you may encounter when using SuiteQL and how to solve them.

In this post I will talk about some tables that aren’t complicated enough to warrant their own post but are still invaluable for writing queries.

I’ll add more tables as I think of them.

InventoryBalance

This table is a great way to get the current quantity on hand in NetSuite.

Here are the columns you need to know about:

Column Name	Description	Type
item	The internal id of the item	integer
location	The internal id of the location	integer
quantityonhand	The quantity on hand	float
quantityavailable	The quantity available	float
status	The status of the item	string
binnumber	The internal id of the bin	integer

There’s some more columns; I suggest you look at the query tool to see if you need them.

Account

This table can help you get account information.

Some columns you may find useful:

Column Name	Description	Type
id	The internal id of the account	integer
acctnumber	The account number	string
acctType	The account type	string (You may want to use BUILTIN.DF)
balance	The account balance	float
fullname	The full name of the account	string
displaynamewithhierarchy	The full name of the account with the hierarchy	string

Dig around to see what else you need.

Bin

General information about bins. This table is especially useful if you use WMS (Warehouse Management System). You will need to look at the query tool to see what columns you have available.

Column Name	Description	Type
id	The internal id of the bin	integer
binnumber	The bin number	string
zone	The internal id of the zone	integer
… Custom fields	…	…