CODEWITHSUNDEEP
netsuite
user3286031
user3286031

Reputation: 61

NetSuite And/Or Filter

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?

Upvotes: 6

Views: 7973

Answers (4)

Manu AVS
Manu AVS

Reputation: 575

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

Upvotes: 2

user5227543
user5227543

Reputation:

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);

Upvotes: 2

Rockstar
Rockstar

Reputation: 2288

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);

Upvotes: 3

Saqib
Saqib

Reputation: 2480

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] 
];

Upvotes: 7

Related Questions