Published on September 14, 2020.

One of my NetSuite clients has started to outsource their Accounts Receivable to a firm that specializes in debt collections. To expedite the process, the collection firm requested that we provide them with an API that they can use to retrieve customer information from NetSuite.

In this post, I'm going to share some of the SuiteQL queries that I wrote to complete the project. We'll start by pulling some basic information from the Customers table, discuss some very helpful Accounts Receivable-related columns that are available, and then discuss joining to the customer default billing and shipping addresses.

The Customer Table

Let's start with a simple query to pull basic information from the Customer table.

SELECT
	Customer.EntityID AS ID,
	Customer.CompanyName AS Name,
	Customer.CreditLimit,
	Customer.OnCreditHold,
	BUILTIN.DF( Customer.Terms ) AS PaymentTerms,
	BUILTIN.DF( Customer.SalesRep ) AS SalesRep
FROM
	Customer
WHERE
	( Customer.IsInactive = 'F' )
ORDER BY
	Customer.EntityID

As you can see, I'm retrieving the Customer's ID and name, as well as their credit limit. The "OnCreditHold" column is a boolean value indicating whether or not the customer is on credit hold.

The next two values make use of the SuiteQL built-in "DF" function, which I wrote about in an earlier post. (See "SuiteQL Built-In Functions Revisited.) I'm using the DF function to map the numeric Terms and SalesRep columns to text values.

In the WHERE clause, I'm indicating that I only want customers that are active to be returned.

Retrieving Customer Balances

That's a good start, but I also need to provide the collection firm with information about each customer's open and past due balances. In addition, the firm only wants customers that have a past due balance.

On the Customer table, two columns are available that make this very easy. The first column is "BalanceSearch," which represents the total balance owed by the customer. A second column, "OverdueBalanceSearch," represents the total balance owed by the customer that is past due.

Here's the updated SQL query.

SELECT
	Customer.EntityID AS ID,
	Customer.CompanyName AS Name,
	Customer.CreditLimit,
	Customer.OnCreditHold,
	BUILTIN.DF( Customer.Terms ) AS PaymentTerms,
	BUILTIN.DF( Customer.SalesRep ) AS SalesRep,
	Customer.BalanceSearch AS BalanceTotal,
	Customer.OverdueBalanceSearch AS BalancePastDue
FROM
	Customer
WHERE
	( Customer.IsInactive = 'F' )
	AND ( Customer.OverdueBalanceSearch > 0 )
ORDER BY
	Customer.EntityID	

Notice that I've added the two columns to the result, and that I've also updated the WHERE clause so that only active customers that have a past due balance are returned.

The Default Billing Address

The only thing left to add is the customer default billto address. This can be a little tricky because it involves joining to two additional tables. One of those tables is EntityAddressbook, which serves as a join table between an entity (which in this case is the customer) and their addresses, which are stored in a table named EntityAddress.

Here's an updated query that shows how the joins work.

SELECT
	Customer.EntityID AS ID,
	Customer.CompanyName AS Name,
	Customer.CreditLimit,
	Customer.OnCreditHold,
	BUILTIN.DF( Customer.Terms ) AS PaymentTerms,
	BUILTIN.DF( Customer.SalesRep ) AS SalesRep,
	Customer.BalanceSearch AS BalanceTotal,
	Customer.OverdueBalanceSearch AS BalancePastDue,
	BillToAddress.Addressee AS BillToAddressee,
	BillToAddress.Addr1 As BillToAddress1,
	BillToAddress.Addr2 As BillToAddress2,
	BillToAddress.Addr3 As BillToAddress3,
	BillToAddress.City As BillToCity,
	BillToAddress.State As BillToState,
	BillToAddress.Zip As BillToZip,
	BillToAddress.Country As BillToCountry,
	BillToAddress.Attention As BillToAttention
FROM
	Customer
	LEFT OUTER JOIN EntityAddressbook AS DefaultBilling ON
		( DefaultBilling.Entity = Customer.ID )
		AND ( DefaultBilling.defaultbilling = 'T' )
	LEFT OUTER JOIN EntityAddress AS BillToAddress ON
		( BillToAddress.nkey = DefaultBilling.AddressBookAddress )
WHERE
	( Customer.IsInactive = 'F' )
	AND ( Customer.OverdueBalanceSearch > 0 )
ORDER BY
	Customer.EntityID

The first join is from the Customer table to the EntityAddressbook table (the "join table"). That join is based on the customer's ID. Notice that in that join, I'm also indicating that I want the record that is flagged as being the default billing address. Also notice that I'm aliasing the EntityAddressbook table to make it clear that the record is being used to join to the default billing addresses. More on that in a moment.

The second join is from the EntityAddressbook table to the EntityAddress table, which is where the actual address information is stored. The column names that are used in this join (nkey and AddressBookAddress) are a little ambiguous. And again, notice that I'm aliasing the EntityAddress table as "BillToAddress" to make it very clear that this represents the billing address.

One final comment about the query: I'm using LEFT OUTER JOINs, and that's because it might be possible that a customer does not have a default billing address. In that case, I want to bse sure that the customer is still included in the result.

The Shipping Address and Sales Rep

At this point, the query was returning everything that the collection firm needed. However, I also added some additional information for future use.

First, I added the customer's default shipping address, which involved a second set of joins to the EntityAddressbook and EntityAddress tables. I also added more detailed information about the customer's sales rep by joining to the rep's record in the Employee table.

The final query is included below.

SELECT
	Customer.EntityID AS ID,
	Customer.CompanyName AS Name,
	Customer.CreditLimit,
	Customer.OnCreditHold,
	BUILTIN.DF( Customer.Terms ) AS PaymentTerms,
	Customer.BalanceSearch AS BalanceTotal,
	Customer.OverdueBalanceSearch AS BalancePastDue,
	BillToAddress.Addressee AS BillToAddressee,
	BillToAddress.Addr1 As BillToAddress1,
	BillToAddress.Addr2 As BillToAddress2,
	BillToAddress.Addr3 As BillToAddress3,
	BillToAddress.City As BillToCity,
	BillToAddress.State As BillToState,
	BillToAddress.Zip As BillToZip,
	BillToAddress.Country As BillToCountry,
	BillToAddress.Attention As BillToAttention,
	ShipToAddress.Addressee AS ShipToAddressee,
	ShipToAddress.Addr1 As ShipToAddress1,
	ShipToAddress.Addr2 As ShipToAddress2,
	ShipToAddress.Addr3 As ShipToAddress3,
	ShipToAddress.City As ShipToCity,
	ShipToAddress.State As ShipToState,
	ShipToAddress.Zip As ShipToZip,
	ShipToAddress.Country As ShipToCountry,
	ShipToAddress.Attention As ShipToAttention,
	SalesRep.LastName AS SalesRepLastName, 
	SalesRep.FirstName AS SalesRepFirstName, 
	SalesRep.Email AS SalesRepEmail,
	SalesRep.Phone AS SalesRepPhone
FROM
	Customer
	LEFT OUTER JOIN EntityAddressbook AS DefaultBilling ON
		( DefaultBilling.Entity = Customer.ID )
		AND ( DefaultBilling.defaultbilling = 'T' )
	LEFT OUTER JOIN EntityAddress AS BillToAddress ON
		( BillToAddress.nkey = DefaultBilling.AddressBookAddress )
	LEFT OUTER JOIN EntityAddressbook AS DefaultShipping ON
		( DefaultShipping.Entity = Customer.ID )
		AND ( DefaultShipping.defaultbilling = 'T' )
	LEFT OUTER JOIN EntityAddress AS ShipToAddress ON
		( ShipToAddress.nkey = DefaultShipping.AddressBookAddress )
	LEFT OUTER JOIN Employee AS SalesRep ON
		( SalesRep.ID = Customer.SalesRep )
WHERE
	( Customer.IsInactive = 'F' )
	AND ( Customer.OverdueBalanceSearch > 0 )
ORDER BY
	Customer.EntityID

Wrapping Up

This is another great example of how you can use SuiteQL, either as part of a RESTlet or via SuiteTalk REST, to very quickly provide an integration solution. In this case, I took the SuiteQL query, included it in a RESTlet, and exposed the RESTlet via a middleware layer. Both my customer and the collection firm were thrilled, not only with the speed with which I was able to provide the solution, but also with how fast the API is able to respond to requests and provide the data.

Have a question about SuiteQL? Need help with a query? Feel free to reach out to me.