Suitescript 2.0 ResultSet.Each Callback Function Exceeded 4000

3
votes

I wrote a script (below) a couple years ago (haven't been coding since - so there is a fair amount or rust ;D) where the ResultSet now exceeds 4000 records which was not anticipated when the script was written. The error is below:

{"type":"error.SuiteScriptError","name":"SSS_SEARCH_FOR_EACH_LIMIT_EXCEEDED","message":"No more than 4000 search results may be returned at one time from nlobjSearchResultSet.forEachResult(callback). Please revise your search criteria or modify the callback logic so that no more than 4000 results are returned."...

Is the best way to fix this to use a different technique (like Map/Reduce - which I'd have to learn now), or is there a way to filter the search so only a certain number of results are returned from the search and the rest of the records get returned/processed in a subsequent execution?

Thanks

//...
invoiceSearch.run().each(function(result) {
    // ensure script usage okay
    if (usageOkay()) {
        entityID = result.getValue({
            'name': 'internalid',
            'join': 'customer',
            'summary': search.Summary.GROUP
        });

        var maxAmountCustomerRecord = result.getValue({
            'name': 'custentity_amount_maxorder_2years',
            'join': 'customer',
            'summary': search.Summary.GROUP
        });

        var maxAmountCalculated = result.getValue({
            'name': 'formulacurrency',
            'formula': 'CASE WHEN {closedate} >= ADD_MONTHS(SYSDATE, -(12 * 2)) THEN {amount} ELSE NULL END',
            'summary': search.Summary.MAX
        });
        // in case the calculated amount is null then make it 0
        maxAmountCalculated = maxAmountCalculated || 0.0;
        // Only write to the customer record when a change is required
        if (maxAmountCustomerRecord != maxAmountCalculated) {
            updateRecord(entityID, maxAmountCalculated);
            log.debug('Updating customer with entityID: ' + entityID + ', to maxAmount: ' +
                maxAmountCalculated + ', from previous value of ' + maxAmountCustomerRecord);
        }
        return true;
    }
    else {
        // If remaining script usage low, reschedule script
        rescheduleScript(entityID);
    }
});
//....
3
javascriptcallbacknetsuitesuitescriptsuitescript2.0

3 Answers

5
votes

I personally like to generate the full search, then parse it from there:

I use this function with any search object to compile the results in blocks of 1000:

function getAllResults(s) {
    var results = s.run();
    var searchResults = [];
    var searchid = 0;
    do {
        var resultslice = results.getRange({start:searchid,end:searchid+1000});
        resultslice.forEach(function(slice) {
            searchResults.push(slice);
            searchid++;
            }
        );
    } while (resultslice.length >=1000);
    return searchResults;
}

Then when I want to process any search, for example:

var mySearch = search.create({
                type:'invoice',
                columns: [
                    {name: 'tranid'},
                    {name: 'trandate'},
                    {name: 'entity', sort: (params.consolidated)?search.Sort.ASC:undefined },
                    {name: 'parent',join:'customer', sort: (!params.consolidated)?search.Sort.ASC:undefined},
                    {name: 'terms'},
                    {name: 'currency'},
                    {name: 'amount'},
                    {name: 'amountremaining'},
                    {name: 'fxamount'},
                    {name: 'fxamountremaining'},
                ],
                filters: [
                    {name: 'mainline', operator:'is',values:['T']},
                    {name: 'trandate', operator:'onorbefore', values: [params.startDate] }
                ]
            });

var myResults = getAllResults(mySearch );

myResults.forEach(function(result) {

//... do stuff with each result

});

I've used this with good results on data sets in excess of 20,000 records.

4
votes

While Eric's answer is the way I'd probably go sometimes a scheduled script is the thing to do. And certainly in this case if you think you'd work through all the items in a day's worth of scheduled runs then a simple fix is:

var count = 0;
invoiceSearch.run().each(function(result) {
   count++;
   if(count == 4000) return false;
   if(usageOk(){
     ...
     return true;
   }else{
     rescheduleScript(entityID);
      return false; // end the each and you may never hit 4k anyway
   }
});
2
votes

My recommendation and maybe the best practice for bulk processing in SuiteScript 2.0 is to use a Map/Reduce rather than a Scheduled Script. There is certainly a bit of a learning curve with them, but they're extremely powerful.

Quick tips will be:

  1. Your getInputData entry point can just create/load whatever invoiceSearch is in the existing script and return it
  2. You'll be able to get rid of all the usage monitoring as you don't need to do this yourself with a M/R
  3. Your reduce entry point will effectively be the guts of the if statement you have there.

It should hopefully be a fairly straightforward conversion.