To understand the filter expressions, we should start with Filter Term. This is a simple array of strings, containing at least 3 elements:
// 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@']
Simple filter expression is also an array. It contains one or more filter terms, combined with operators - 'AND', 'OR', 'NOT'. (Operators are case insensitive):
[
['mainline', 'is', 'T'],
'and', ['type','anyof',['CustInvc','CustCred']],
'and', 'not', ['amount', 'equalto', '0.00'],
'or', ['customer.companyname', 'contains', 'ltd']
]
More complex filter expressions, could contain filter terms AND nested filter expressions, combined with operators. No more than 3 levels of nested expressions are allowed:
[
['mainline', 'is', 'T'],
'and', ['type','anyof',['CustInvc','CustCred']],
'and', [ ['customer.companyname', 'contains', 'ltd'],
'or', ['customer.companyname', 'contains', 'inc']
],
'and', [ ['subsidiary', 'is', 'HQ'],
'or', ['subsidiary', 'anyof', '@HIERARCHY@']
],
'and', ['trandate', 'notbefore', 'yesterday']
]
And finally, let's put all this altogether in a SS2.0 sample:
var s = search.create({
type : 'transaction',
columns : [
'trandate',
'tranid',
'currency',
'customer.companyname',
'customer.country',
'amount'
],
filters : [
['mainline', 'is', 'T'],
'and', ['type','anyof',['VendBill','VendCred']],
'and', [ ['customer.companyname', 'contains', 'ltd'],
'or', ['customer.companyname', 'contains', 'inc']
],
'and', [ ['subsidiary', 'is', 'HQ'],
'or', ['subsidiary', 'anyof', '@HIERARCHY@']
],
'and', ['trandate', 'notbefore', 'yesterday']
]
});
Filter expressions cannot include Filter Objects. This is very important. If you decide to form your filters with Filter Expression, you use array of string arrays. The following syntax is wrong:
// WRONG!!!
var f1 = search.createFilter({
name: 'mainline',
operator: search.Operator.IS,
values: 'T'
});
var f2 = search.createFilter({
name: 'type',
operator: search.Operator.ANYOF,
values: ['VendBill','VendCred']
});
// here you will receive an error message
var s = search.create({
type : 'transaction',
filters : [ f1, 'and', f2 ] // f1,f2 are Filter Objects, instead of string arrays
});
Instead, use the correct:
// CORRECT!!!
var f1 = ['mainline', search.Operator.IS, 'T'];
var f2 = ['type', search.Operator.ANYOF, ['VendBill','VendCred'] ];
var s = search.create({
type : 'transaction',
filters : [ f1, 'and', f2 ]
});
or if you want to keep with Filter Objects approach, pass an array of filter objects, and forget about operators 'AND', 'OR', 'NOT'. It will be always AND
// correct, but not useful
var f1 = search.createFilter({
name: 'mainline',
operator: search.Operator.IS,
values: 'T'
});
var f2 = search.createFilter({
name: 'type',
operator: search.Operator.ANYOF,
values: ['VendBill','VendCred']
});
var s = search.create({
type : 'transaction',
filters : [ f1, f2 ] // here you have array of Filter Objects,
// filtering only when all of them are TRUE
});
['trandate', 'notbefore', 'daysAgo17']
Here are the search operators:
https://system.netsuite.com/app/help/helpcenter.nl?fid=section_N3005172.html
Of course you can use serach.Operator enum:
https://system.netsuite.com/app/help/helpcenter.nl?fid=section_4345782273.html
Here are the search summary types:
https://system.netsuite.com/app/help/helpcenter.nl?fid=section_N3010474.html
You can use ANYOF operator only on select type fields (List/Record). If you want to use it against free-text fields (like names, emails etc.), the only way is to create a nested Filter Expression with 'OR' operators:
[ ['email', 'startswith', '[email protected]'],
'or', ['email', 'startswith', '[email protected]'],
'or', ['email', 'startswith', '[email protected]'],
'or', ['email', 'startswith', '[email protected]']
]
or you can write small script, doing this instead of you:
function stringFieldAnyOf(fieldId, listOfValues) {
var result = [];
if (listOfValues.length > 0) {
for (var i = 0; i < listOfValues.length; i++) {
result.push([fieldId, 'startswith', listOfValues[i]]);
result.push('or');
}
result.pop(); // remove the last 'or'
}
return result;
}
// usage: (two more filters added just to illustrate how to combine with other filters)
var custSearch = search.create({
type: record.Type.CUSTOMER,
columns: searchColumn,
filters: [
['companyname', 'startswith', 'A'],
'and', stringFieldAnyOf('email', ['[email protected]', '[email protected]']),
'and', ['companyname', 'contains', 'b']
]
});
var s = search.load('customsearch1234');
log.debug('filterExpression', JSON.stringify(s.filterExpression));