Sunday, March 3, 2019

Defining search filter expressions with AND/OR/() operators

When using the SuiteScript nlapiSearchRecord(type, id, filters, columns) function, there are three ways how search filters can be specified:

  • nlobjSearchFilter object
  • array of nlobjSearchFilter objects
  • search filter expressions

This article shows how to define search expressions where AND/OR operators are used. Parentheses are not used, because operator precedence is defined by array structure.

Example1 (simple expressions, expression with joined record) :

// (A equal to 1)var filterExpr1  = ['A', 'equalto', 1];  // (B between 1 and 10) while B is from a joined record "JoinedRec" with id "joinedrec"var filterExpr1  = ['joinedrec.B', 'between', 1, 10];var arrCols = [ new nlobjSearchColumn('id') ];var arrSearch = nlapiSearchRecord('customrecord137', null, filterExpr1 , arrCols);

Example2 (expressions with AND/OR) :

// ((A equal to 1) OR (B between 1 and 10))var filterExpr2  = [                               ['A', 'equalto', 1],                              'OR' ,                              ['B', 'between', 1, 10]];

Example3 (nested expressions with AND/OR) :

// (((A equal to 1) OR (B between 1 and 10)) AND (C equal to 1))var filterExpr3  = [ 		[			['A', 'equalto', 1],			'OR' ,			['B', 'between', 1, 10]		],		AND,		['C', 'equalto', 1]];

There is also the possibility to use UI to create a saved search including expressions with AND/OR/(),  load the saved search using SuiteScript Search API functions, and use the getFilterExpression() method of nlobjSearch object to retrieve search filters in a form of a filter expression:

var mySearch = nlapiLoadSearch('customrecord13',178);var filterExpr = mySearch.getFilterExpression();

No comments:

Post a Comment