Scripting search in NetSuite not working as expected

Question

I tried a search script in NetSuite and the problem I'm having is that the search is getting several results despite I put the order number in the transaction table search. If I do the same search by the IU I just get 1 result which is the correct one.

The script is

var filters = new Array();

filters[0] = new nlobjSearchFilter('item', null, 'is', 'ITEM123');
filters[1] = new nlobjSearchFilter('type', null, 'is', 'SalesOrd');
filters[2] = new nlobjSearchFilter('companyname', 'customer', 'contains', 'CustomerName');
filters[3] = new nlobjSearchFilter('number', null, 'is', 'ORDER9887');


var columns = new Array();
columns[0] = new nlobjSearchColumn('item');
columns[1] = new nlobjSearchColumn('type');
columns[2] = new nlobjSearchColumn('name','item');
columns[3] = new nlobjSearchColumn('companyname','customer');
columns[4] = new nlobjSearchColumn('number');


var searchResults = nlapiSearchRecord('transaction', null, filters, columns);
var values = 'TOTAL RESULTS: ' + searchResults.length;
if(searchResults != null)
{
  for( i = 0 ; i<  searchResults.length ; i++)
  {
   values = values + '\r\nITEM ' +  searchResults[i].getValue(columns[0]) + 
            '\r\nTYPE ' +  searchResults[i].getValue(columns[1]) +
            '\r\nITEM NAME ' + searchResults[i].getValue(columns[2]) +
            '\r\nCOMPANY NAME ' + searchResults[i].getValue(columns[3]) +
            '\r\nTRANSACTION NUMBER ' + searchResults[i].getValue(columns[4]); 
  }
  alert(values);
}

So it doesn't make much sense to me, it's suppose that filters are implicitly with an AND operator.

Is there any clue which I'm doing wrong?

Thanks in advance.

Pablo.

Answer 1

When searching transactions, you need to understand and use the mainline filter to adjust the results you want. See this answer for a more detailed explanation.

Unfortunately, I cannot explain why you are getting different results for the exact same criteria between the UI and the script. I would need to see the UI search as well to troubleshoot that.

You are correct that search filters provided this way are always with an AND operator. You can instead use filter expressions to explicitly state the logical operator. See the NetSuite Help document titled Filtering a Search for examples of filter expressions.

Using filter expressions and the mainline filter, I might write your search something like:

// Filter expression syntax
var filters = [
    ['item', 'is', 'ITEM123'], 'and',
    ['type', 'is', 'SalesOrd'], 'and',
    ['mainline', 'is', 'F'], 'and', // mainline=F gives me only line item results
    ['customer.companyname', 'contains', 'CustomerName'], 'and',
    ['number', 'is', 'ORDER9887']
];

var columns = [
    new nlobjSearchColumn('item'),
    new nlobjSearchColumn('type'),
    new nlobjSearchColumn('name','item'),
    new nlobjSearchColumn('companyname','customer'),
    new nlobjSearchColumn('number')
];

// I always default my search results to [] to avoid the null check later
var searchResults = (nlapiSearchRecord('transaction', null, filters, columns) || []);

// Logging to console assuming client script, otherwise use nlapiLogExecution
console.log('TOTAL RESULTS: ' + searchResults.length);

// I prefer using Array.map or Array.forEach when iterating over arrays in SuiteScript
var resultString = searchResults.map(
    function (result) { // Change each result to its String representation
        return 'ITEM ' + result.getValue('item') + 
            '\r\nTYPE ' +  result.getValue('type') +
            '\r\nITEM NAME ' + result.getValue('name', 'item') +
            '\r\nCOMPANY NAME ' + result.getValue('companyname','customer') +
            '\r\nTRANSACTION NUMBER ' + result.getValue('number'); 
    }
).join("\r\n"); // Join all of the result strings with a newline

console.log(resultString);