Published on November 7, 2021.

A NetSuite client recently asked for help with SuiteQL. They wanted queries that they can use to analyze customer deposits, and eventually use in a Customer Web portal.

In this post, I'll share some of the queries that I created for my client, and walk you through the process that I used to create the queries.

Customer Deposits

Let's start by taking a look at a customer deposit as it appears in the NetSuite UI. Here's a screen shot of a sample deposit.

As you can see, this is a customer deposit received from a customer named "Altima Technology." It was received on 10/28/2021, in the amount of $2,500.00 USD. The deposit was made by check, and the check number was 2120.

Here's a second screen shot of the deposit, showing the "Applied To" tab.

This shows that the deposit was applied to Invoice 216, on 10/30/2021, and that $1,478.00 was applied. Remember that the deposit amount was $2,500.00. So the deposit has only been partially applied.

In NetSuite, customer deposits are stored in the Transaction table, and the transaction type is "CustDep." To locate this deposit using SuiteQL, we can use a query like this.

SELECT
	ID,
	TranDate,
	TranID,
	BUILTIN.DF( PaymentMethod ) AS PaymentMethod,
	OtherRefNum AS ReferenceNumber,
	ForeignTotal AS PaymentAmount,
	ForeignPaymentAmountUsed AS AmountApplied,
	Memo,
	Entity AS CustomerID,
	BUILTIN.DF( Entity ) AS CustomerName,
	REPLACE( BUILTIN.DF( Status ), BUILTIN.DF( Type ) || ' : ', '' ) AS Status
FROM 
	Transaction 
WHERE 
	( Type = 'CustDep' )	
	AND ( Entity = 240 )
	AND ( Voided = 'F' )
ORDER BY
	TranID

In that query, I'm asking for all customer deposits for customer 240 (the internal ID for Altima Technology), and I'm also specifying that I want voided transactions to be excluded. The query result will look like this.

As you can see, the query results reflect what we're seeing in the NetSuite UI. The deposit was made on 10/28/2021, via check 2120, in the amount of $2,500.00. Additionally, we can see that $1,478.00 has been applied.

Before we continue, let's take a look at a similar query.

SELECT
	ID,
	TranDate,
	TranID,
	BUILTIN.DF( PaymentMethod ) AS PaymentMethod,
	OtherRefNum AS ReferenceNumber,
	ForeignTotal AS PaymentAmount,
	ForeignPaymentAmountUsed AS AmountApplied,
	Memo,
	Entity AS CustomerID,
	BUILTIN.DF( Entity ) AS CustomerName,
	REPLACE( BUILTIN.DF( Status ), BUILTIN.DF( Type ) || ' : ', '' ) AS Status
FROM 
	Transaction 
WHERE 
	( Type = 'CustDep' )
	AND ( Voided = 'F' )
	AND ( ForeignTotal > ForeignPaymentAmountUsed )
ORDER BY
	TranID

With this query, I'm requesting all customer deposits that haven't been fully applied. You might find that query to be helpful.

Deposit Applications

Now let's look at the deposit application. In the NetSuite UI, it looks like this.

As you might expect, deposit applications are also stored in the Transaction table. Their transaction type is "DepAppl."

In terms of SuiteQL, we want a query that will return the application transactions for a given deposit. Continuing with the example above, the internal ID of the Customer Deposit was 25512.

Here's what the query will look like.

SELECT
	Transaction.ID,
	Transaction.TranDate,
	Transaction.TranID,
	Transaction.ForeignTotal AS PaymentAmount,
	Transaction.ForeignPaymentAmountUsed AS AmountApplied
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.Mainline = 'T' )
WHERE
	( Transaction.Type = 'DepAppl' )
	AND ( TransactionLine.CreatedFrom = 25512 )

Notice that I'm joining to the main TransactionLine associated with the deposit application transaction, and then using that to filter on the "CreatedFrom" value. Again, 25512 is the internal ID of the Customer Deposit. So again, we're asking for deposit applications that were made against the specified customer deposit.

The results of that query will look like this.

Deposit Application Details

At this point, we have queries for locating a customer deposit, and the related applications of the deposit. What we're missing are the details of the application. In other words, we need a way to identify the transactions that the deposit applied to.

To locate that information, we'll need to make use of the NextTransactionLineLink table. I wrote about this in March of this year, in a blog post titled NetSuite: Use SuiteQL to Get Related Transactions. If you aren't already familar with the NextTransactionLineLink table, I encourage you to read that post. I think you'll find that the table will prove to be helpful when you need a way to relate many different types of transactions.

In this case, I'm using the NextTransactionLineLink table to identify the invoices that a deposit was applied to. Let's look at the query.

SELECT
	BUILTIN.DF( NT.Type ) AS Type,
	NT.TranID,
	DATL.AmountLinked AS AmountApplied
FROM
	NextTransactionLineLink AS NTLL
	INNER JOIN Transaction AS NT ON
		( NT.ID = NTLL.PreviousDoc  )
	INNER JOIN TransactionLine AS DATL ON
		( DATL.Transaction = NTLL.NextDoc  )
		AND ( DATL.ID = NTLL.NextLine )
WHERE
	( NTLL.NextDoc = 25514 )
ORDER BY
	NT.ID

In this query, 25514 is the internal ID of the Deposit Application transaction. It is not the ID of the Customer Deposit transaction itself (which had an ID of 25512).

The link to the TransactionLine is necessary because we need to know the actual amount that was applied to the invoice. We cannot assume that it was paid in full. The join to the TransactionLine is done using the "NextDoc" and "NextLine" values, so we're moving from the invoice that the deposit was applied to corresponding deposit application's transaction line, which is where the application detail amounts are actually stored.

The query result looks like this.

Wrapping Up

We now have queries that can be used to go from a customer deposit, to the applications of the deposit, and all the way down to the specific transactions that the deposit was applied to. The queries that I've shared in this post can be used in a number of different ways. My client is initially using them to identify open and partially applied deposits and generally "clean them up." Longer the term, the plan is to use versions of these queries in their Customer Web portal.

One More Thing

You might have noticed that the screen shots used in this post look a little different from those that I've shared in the past. The reason is that I was running the queries in a macOS native version of the SuiteQL Query Tool. Here's an animation that shows the application.

I'm developing this version of the SuiteQL Query Tool using Xojo, which I've mentioned in previous posts. If you're not familiar with Xojo, it's a cross-platform application development tool that can be used to develop native applications for macOS, Windows and Linux. You can use it to develop desktop apps, mobile apps (currently only iOS, but Anrdoid support is on the way), console apps, and Web apps. You can also use it to develop Raspberry Pi apps. At the moment, I'm using a beta version of Xojo. Specifically, I'm using Xojo 2021 Release 3 Build 54689 - and I'm happy to report that it is working perfectly.

Under the hood, the app is making calls to a NetSuite RESTlet, and doing so via a custom Xojo class ("NSRestletConnection") that is a subclass of the URLConnection class. My plan is to swap this out, and make the NetSuite calls via the SuiteTalk REST API.

I've been really impressed by the speed of the application. It doesn't feel any slower than the NetSuite-native version of the tool. I'm still not sure of what I'll do with the application, but most likely I'll make it available under an open source license.

If you have any questions about the queries that I've shared in the post - or about Xojo - please let me know.