Published on March 31, 2021.

As I've mentioned in a few recent posts, I'm developing a custom employee portal for one of my NetSuite clients. One of the functions that they've requested is designed to give their salesreps and managers a fast, convenient summary of where things stand in terms of their customer balances. They want to be able to see, at a glance, all customers that have an open accounts receivable balance and/or open sales orders. The goal is to provide salesreps with the information that they need to better manage their customer relationshops by being made aware of existing and potential credit issues.

As part of the Customer table, NetSuite provides several convenient columns that can be used via SuiteQL queries to get the data needed for this type of request. Here's an example of a query that I'm using for this project.

SELECT
	ID,	
	CompanyName,
	EntityID,
	BUILTIN.DF( Terms ) AS Terms,
	OnCreditHold,
	CreditLimit,
	BalanceSearch,
	OverdueBalanceSearch,
	UnbilledOrdersSearch
FROM
	Customer
WHERE
	( SalesRep = 15 )	
	AND ( ( BalanceSearch > 0 ) OR ( UnbilledOrdersSearch > 0 ) )
ORDER BY
	CompanyName

The columns that are particularly helpful are BalanceSearch, OverdueBalanceSearch, and UnbilledOrdersSearch. BalanceSearch is the total accounts receivable balance for the customer, and OverdueBalanceSearch is the total amount that is overdue. UnbilledOrdersSearch is the total amount of open sales orders for the customers.

I've included a few additional columns that I believe the salesreps will find helpful, including the customer's payment terms, their credit limit, and the "OnCreditHold" column value so that they can easily see if a customer is already on credit hold.

In terms of filtering, I'm retrieving customers for a specific salesrep, and only including customers that have open receivables or open orders balances. You can easily adjust the query to only return customers that have overdue balances.

SELECT
	Customer.ID,	
	Customer.CompanyName,
	Customer.EntityID,
	BUILTIN.DF( Customer.Terms ) AS Terms,
	Customer.OnCreditHold,
	Customer.CreditLimit,
	Customer.BalanceSearch,
	Customer.OverdueBalanceSearch,
	Customer.UnbilledOrdersSearch
FROM
	Customer
WHERE
	( SalesRep = 15 )	
	AND ( OverdueBalanceSearch > 0 )
ORDER BY
	Customer.CompanyName

I hope you find these queries to be helpful. If so, then you might also be interested in the Accounts Receivable aging queries that I shared in this blog post from last November.

If you have any questions, please feel free to reach out to me.