Suitescript – Searching/querying

by Posted on

When writing Suitescript, I perform the majority of searches ad-hoc in the code. The primary reason for this is that I find its neater to only define a search when you need it, and not to clutter up the system with actual saved searches.

Before the recent changes in Netsuite, the de-facto way to retrieve information in script was to run a search using the N/search module. However with the addition of the N/query module, SQL queries can be used which are leaner, faster and allow better flexibility.

With that in mind, I have two reusable blocks of code which I use quite frequently.

N/search

The first block combines several different elements of the N/search module to produce an array of object representing the results. This block of code will retrieve all the results it can (until it exceeds any runtime limits!), taking care of the result paging.

/*
*Bulletproof search
*Takes type, filter and columns and returns a paged search array. Page size is set to 1000, however the search will loop through and
*retrieve all the search results.
*/

var arrTranRes = [];
var objTranSea = search.create({
     type: "transaction",
     filters:
     [
        ["jobmain.internalid","anyof",'1234'], 
        "AND", 
        ["mainline","is","T"], 
        "AND", 
        ["type","anyof","ExpRept","Estimate","SalesOrd","VendBill","PurchOrd","CustInvc"]
     ],
     columns:
     [
        search.createColumn({name: "type", label: "Type"}),
        search.createColumn({name: "tranid", label: "Document Number"}),
        search.createColumn({name: "statusref", label: "Status"})
     ]
  });
var objPagedData = objTranSea.runPaged({
  pageSize: 1000
});
objPagedData.pageRanges.forEach(function(pageRange){

  var objPage = objPagedData.fetch({
    index: pageRange.index
  });

  objPage.data.forEach(function(result){
    var obj = {}
    for(var x in objTranSea['columns']){
    var searchValue = result.getValue(objTranSea['columns'][x]);
    obj[objTranSea['columns'][x].label] = searchValue; 		
    }

arrTranRes.push(obj);
  });
});

Type, Filters and Columns

Type should conform to the record type you want to get. The filter and column arrays can be generated from a search using the excellent Chrome extension Search Export by David Smith

Paged Data

As searches in Netsuite are always paged, running a query through Suitescript can also take advantage of this. The code above iterates through the results to build one big large array of both the column names and values.

N/query

Things fortunately get a bit easier to understand with the N/query module.

NOTE: the below code requires the script to be in Suitescript 2.1

try {

	var strSQL = `select customer.id, customer.salesrep as accthand,
	from transaction 
	inner join customer on transaction.entity = customer.id
	where transaction.id = 1234`
	
	var arrRes = [];
	var beginTime = new Date().getTime();
	var queryResults = query.runSuiteQL(
		{
			query: strSQL
		}
	);
	var endTime = new Date().getTime();
	var elapsedTime = endTime - beginTime ;

	// Get the mapped results.
	var records = queryResults.asMappedResults();

	var objSQL = {};

	objSQL.resultCount = records.length;
	objSQL.time = elapsedTime;
	objSQL.results = records;

	log.debug("**SQL Debug - Query & Results**", strSQL+"<br />"+JSON.stringify(objSQL));
	
	return objSQL;                

}catch(e){
	return e+e.stack;
}

strSQL

This is a variable that contains the SQL statement. This can be written in conjunction with the Schema Browser

objSQL

This contains:

  • The amount of results returned
  • The time the query took
  • The result set as an array of objects

The result set can be accessed by referencing the result row, and then the object name. The object names are formed as per the statement, so the below will return the sales rep field for the second result

objSQL.results[1].accthand

Limitation

The above query is a non-paged query; in real terms this means that the maximum number of records that can be returned in a single run is 5000 – this seems to be a hard limit set by Netsuite

If more than 5000 records need to be returned in the dataset, the below query is better. It takes the same input, but will run it as a paged query (similar to the above search) and iterate through the results to build one big array.

Note: controversially, Netsuite still apparently limit this query method to 100,000 results if the SuiteAnalytics Connect module is not enabled. If it is enabled (and purchased!) then it will return unlimited results.

Paged Query


try{

    var arrResults = [];

    var strSQL = `select * from customfield`

    var objIterator = query.runSuiteQLPaged({
        query: strSQL,
        pageSize: 1000
    }).iterator()

    objIterator.each(function(page) {
        var objPage = page.value.data.iterator();

        objPage.each(function(row) {

            arrResults.push(row.value.asMap());
            return true;
        });

        return true;
    });
    
}catch(e){
	return e+e.stack;
}

Summary

Two similar, but very different ways to query/search in Netsuite. My personal preference is to exclusively use the N/query module in every script now due to its flexibility and speed

In the search, columns and filters need to be added using the module specific syntax, taking into account any joins/aggregation/functions you need to do on the data.

N/query requires none of that complexity, as the SQL query can be written to return this information in a easier to understand way.

My recommendation is to put the query block into a common script file which is included in every server script; that way you can easily access the query using a reusable function – providing the SQL statement when you call it.

Published by samlystor