Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get over 1000 records from a SuiteScript Saved Search?

Tags:

netsuite

Below is code I came up with to run a Saved Search in NetSuite using SuiteScript, create a CSV with the Saved Search results and then email the CSV. The trouble is, the results are limited to 1000 records. I've researched this issue and it appears the solution is to run a loop that slices in increments of 1000. A sample of what I believe is used to slice searches is also below.

However, I cannot seem to be able to incorporate the slicing into my code. Can anyone help me combine the slicing code with my original search code?

var search = nlapiSearchRecord('item', 'customsearch219729'); 

// Creating some array's that will be populated from the saved search results 
var content = new Array(); 
var cells = new Array(); 
var temp = new Array(); 
var x = 0; 

// Looping through the search Results 
for (var i = 0; i < search.length; i++) { 
var resultSet = search[i]; 
// Returns an array of column internal Ids
var columns = resultSet.getAllColumns(); 

// Looping through each column and assign it to the temp array 
for (var y = 0; y <= columns.length; y++) { 
temp[y] = resultSet.getValue(columns[y]); 
} 
// Taking the content of the temp array and assigning it to the Content Array. 
content[x] += temp; 
// Incrementing the index of the content array 
x++; 
} 

//Inserting headers
content.splice(0, 0, "sku,qty,");

// Creating a string variable that will be used as the CSV Content 
var contents; 

// Looping through the content array and assigning it to the contents string variable. 
for (var z = 0; z < content.length; z++) { 
contents += content[z].replace('undefined', '') + '\n';
}
// Creating a csv file and passing the contents string variable. 
var file = nlapiCreateFile('InventoryUpdate.csv', 'CSV', contents.replace('undefined', ''));

// Emailing the script.
function SendSSEmail()
{
   nlapiSendEmail(768, 5, 'Inventory Update', 'Sending saved search via scheduled script', '[email protected]', null, null, file, true, null, '[email protected]');
}

The following code is an example of what I found that is used to return more than a 1000 records. Again, as a novice, I can't seem to incorporate the slicing into my original, functioning SuiteScript. Any help is of course greatly appreciated.

var filters = [...];
var columns = [...];
var results = [];
var savedsearch = nlapiCreateSearch( 'customrecord_mybigfatlist', filters, columns );
var resultset = savedsearch.runSearch();
var searchid = 0;
do {
    var resultslice = resultset.getResults( searchid, searchid+1000 );
    for (var rs in resultslice) {
        results.push( resultslice[rs] );
        searchid++;
    }
} while (resultslice.length >= 1000);
return results;
like image 796
Accounting Avatar asked Jun 26 '15 11:06

Accounting


1 Answers

Try out this one :

function returnCSVFile(){

    function escapeCSV(val){
        if(!val) return '';
        if(!(/[",\s]/).test(val)) return val;
        val = val.replace(/"/g, '""');
        return '"'+ val + '"';
    }


    function makeHeader(firstLine){
        var cols = firstLine.getAllColumns();
        var hdr = [];
        cols.forEach(function(c){
            var lbl = c.getLabel(); // column must have a custom label to be included.
            if(lbl){
                hdr.push(escapeCSV(lbl));
            }
        });
        return hdr.join(",");
    }

    function makeLine(srchRow){
        var cols = srchRow.getAllColumns();
        var line = [];
        cols.forEach(function(c){
            if(c.getLabel()){
                line.push(escapeCSV(srchRow.getText(c) || srchRow.getValue(c)));
            }
        });
        return line.join(",");
    }

    function getDLFileName(prefix){
        function pad(v){ if(v >= 10) return v; return "0"+v;}
        var now = new Date();
        return prefix + '-'+    now.getFullYear() + pad(now.getMonth()+1)+ pad(now.getDate()) + pad( now.getHours())    +pad(now.getMinutes()) + ".csv";
    }


    var srchRows = getItems('item', 'customsearch219729'); //function that returns your saved search results

    if(!srchRows)   throw nlapiCreateError("SRCH_RESULT", "No results from search");


    var fileLines = [makeHeader(srchRows[0])];

    srchRows.forEach(function(soLine){
        fileLines.push(makeLine(soLine));
    });



var file = nlapiCreateFile('InventoryUpdate.csv', 'CSV', fileLines.join('\r\n'));
nlapiSendEmail(768, 5, 'Test csv Mail','csv', null, null, null, file);
}

function getItems(recordType, searchId) {
    var savedSearch = nlapiLoadSearch(recordType, searchId);
    var resultset = savedSearch.runSearch();
    var returnSearchResults = [];
    var searchid = 0;
    do {
        var resultslice = resultset.getResults(searchid, searchid + 1000);
        for ( var rs in resultslice) {
            returnSearchResults.push(resultslice[rs]);
            searchid++;
        }
    } while (resultslice.length >= 1000);

    return returnSearchResults;
}

I looked into your code but it seems you're missing the label headers in the generated CSV file. If you are bound to use your existing code then just replace

var search = nlapiSearchRecord('item', 'customsearch219729'); 

with

var search = getItems('item', 'customsearch219729');

and just use the mentioned helper function to get rid off the 1000 result limit.

Cheers!

like image 70
Rockstar Avatar answered Sep 21 '22 16:09

Rockstar