Published on May 5, 2022.

I was recently asked by a NetSuite client for help with an interesting reporting project. Their goal was to send weekly email messages to salesreps and their managers, listing all of their customers who either have an overdue balance or have less than 75% of their credit limit remaining.

To fulfill this request, I developed a scheduled script and the following SuiteQL query to gather the data.

SELECT
	Customer.ID,
	Customer.EntityID,
	BUILTIN.DF( Customer.SalesRep ) AS SalesRep,
	SalesRep.Email AS SalesRepEmail,
	BUILTIN.DF( SalesRep.Supervisor) AS Manager,
	Manager.Email AS ManagerEmail,
	Customer.CreditLimit,
	( Customer.CreditLimit - Customer.BalanceSearch ) AS CreditRemaining,
	CASE
		WHEN COALESCE( Customer.CreditLimit, 0 ) = 0 THEN 0
		ELSE
			ROUND( ( ( ( Customer.CreditLimit - Customer.BalanceSearch ) / Customer.CreditLimit ) * 100 ), 2 ) 
		END AS CreditRemainingPCT,
	BUILTIN.DF( Customer.Terms ) AS Terms,
	Customer.OnCreditHold,
	Customer.BalanceSearch AS BalanceTotal,
	( Customer.BalanceSearch - Customer.OverdueBalanceSearch ) AS BalanceCurrent,
	Customer.OverdueBalanceSearch AS BalanceOverdue
FROM
	Customer
	INNER JOIN Employee AS SalesRep ON
		( SalesRep.ID = Customer.SalesRep )
	INNER JOIN Employee AS Manager ON
		( Manager.ID = SalesRep.Supervisor )
WHERE
	( Customer.OverdueBalanceSearch > 0 )
	OR (
		( COALESCE( Customer.CreditLimit, 0 ) > 0 ) AND 
		( ( ( Customer.CreditLimit - Customer.BalanceSearch ) / Customer.CreditLimit ) < .25 )
	)
ORDER BY
	Customer.EntityID

Most of the data is sourced from the Customer table. It conveniently gives us access to a customer's total open balance ("BalanceSearch") and the total amount that is past due ("OverdueBalanceSearch"). The customer's credit limit is also stored in the Customer table.

I'm simply subtracting the open balance from the credit limit to calculate the amount of credit that is remaining. I'm then dividing that amount by the credit limit to get the percentage of credit remaining. Note that I'm using a CASE statement to avoid division by zero errors.

To calculate the current balance due, I'm subtracting the overdue balance from the total balance due.

I'm making two joins to the Employee table - one to get the sales rep, and a second to get the rep's manager. These joins also give me access to their email addresses.

And finally, in the WHERE clause, I'm filtering to get customers who either have an overdue balance, or who have less than 25% of their credit remaining. Again, note the additional code needed to prevent a division by zero error.

This project was easy because of the conveniences provided by the Customer table. There's no need to complicate the query in order to calculate the open and past due balances.

If you've been using SuiteQL for awhile, you might find yourself using the more generic Entity table to get data on customers. Don't forget about the Customer table, as well as similar tables such as Vendor and Partner.