NetSuite RESTlet Saved Search Return Limit

Question

I have the following JSON RESTlet script being utilized to export some data. Due to the limitations, it's capped at 1000 lines, which is well under the total that I need to export. I've come across a few different solutions but JSON/RESTlet are fairly new to me so I'm looking for a bit of feedback on how to adjust my code to loop through all the results.

function GetSearchResult(){
    //array container for search results
    var output = new Array();

    //get search results
    var results = nlapiSearchRecord('transaction','customsearchid',null,null);
    var columns = results[0].getAllColumns();

    //loop through the search results
    for(var i in results){
        //create placeholder object place holder
        var obj = new searchRow(
          //set the values of the object with the values of the appropriate columns
          results[i].getValue(columns[0]),
          results[i].getValue(columns[1]),
          results[i].getValue(columns[2]),
          results[i].getValue(columns[3]),
          results[i].getValue(columns[4]),
          results[i].getValue(columns[5]),
          results[i].getValue(columns[6]),
          results[i].getValue(columns[7]),
          results[i].getValue(columns[8]),
          results[i].getValue(columns[9]),
          results[i].getValue(columns[10]),
          results[i].getValue(columns[11]),
          results[i].getValue(columns[12])
          );

        //add the object to the array of results
        output.push(obj);
    }

    //return the array of search objects
    return output;
}

//Object to serve a place holder for each search row
function searchRow(internalid,lineid,subsidiaryid,locationid,departmentid,accountid,date,name,memo,amount,uniqueid,product,period){
    this.internalid = internalid;
    this.lineid = lineid;
    this.subsidiaryid = subsidiaryid;
    this.locationid = locationid;
    this.departmentid = departmentid;
    this.accountid = accountid;
    this.date = date;
    this.name = name;
    this.memo = memo;
    this.amount = amount;
    this.uniqueid = uniqueid;
    this.product = product;
    this.period = period;

}

Here's an example I was trying to follow along with to no avail:

var types = ["Estimate","Opprtnty","SalesOrd","PurchOrd","CustInvc","CashSale"];
var filters = new Array(); //define filters of the search
filters[0] = new nlobjSearchFilter('type',null,'anyof',types);
filters[1] = new nlobjSearchFilter('mainline',null,'is','T');
var columns = new Array();
columns[0] = new nlobjSearchColumn('internalid').setSort(); //include internal id in the returned columns and sort for reference
var results = nlapiSearchRecord('transaction',null,filters,columns); //perform search
var completeResultSet = results; //container of the complete result set
while(results.length == 1000){ //re-run the search if limit has been reached
     var lastId = results[999].getValue('internalid'); //note the last record retrieved
     filters[2] = new nlobjSearchFilter('internalidnumber',null,'greaterthan',lastId); //create new filter to restrict the next search based on the last record returned
     results = nlapiSearchRecord('transaction',null,filters,columns);
     completeResultSet = completeResultSet.concat(results); //add the result to the complete result set 
}

Thank you for the help!

score 5 · Answer 1 · edited Dec 17 '21 at 20:10

Here's a restlets 2.0 version of the same script. To add this to Netsuite:

Check scripting is enabled: Select Setup > Company > Setup Tasks > Enable Features. Click the SuiteCloud subtab. Ensure Client and Server SuiteScript are ticked.
Documents. Pick a location from the folders on the left (eg Suitescripts), and click "add file".
Edit and save the following as mySavedSearch.js, then upload from the site above.
Customization > Scripting > Scripts > New. select mySavedSearch.js. Fill in Name and ID fields, save.
Click "Deploy Script". Change status from "Testing" to "Released", and set permissions as appropriate. That will give you a URL and an external URL (something like: https://[your-ID].restlets.api.netsuite.com/app/site/hosting/restlet.nl?script=1234&deploy=1 ).

After you have set up the Netsuite postman collection: (howto here: Where is the Netsuite Webservices Postman Collection? ), it will be configured to send the correct auth headers. Perform a get request to the URL you got from the previous step and you'll get the JSON of your saved search.

 /**
  * @NApiVersion 2.x
  * @NScriptType Restlet
  * @NModuleScope SameAccount
  */
 define(['N/search','N/record'],

 function(search,record) {

     /**
      * Function called upon sending a GET request to the RESTlet.
      *
      * @param {Object} requestParams - Parameters from HTTP request URL; parameters will be passed into function as an Object (for all supported content types)
      * @returns {string | Object} HTTP response body; return string when request Content-Type is 'text/plain'; return Object when request Content-Type is 'application/json'
      * @since 2015.1
      */
     function doGet(requestParams) {

         var results = [];
         var slice = [];
         var i = 0;

         var mySearch = search.load({
             id: 'customsearch12345' // change to the ID of your saved search
         });

         var resultSet = mySearch.run();

         do {
             slice = resultSet.getRange({ start: i, end: i + 1000 });
             slice.forEach(function(row) {
                 var resultObj = {};
                 row.columns.forEach(function(column) {
                     resultObj[column.name] = row.getValue(column);
                     });
                 results.push(resultObj);
                 i++;
             });
         } while (slice.length >= 1000);

         return JSON.stringify(results);
     }

     return {
         'get': doGet,
     };

 });

score 3 · Answer 2 · answered Apr 21 '17 at 01:32

3

Here is a general purpose function that can load a Netsuite Saved Search, and retrieve the results as an array of standard Javascript objects, and will not be capped to the 1000 results limit.

function getSearchResults(id) {
  var search = nlapiLoadSearch(null, id);
  var columns = search.getColumns();
  var resultSet = search.runSearch();

  var results = [];
  var slice = [];
  var i = 0;

  do {
    slice = resultSet.getResults(i, i + 1000);
    slice.forEach(function(result) {
      var resultObj = {};
      columns.forEach(function(column) {
        resultObj[column.getName()] = result.getValue(column);   
      });
      results.push(resultObj);
      i++;
    });
  } while (slice.length >= 1000);

  return results;
}

answered Apr 21 '17 at 01:32

michoel

3,725
2
16
19

When attempting to use this code and substituting my report ID in under the nlapiLoadSearch, I'm getting a timeout error. What would be driving that? – Ryan Apr 21 '17 at 18:25
Typically there are 15-20K results – Ryan Apr 24 '17 at 15:58
Strange.. I've tested this code with 20k results and it worked fine. – michoel Apr 25 '17 at 23:15

NetSuite RESTlet Saved Search Return Limit

2 Answers2