Published on July 17, 2022.

A few weeks ago, a colleague asked me for help with a NetSuite SuiteQL query that she was struggling with. The company's CFO had requested a custom dashboard portlet that would show a list of what she referred to as "transactions of interest."

For example, she wanted the portlet to display transactions that have occurred in the past 30 days, that posted to general ledger accounts involving discounts or scrap, in amounts equal to or greater than $100. In addition, she wanted the list to include all transactions that have occurred in the past 30 days in amounts of $10,000 or more, excluding a few specific transaction types.

The approach that I used to help with this project is described below, including the SuiteQL queries and portlet that were developed. But first, here's a screen shot of what the portlet looks like on a NetSuite dashboard.

Click the image to view a larger version.

Transactions Posted to Specific GL Accounts

The first step that I took with this project was to develop SuiteQL queries that retrieve the transactions based on the CFO's criteria. Again, the criteria for the first set of transactions is that they have occurred in the past 30 days, they posted to general ledger accounts involving discounts or scrap, and that they posted in amounts that are equal to or greater than $100.

The key to this query is NetSuite's Transaction Accounting Line table ("TransactionAccountingLine"). This table maps a transaction - or more specifically, a transaction line - to the general ledger accounts that it posted to, and the amounts for each.

It's important to note that by "posted" I mean that a transaction hit the GL as either a posting transaction (had financial impact) or as a non-posting transaction (had no true financial impact, and is being logged against an account register). Examples of non-posting transaction types are Sales Orders, Purchase Orders, Estimates, and so on.

Here's the query that I wrote that meet the requirements for this first set of transactions.

SELECT
	Transaction.TranDate AS Date,
	BUILTIN.DF( Transaction.Type ) AS Type,	
	( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,			
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
	Transaction.Posting,
	Transaction.Memo, 
	TO_CHAR( TransactionAccountingLine.Amount, '$999,999,999.00') AS Amount
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Account ON
		( Account.ID = TransactionAccountingLine.Account )
WHERE 
	( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) ) 
	AND ( TransactionAccountingLine.Posting = 'T' )
	AND ( ABS( TransactionAccountingLine.Amount ) >= 100 )
	AND ( ( Account.DisplayNameWithHierarchy LIKE '%Discount%' ) OR ( Account.DisplayNameWithHierarchy LIKE '%Scrap%' ) )

There's a lot going on in this query, so let's take a minute to review some of the highlights.

I'll start with the FROM clause. As you can see, I'm joining Transactions to their TransactionAccountingLine records, and then to the related general ledger account (Account). I mentioned above that the TransactionAccountingLine table actually maps transaction lines to the accounts that they hit. In cases where you need that level of details, you'd join from the Transaction, to the TransactionLine, and from there to the TransactionAccountingLine.

However, in this case, we don't need that level of detail. We really only need to know how the transaction hit the GL. Therefore, I'm joining directly from the Transaction table to the TransactionAccountingLine table, and bypassing the join to the TransactionLine table altogether. Additionally, if I had joined to the TransactionLine table, then the results of this query would potentially have included the same transaction multiple times, one for each line that hit a GL account that we're interested in.

Moving on to the WHERE clause, the first thing worth noting is the use of the BUILTIN.RELATIVE_RANGES function. I first discussed BUILTIN.RELATIVE_RANGES in this post. I'm using the function to indicate that I want transactions that occurred in the past 30 days ("DAGO30").

I'm also indicating that I only want transactions that actually did have financial impact, and doing so using this: TransactionAccountingLine.Posting = 'T'

To indicate the amount of the transactions that I want returned, I'm using the ABS (absolute value) function. I'm using the function because the amount of the transaction will either be positive or negative, depending on whether they are acting as a debit or credit.

To specify the GL accounts that I'm interested, I'm using a somewhat "sloppy" approach. I'm indicating that the account's DisplayNameWithHierarchy value needs to include the keywords "scrap" or "discount." I describe this approach as sloppy because it isn't precise, and it is, admittedly, quite inefficient, because Oracle can't use an index to resolve the condition. That said, this approach does provide a lot of flexibility, because if a new GL account is added at some point, and it involves scrap or discounts, then no changes will need to be made to the query.

The SELECT clause is pretty straightforward, with a few exceptions.

This expression - ( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID - is used to wrap the transaction ID in a link. This is an approach that I first discussed in this post. The expression creates a link to any transaction, regardless of its type. The resulting tag includes a unique target attribute for each transaction, making it easy for the user to open multiple transactions, each in their own tab or window.

This expression - REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status - returns the transaction's status value without the leading transaction type and colon prefix.

I think the most interesting aspect of the SELECT clause is this expression: TO_CHAR( TransactionAccountingLine.Amount, '$999,999,999.00') AS Amount I've discussed the TO_CHAR function in previous posts, but specifically to change the format in which date values are returned. In this query, I'm using the TO_CHAR function to specify the format of a decimal value, and I'm using a format model to force the value into a dollar format. This is an interesting and easy approach to formatting the amounts, but it makes the assumption that all of the transactions were made using US Dollars as the currency type. In this particular case, it's a safe assumption, because the company doesn't use multiple currencies. But if they were transacting in multiple currencies, I'd have to use a different approach to ensure that the format model used reflected the transaction's currency. Keep this in mind if you're going to use the Portlet in a NetSuite account that uses multiple currencies.

Transactions Based On Amount and Type

The criteria for the second set of transactions is that they have occurred in the past 30 days, in amounts of $10,000 or more, excluding a few specific transaction types. The transaction types that the CFO wanted to be excluded are Estimates, Work Order Completions and Closes, Builds, and Deposit Applications. Here's the query that I developed to get the transactions based on that criteria.

SELECT
	Transaction.TranDate AS Date,
	BUILTIN.DF( Transaction.Type ) AS Type,		
	( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,	
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
	Transaction.Posting,
	Transaction.Memo,
	TO_CHAR( Transaction.ForeignTotal, '$999,999,999.00' ) AS Amount
FROM 
	Transaction
WHERE 
	( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
	AND ( ABS( Transaction.ForeignTotal ) >= 10000 )
	AND ( Transaction.Type NOT IN ( 'Estimate', 'WOCompl', 'WOClose', 'Build', 'DepAppl' ) )	

This query is much less complicated than the first query. Just as I did in the first query, I'm using various functions to get the results formatted in specific ways - transaction IDs wrapped as links, "clean" status values, and amounts formatted as currency.

Combining the Queries

My next step was to combine the two queries, so that the result was a single list of transactions that meet the criteria. I did this using the UNION ALL set operator.

UNION ALL returns all rows from the results of both SELECT statements, and includes duplicate rows. In theory, there should be no duplicate rows in this query. However, I'm using UNION ALL in the event that the query is adjusted in the future and in a way that duplicates become possible. Also, UNION ALL should be a little more efficient than using UNION, because it returns only the distinct rows of the result, which requires some additional overhead.

To use the UNION and UNION ALL set operators, the columns being returned by the individual queries have to match in number and in type. So if you add a column to one query, remember to add a similar column to the other query. And if you change the order of the columns in one query, be sure to make a similar change in the other query.

My first pass at the query was this.

SELECT
	Transaction.TranDate AS Date,
	BUILTIN.DF( Transaction.Type ) AS Type,	
	( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,		
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
	Transaction.Posting,
	Transaction.Memo, 
	TO_CHAR( TransactionAccountingLine.Amount, '$999,999,999.00') AS Amount
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	INNER JOIN Account ON
		( Account.ID = TransactionAccountingLine.Account )
WHERE 
	( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) ) 
	AND ( TransactionAccountingLine.Posting = 'T' )
	AND ( ABS( TransactionAccountingLine.Amount ) >= 100 )
	AND ( ( Account.DisplayNameWithHierarchy LIKE '%Discount%' ) OR ( Account.DisplayNameWithHierarchy LIKE '%Scrap%' ) )

UNION ALL

SELECT
	Transaction.TranDate AS Date,
	BUILTIN.DF( Transaction.Type ) AS Type,		
	( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
	Transaction.Posting,
	Transaction.Memo,
	TO_CHAR( Transaction.ForeignTotal, '$999,999,999.00' ) AS Amount
FROM 
	Transaction
WHERE 
	( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
	AND ( ABS( Transaction.ForeignTotal ) >= 10000 )
	AND ( Transaction.Type NOT IN ( 'Estimate', 'WOCompl', 'WOClose', 'Build', 'DepAppl' ) )	

The result of the query looked like this.

Click the image to view a larger version.

That was a good start, and I could have used that query as the basis for the Portlet. However, the results lacked a few things. For example, there was no way to determine why a transaction was included in the results, and that was particularly confusing in cases where a transaction was listed twice (because it met the criteria of both queries). Also, the CFO wanted to see the transactions in reverse chronological order based on the transaction date.

Here's another version of the query that addresses those issues.

SELECT * FROM (

	SELECT
		Transaction.TranDate AS Date,
		'Discount/Scrap, $100+, 30 Days' AS Reason,
		BUILTIN.DF( Transaction.Type ) AS Type,	
		( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,
		BUILTIN.DF( Transaction.Entity ) AS Entity,
		REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
		Transaction.Posting,
		Transaction.Memo, 
		TO_CHAR( TransactionAccountingLine.Amount, '$999,999,999.00') AS Amount
	FROM 
		Transaction
		INNER JOIN TransactionAccountingLine ON
			( TransactionAccountingLine.Transaction = Transaction.ID )
		INNER JOIN Account ON
			( Account.ID = TransactionAccountingLine.Account )
	WHERE 
		( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) ) 
		AND ( TransactionAccountingLine.Posting = 'T' )
		AND ( ABS( TransactionAccountingLine.Amount ) >= 100 )
		AND ( ( Account.DisplayNameWithHierarchy LIKE '%Discount%' ) OR ( Account.DisplayNameWithHierarchy LIKE '%Scrap%' ) )

	UNION ALL

	SELECT
		Transaction.TranDate AS Date,
		'Transaction, $10k+, 30 Days' AS Reason,
		BUILTIN.DF( Transaction.Type ) AS Type,		
		( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,	
		BUILTIN.DF( Transaction.Entity ) AS Entity,
		REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
		Transaction.Posting,
		Transaction.Memo,
		TO_CHAR( Transaction.ForeignTotal, '$999,999,999.00' ) AS Amount
	FROM 
		Transaction
	WHERE 
		( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
		AND ( ABS( Transaction.ForeignTotal ) >= 10000 )
		AND ( Transaction.Type NOT IN ( 'Estimate', 'WOCompl', 'WOClose', 'Build', 'DepAppl' ) )	
	
)	
ORDER BY
	Date DESC,
	Reason

There are two changes that I made to the query. I added a new "Reason" column to each of the queries. This indicates the result set that each record was in, and the criteria that were met. Also, I'm now using the union of the two queries as a subquery, so that the outer-most query can sort the full result set by date and reason.

The result of that last query looks like this.

Click the image to view a larger version.

Developing The Portlet and Caching The Query Results

With the query completed, my next step was to develop a portlet that used it and displayed the results. I based the portlet on the SuiteQL Query Results Portlet, a script that I shared last February.

The new portlet is included at the end of this post. It is very similar to the SuiteQL Query Results Portlet, with a few exceptions. First, it does not use script parameters. Instead, the portlet name, and the SuiteQL query, are hard-coded into the new script.

The more significant change to the script is the use of the SuiteScript N/cache Module to cache the query results. Caching the results improves the performance of the Portlet, and it renders more quickly. In this use case, the query results are unlikely to change frequently. In this case, I'm caching the results for up to 5 minutes. I've designed the script so that if you don't want to cache the query results, you can simply set the value of the cacheTTL constant to 0.

Wrapping Up

This was a fun project to work on, and it came together quickly. I like that the end result is a framework that can be used to create similar custom portlets. To customize it, all you need to do is add your own SuiteQL query, and if you'd like, adjust the portlet's name and the cache settings.

I hope you find the queries that I've shared in this post, and the SuiteScript, to be helpful.

The Portlet

Here's the SuiteScript, and it can also be downloaded here.

/**
* @NApiVersion 2.1
* @NScriptType Portlet
* @NModuleScope SameAccount
*/

/* 

------------------------------------------------------------------------------------------
Script Information
------------------------------------------------------------------------------------------

Name:
Transaction Monitoring Portlet

ID:
_tx_monitor

Description:
Displays transactions that meet certain criteria.

Portlet Type:
Simple List


------------------------------------------------------------------------------------------
Developer(s)
------------------------------------------------------------------------------------------

TD:
• Tim Dietrich
• timdietrich@me.com


------------------------------------------------------------------------------------------
History
------------------------------------------------------------------------------------------

20220710 - TD
Initial version, based on the SuiteQL Query Results Portlet:
https://timdietrich.me/blog/netsuite-suiteql-query-results-portlet/


*/

	
const title = 'Transactions For Review';
	
	
const sql = `

	SELECT * FROM (

		SELECT
			Transaction.TranDate AS Date,
			'Discount/Scrap, $100+, 30 Days' AS Reason,
			BUILTIN.DF( Transaction.Type ) AS Type,	
			( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,			
			BUILTIN.DF( Transaction.Entity ) AS Entity,
			REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
			Transaction.Posting,
			Transaction.Memo, 
			TO_CHAR( TransactionAccountingLine.Amount, '$999,999,999.00') AS Amount
		FROM 
			Transaction
			INNER JOIN TransactionAccountingLine ON
				( TransactionAccountingLine.Transaction = Transaction.ID )
			INNER JOIN Account ON
				( Account.ID = TransactionAccountingLine.Account )
		WHERE 
			( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) ) 
			AND ( TransactionAccountingLine.Posting = 'T' )
			AND ( ABS( TransactionAccountingLine.Amount ) >= 100 )
			AND ( ( Account.DisplayNameWithHierarchy LIKE '%Discount%' ) OR ( Account.DisplayNameWithHierarchy LIKE '%Scrap%' ) )
	
		UNION

		SELECT
			Transaction.TranDate AS Date,
			'Transaction, $10k+, 30 Days' AS Reason,
			BUILTIN.DF( Transaction.Type ) AS Type,		
			( '<a href="/app/accounting/transactions/' || LOWER( Transaction.Type ) || '.nl?id=' || Transaction.ID || '" target="_tx' || Transaction.ID || '">' || Transaction.TranID || '</a>' ) AS ID,		
			BUILTIN.DF( Transaction.Entity ) AS Entity,
			REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
			Transaction.Posting,
			Transaction.Memo,
			TO_CHAR( Transaction.ForeignTotal, '$999,999,999.00' ) AS Amount
		FROM 
			Transaction
		WHERE 
			( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
			AND ( ABS( Transaction.ForeignTotal ) >= 10000 )
			AND ( Transaction.Type NOT IN ( 'Estimate', 'WOCompl', 'WOClose', 'Build', 'DepAppl' ) )	
		
	)	
	ORDER BY
		Date DESC,
		Reason
		
`;


const cacheTTL = 300;


const cacheKey = 'records';


var cache, query;


define( [ 'N/cache', 'N/query' ], main );


function main( cacheModule, queryModule ) {

	cache = cacheModule;
	query = queryModule;
	
    return {
        render: renderContent
    }

}


function renderContent( params ) {		

	params.portlet.title = title;	
	
	if ( cacheTTL > 0 ) {
			
		var txCache = cache.getCache( 
			{ 
				name: 'txCache', 
				scope: cache.Scope.PRIVATE 
			}
		);
					
		var records = txCache.get(
			{
				key: cacheKey,
				loader: recordsGet,
				ttl: cacheTTL
			}
		);	
				
		records = JSON.parse( records );	
		
		// params.portlet.title += " (" + records.length + " Transactions)";
		
	} else {
	
		records = recordsGet();
			
	}
	
	if ( records.length > 0 ) {		
		var columnNames = Object.keys( records[0] );							
		for ( i = 0; i < columnNames.length; i++ ) {		
			var columnName = columnNames[i];		
			params.portlet.addColumn(
				{
					id: 'custpage_' + columnName,
					type: 'text',
					label: columnName,
					align: 'LEFT'
				}
			);

		}	
		
		for ( r = 0; r < records.length; r++ ) {
			var record = records[r];			
			var row = {};
			for ( c = 0; c < columnNames.length; c++ ) {
				var columnName = columnNames[c];
				var value = record[columnName];
				if ( value != null ) { value = value.toString(); }				
				row['custpage_' + columnName] = value;  	
			}									
			params.portlet.addRow( { row: row } );			
		}	
		
	}
	
}


function recordsGet() {

	var queryResults = query.runSuiteQL( { query: sql } ); 		
	
	var records = queryResults.asMappedResults();	
	
	return records;

}