5

I am trying to create a report in NetSuite. I want to filter out certain data in regards to one parameter, then I want to filter in data by using another parameter. Is there an AND/OR filter in NetSuite?

user3286031
  • 51
  • 1
  • 2

4 Answers4

7

If you are doing it using Netsuite Report/search interface, click on Use Advanced Search and then check Use Expressions. You would see the And/Or column in the Criteria Tab.

If you are doing it using Suit Script use Search Filter Expressions

//Define search filter expression
var filterExpression = [ 
   [ 'trandate', 'onOrAfter', 'daysAgo90' ],
   'or',
   [ 'projectedamount', 'between', 1000, 100000 ],
   'or',
   'not', [ 'customer.salesrep', 'anyOf ', -5] 
];
Saqib
  • 2,470
  • 3
  • 19
  • 32
  • This works. Quote from the API: Note that the filters argument in nlapiSearchRecord(type, id, filters, columns) refers to either a search filter list (nlobjSearchFilter[]) or to a search filter expression (Object[]). With the object reference returned, you can then use any of the following nlobjSearchFilter methods to filter your results. – Phil Jan 22 '15 at 19:07
  • 1
    Is there a way to use FilterExpressions using SuiteTalk .NET client? @Phil_1984_ – wOvalle Feb 09 '16 at 12:13
  • Please could you look at my question, which is related: http://stackoverflow.com/questions/41494717/sss-invalid-srch-filter-join-when-using-filter-expression-on-joined-custom-field – cja Jan 05 '17 at 21:16
3

I encountered the situation in which i need to use AND/OR filter in Netsuite. I achieved this by :

You can directly give static filter value by 

filters: [
    ['internalid', 'anyof', parseInt(customer_internal_id)],
    'and',
    ['transaction.internalidnumber', 'equalto', parseInt(salesorder_internal_id)],
    'and',
    ['transaction.mainline', 'is', 'true']
]

If you want to dynamically add filter , you store/push your filter to a array and set this array as filter. You can achieve this by

var individualFilter = [], combineIndividualFilter = [], defaultFilters = [];
for (var i = 0; i < numLines; i++) {
    individualFilter[0] = ['formulatext: {category}','startswith',"Related Items for "+ itemName[i].split(" :")[0]];
    combineIndividualFilter.push(individualFilter[0]);
    if ((i + 1) != numLines) {
        combineIndividualFilter.push('OR');
    }
}
defaultFilters.push(combineIndividualFilter);
defaultFilters.push('AND');
defaultFilters.push(['website', 'anyof', 3 ]);
defaultFilters.push('AND');
defaultFilters.push(['formulatext: {storedisplayimage}','isnotempty', null ]);

and finally by setting 

filters : defaultFilters
Manu AVS
  • 557
  • 3
  • 7
2

I think you can achieve the same with the saved search option also.
Go to : Lists --> Search --> saved search --> new
create a new saved search and on the criteria sub-tab mark on Use Expressions box. Place your filter conditions with 'And/Or' conjunction.

Otherwise use the followings:
var filters = new Array(); filters[0] = new nlobjSearchFilter('custrecord01',null,'contains','alpha').setOr(true);
filters[1]= new nlobjSearchFilter( 'custrecord02', null, 'contains', 'x').setOr(true);

Rockstar
  • 2,228
  • 3
  • 20
  • 39
  • Just tried this and it works. You would have thought that you could just use a string array in the 4th argument, but you cant. – Phil Jan 22 '15 at 19:09
2

We also can use sql OR in formula search like shown below 

var flt = new Array();
var strFormula = "case when {internalid}=123 OR {internalid}=456 then 'yes' else 'no' end";
flt.push(new nlobjSearchFilter('formulatext',null,'is','yes').setFormula(strFormula));

var col = new Array();
col.push(new nlobjSearchColumn("entityid"));

var rslt = nlapiSearchRecord('customer',null,flt,col);