netsuite Scripting searches with Filter Expressions Filter term
Example
To understand the filter expressions, we should start with Filter Term. This is a simple array of strings, containing at least 3 elements:
- Filter (Field/Join field/Formula/Summary)
- Operator (search.Operator)
- Values (string value(or array of string values), to be used as filter parameter)
// Simple example:
['amount', 'equalto', '0.00']
// When the field is checkbox, use 'T' or 'F'
['mainline', 'is', 'T']
// You can use join fields
['customer.companyname', 'contains', 'ltd']
// summary filter term
['sum(amount)', 'notlessthan', '170.50']
// summary of joined fields
['sum(transaction.amount)', 'greatherthan', '1000.00']
// formula:
["formulatext: NVL({fullname},'John')", "contains", "ohn"]
// and even summary formula refering joined fields:
['sum(formulanumeric: {transaction.netamount} + {transaction.taxtotal})', 'greaterthanorequalto','100.00']
// for selection fields, you may use 'anyof'
// and put values in array
['type','anyof',['CustInvc','VendBill','VendCred']]
// when using unary operator, like isempty/isnotempty
// don't forget that the filter term array contains at least 3 elements
// and put an empty string as third:
['email', 'isnotempty', '']
// you may have more than 3 elements in Filter Term array,
// when the operator requires more than one value:
['grossamount','between','100.00','200.00']
In some selector fields, you can use special values.
// When filtering the user related fields, you can use:
// Me (Current user): @CURRENT@
// My Team (somebody from the team I am leading): @HIERARCHY@
['nextapprover','is','@CURRENT@']
// The same is valid for Subsidiary, Department, Location, etc.
// @CURRENT@ means MINE (Subsidiary, Department...)
// @HIERARCHY@ means MINE or DESCENDANTS
["subsidiary","is","@HIERARCHY@"]
// on selection fields you may use @ANY@ and @NONE@
['nextapprover','is','@NONE@']
