NetSuite: Use SuiteQL to Get Customer Balances for Sales Reps and Managers

Published on January 21, 2024.

Last week I wrote about a custom mobile app that I've been working on for one of my NetSuite clients. The app is designed for the client's sales reps, and gives them access to customer information, sales orders, estimates, inventory information, and more.

One of the app's features is a Customer Balance Overview. It's designed to gives sales reps a quick overview of their customers' balances, including balance due, overdue balance, and deposit balance. My client also wanted the reps to see customers that are on credit gold.

Most of those values are available via the Customer table (which I believe is actually a VIEW). Here's the initial query.

SELECT
	Customer.CompanyName AS Customer,
	Customer.BalanceSearch AS TotalBalance,
	Customer.OverdueBalanceSearch AS OverdueBalance,
	Customer.UnbilledOrdersSearch AS UnbilledOrders,
	Customer.CreditLimit,
	Customer.OnCreditHold
FROM 
	Customer
WHERE
	( Customer.SalesRep = 29 )
	AND ( 
		( Customer.BalanceSearch <> 0 ) 
		OR ( Customer.UnbilledOrdersSearch <> 0 )
		OR ( Customer.OnCreditHold = 'T' )
	)
ORDER BY
	Customer.CompanyName

Adding Deposit Balances

One value that my client wanted to display - and filter on - was the customer deposit balance. That's not available via the Customer table. However, there's another table / view that makes getting that value easy. The table is CustomerCurrencyBalance.

Here's the updated query.

SELECT
	Customer.CompanyName AS Customer,
	Customer.BalanceSearch AS TotalBalance,
	Customer.OverdueBalanceSearch AS OverdueBalance,
	Customer.UnbilledOrdersSearch AS UnbilledOrders,
	Customer.CreditLimit,
	Customer.OnCreditHold,
	CCB.DepositBalance
FROM 
	Customer
	INNER JOIN CustomerCurrencyBalance AS CCB ON
		( CCB.Customer = Customer.ID )
WHERE
	( Customer.SalesRep = 29 )
	AND ( 
		( Customer.BalanceSearch <> 0 ) 
		OR ( Customer.UnbilledOrdersSearch <> 0 )
		OR ( CCB.DepositBalance <> 0 )
		OR ( Customer.OnCreditHold = 'T' )
	)
ORDER BY
	Customer.CompanyName

I've simply added a JOIN to the CustomerCurrencyBalance table, added the DepositBalance column to the SELECT clause, and added the additional criteria to the WHERE clause.

Adding Support for Sales Managers

Another request from my client was that the mobile app include special support for sales managers. Specifically, they wanted sales managers to see the balance information for customers whose sales reps report to a sales manager, as well as any customers that the sales manager handled directly.

Here's the updated query.

SELECT
	Customer.CompanyName AS Customer,
	Customer.BalanceSearch AS TotalBalance,
	Customer.OverdueBalanceSearch AS OverdueBalance,
	Customer.UnbilledOrdersSearch AS UnbilledOrders,
	Customer.CreditLimit,
	Customer.OnCreditHold,
	CCB.DepositBalance,
	BUILTIN.DF( Customer.SalesRep ) AS SalesRep
FROM 
	Customer
	INNER JOIN CustomerCurrencyBalance AS CCB ON
		( CCB.Customer = Customer.ID )
WHERE
	(
		( Customer.SalesRep = 124 )
		OR ( Customer.SalesRep IN ( SELECT ID FROM Employee WHERE Supervisor = 124 ) )
	)
	AND ( 
		( Customer.BalanceSearch <> 0 ) 
		OR ( Customer.UnbilledOrdersSearch <> 0 )
		OR ( CCB.DepositBalance <> 0 )
		OR ( Customer.OnCreditHold = 'T' )
	)
ORDER BY
	Customer.CompanyName

I made two changes to the previous query.

First, I added a subquery to the WHERE clause. It indicates that customers must either be assigned to the specified employee, or assigned to one of the employee's direct reports. The "SELECT ID FROM Employee WHERE Supervisor = 124" subquery returns the IDs of employees that have been assigned to the Supervisor.

Second, I added the SalesRep to the SELECT clause. This allows the sales manager to easily see what rep is assigned to each customer.