Published on January 26, 2024.

In some of my more recent blog posts, I've written about some SuiteQL queries that I've developed for a custom mobile app that I'm working on for one of my NetSuite clients. In this post, I'll share another query that I've developed.

One of my client's requests was that the app provide their sales reps with visibility into customer transactions - essentially a transaction ledger. They wanted the ledger to be similar to what can be seen in NetSuite when viewing a customer and the Sales > Transactions sublist.

Here's an example.

Click the image to view a larger version.

Here's the SuiteQL query that I developed to handle this request.

SELECT
	Transaction.TranDate,
	Transaction.ID AS Transaction,
	BUILTIN.DF( Transaction.Type ) AS Type,
	Transaction.TranID,
	Transaction.OtherRefNum,		
	Transaction.ForeignTotal,
	BUILTIN.DF( Transaction.Currency ) AS Currency,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
	BUILTIN.DF( Mainline.CreatedFrom ) AS CreatedFrom
FROM
	Transaction
	INNER JOIN TransactionLine AS Mainline ON
		( Mainline.Transaction = Transaction.ID )
		AND ( Mainline.MainLine = 'T' )	
WHERE
	( Transaction.Entity = 237 )
	AND ( Transaction.Void = 'F' )
	AND ( Transaction.Voided = 'F' )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID DESC

The query is very simple. Nearly all of the data is available via the Transaction table.

For some types of transactions, such as invoices and item fulfillments, knowing the source of those transactions can be helpful. You can get a transaction's source from its mainline, and specifically from the CreatedFrom column. Using that value with the BUILTIN.DF function, you can get the source transaction's type and ID without needing to join to it. To get to the mainline, a JOIN to the TransactionLine was required.

To get a "clean" version of each transaction's status, I'm using a combination of the REPLACE and BUILTIN.DF functions. I'm using those functions to remove the transaction type from the status. For example, instead of getting the status as "Payment : Deposited" you get "Deposited" instead.

Here's a short animation showing what the function looks like in the mobile app. In this example, the iOS version of the app is being displayed.

Click the image to view a larger version.

I have a few additional queries that I've developed for this project, and I'll be sharing them over the next few weeks.