Published on September 12, 2021.

Of all of the SuiteScript script types that NetSuite supports, the map/reduce script type is probabaly the most difficult to understand - especially when you first start using it. But map/reduce scripts are a great way to do batch processing, or when you need to work with large volumes of data. So taking the time to understand them is time well spent.

A question that I get asked frequently is, "Can I use SuiteQL in map/reduce scripts?" I usually get asked this by developers who are familiar with map/reduce scripts, but have only ever used them with searches (i.e. the "N/search" module).

The good news is that yes, you can use SuiteQL queries in map/reduce scripts. Even better news is that it's easy to use SuiteQL queries in map/reduce scripts. And in this post, I'll show you how to do that.

Before I continue, I want to mention that there is a SuiteAnswers article titled "Examples of Using SuiteQL in the N/query Module" (SuiteAnswers# 91329) that includes an example of a SuiteQL query being used in the getInputData phase of a map/reduce script. The approach used in that article works, but I prefer the technique that I'm going to present below. The reason is that when you use the approach used in the SuiteAnswers article, the data passed from the getInputData phase to the map phase isn't well structured and is therefore a bit awkward to use. With the technique that I'm going to show below, the resulting data is well structured and very easy to use.

A Map/Reduce SuiteQL Example

Let's start with an example of a map/reduce script that uses SuiteQL in its getInputData phase.

/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
* @NModuleScope Public
*/

var 
	query,
	record;


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


function main( queryModule, recordModule ) {

	query = queryModule;
	record = recordModule;
	
    return {
        getInputData: getInputData,
        map: map,
        reduce: reduce,
        summarize: summarize
    };	    
    
}


function getInputData( context ) {

	var sql = `
		SELECT
			Transaction.ID
		FROM
			Transaction
		 WHERE 
			Type = 'PurchOrd'
		`;	
		
	var queryParams = new Array();	
	
	var rows = selectAllRows( sql, queryParams );
	
	log.audit( { title: 'getInputData - number of rows selected', details: rows.length } );
	
	return rows;

}


function map( context ) {

	// log.debug( { title: 'map - result', details: context } );

	let result = JSON.parse( context.value );
	
	try {
			
		record.submitFields(
			{ 
				type: 'purchaseorder',
				id: result.id,
				values: {
					'memo': 'Updated by Map/Reduce script.'
				}
			}
		);	
		
	} catch( e ) {		

		log.error( { title: 'map - error', details: { 'context': context, 'error': e } } );

	}		
	
}


function reduce( context ) {

	log.audit( { title: 'reduce - context', details: context } );

	// let result = context.values.map( JSON.parse );
	
}


function summarize( context ) {

	log.audit( { title: 'summarize - context', details: context } );

}


function selectAllRows( sql, queryParams = new Array() ) {

	try {	

		var moreRows = true;	

		var rows = new Array();						

		var paginatedRowBegin = 1;

		var paginatedRowEnd = 5000;						

		do {			

			var paginatedSQL = 'SELECT * FROM ( SELECT ROWNUM AS ROWNUMBER, * FROM (' + sql + ' ) ) WHERE ( ROWNUMBER BETWEEN ' + paginatedRowBegin + ' AND ' + paginatedRowEnd + ')';

			var queryResults = query.runSuiteQL( { query: paginatedSQL, params: queryParams } ).asMappedResults(); 	
		
			rows = rows.concat( queryResults );	
			
			if ( queryResults.length < 5000 ) { moreRows = false; }

			paginatedRowBegin = paginatedRowBegin + 5000;
		
		} while ( moreRows );
															
	} catch( e ) {		

		log.error( { title: 'selectAllRows - error', details: { 'sql': sql, 'queryParams': queryParams, 'error': e } } );

	}	
	
	return rows;

}

The getInputData Phase

function getInputData( context ) {

	var sql = `
		SELECT
			Transaction.ID
		FROM
			Transaction
		 WHERE 
			Type = 'PurchOrd'
		`;	
		
	var queryParams = new Array();	
	
	var rows = selectAllRows( sql, queryParams );
	
	log.audit( { title: 'getInputData - number of rows selected', details: rows.length } );
	
	return rows;

}

Notice that in the getInputData phase of the script, I'm specifying the SuiteQL query ("sql") that I want to use. In this case, it's a very simple query, where I'm asking for the internal NetSuite record IDs for all transactions that are purchase orders. There are no query parameters used in this example, so the queryParams variable is an empty array.

Note that while the query used in this example is very basic, you could also use much more complicated queries if you need them, with multiple joins, query parameters, and so on.

function selectAllRows( sql, queryParams = new Array() ) {

	try {	

		var moreRows = true;	

		var rows = new Array();						

		var paginatedRowBegin = 1;

		var paginatedRowEnd = 5000;						

		do {			

			var paginatedSQL = 'SELECT * FROM ( SELECT ROWNUM AS ROWNUMBER, * FROM (' + sql + ' ) ) WHERE ( ROWNUMBER BETWEEN ' + paginatedRowBegin + ' AND ' + paginatedRowEnd + ')';

			var queryResults = query.runSuiteQL( { query: paginatedSQL, params: queryParams } ).asMappedResults(); 	
		
			rows = rows.concat( queryResults );	
			
			if ( queryResults.length < 5000 ) { moreRows = false; }

			paginatedRowBegin = paginatedRowBegin + 5000;
		
		} while ( moreRows );
															
	} catch( e ) {		

		log.error( { title: 'selectAllRows - error', details: { 'sql': sql, 'queryParams': queryParams, 'error': e } } );

	}	
	
	return rows;

}

To actually run the query and get the data, I'm passing the SQL query and the query parameters array to a helper function named "selectAllRows." The selectAllRows function actually runs the query and returns all of the results as mapped results. The getInputData function then passes those results on to the map stage of the script.

The map Phase

function map( context ) {

	// log.debug( { title: 'map - result', details: context } );

	let result = JSON.parse( context.value );
	
	try {
			
		record.submitFields(
			{ 
				type: 'purchaseorder',
				id: result.id,
				values: {
					'memo': 'Updated by Map/Reduce script.'
				}
			}
		);	
		
	} catch( e ) {		

		log.error( { title: 'map - error', details: { 'context': context, 'error': e } } );

	}		
	
}

In the map phase, each result (or row) is processed, and this is where you can see how easy it is to reference the data that has been passed to it. The data passed to the map function is in the form of a JSON-encoded string value, so we first have to decode it. But from that point on, you can simply refer to the result's attributes using simple "dot notation." In other words, each result is simply an object that represents a query result row, and the object's attributes are the columns.

In the map phase of this example script, the memo of a purchase order transaction is updated. Admittedly, this is a very simple example. But you can do all sorts of things in a map function. For example, for one of my clients, I'm using a map/reduce script to keep external systems in sync, and doing so by sending API requests (via the N/https module). For another client, I'm using a map/reduce script to check inventory levels from suppliers. The possibilities are endless.

Additional Uses of SuiteQL in Map/Reduce Scripts

I also want to mention that in addition to using SuiteQL in the getInputData phase, you can use it in the map and reduce phases. For example, you can use a query in the getInputData phase to get the IDs of a set of records that you want to work with, and then use another SuiteQL query in the map phase to get additional data.

Map/Reduce SuiteQL Performance

To give you an example of the type of performance that I'm getting when using map/reduce scripts with SuiteQL queries, I ran the example script (shown above) in a test drive account, with the deployment's concurrency limit to 2. The script updated a little over 200 purchase orders in less than 90 seconds.

Comparing Map/Reduce and Scheduled Scripts

A related question that I get asked quite often is, "Should I use a map/reduce script, or a scheduled script?" Scheduled scripts are much easier to understand when compared to map/reduce scripts. However, I've found that the benefits that map/reduce script provide are so significant that I almost always recommend using them. For example, map/reduce scripts are multi-threaded, can use multiple SuiteCloud Processors, and they can yield to other scripts (and even better, they can do so automatically).

Wrapping Up

I hope you find the technique and sample script that I've shared in this article to be helpful. Feel free to reach out to me if you have any questions about it.