SuiteScript Search Filter Expressions: AND, OR, NOT, Joins and Empty Values

Simple SuiteScript searches are easy to read:

const filters = [
  ['isinactive', search.Operator.IS, false],
];

The difficulty begins when a search needs grouped AND and OR conditions, joined fields, formulas or a test for an empty list field.

SuiteScript filter expressions can represent all of those cases. The important part is getting the array structure and operator appropriate for the field type.

Oracle describes Search.filterExpression as an array containing filter terms and the logical operators and, or and not. Nested arrays create grouped expressions.

The basic shape

A single filter term contains a field ID, an operator and a value:

['email', search.Operator.STARTSWITH, 'accounts@']

Multiple terms are joined by logical operators:

const filters = [
  ['isinactive', search.Operator.IS, false],
  'and',
  ['category', search.Operator.ANYOF, ['1', '2']],
];

Use the search.Operator enum where practical. It makes the intended operator clearer and avoids spelling mistakes.

Grouping OR conditions

Suppose a record should be returned when it is active and its review date is either empty or still in the future:

active AND (review date is empty OR review date is today or later)

The grouped branch needs its own nested array:

const filters = [
  ['isinactive', search.Operator.IS, false],
  'and',
  [
    ['custrecord_review_date', search.Operator.ISEMPTY, null],
    'or',
    ['custrecord_review_date', search.Operator.ONORAFTER, formattedToday],
  ],
];

Without that inner array, the expression becomes harder to reason about and may not reflect the grouping you intended.

Using NOT

Place not immediately before the condition it negates:

const filters = [
  ['mainline', search.Operator.IS, true],
  'and',
  'not',
  ['amount', search.Operator.EQUALTO, 0],
];

For a complex negative condition, group that condition in its own array.

Filtering a joined record

When using search.createFilter(), identify the field on the joined record with name and the relationship from the base record with join:

const customerIsCustomRole = search.createFilter({
  name: 'iscustom',
  join: 'role',
  operator: search.Operator.IS,
  values: true,
});

Oracle’s search.createFilter() documentation uses this same distinction: name belongs to the joined record, while join identifies the field on the current search record that provides the relationship.

In an array expression, joined custom-record fields can also appear in dotted form:

[
  'custrecord_enrolment_student.custrecord_student_customer',
  search.Operator.ANYOF,
  customerId,
]

The exact join ID depends on the search type, so confirm it in the Records Browser or a working saved search.

Empty fields are not all tested the same way

For ordinary text and date fields, ISEMPTY uses null as its third element:

['custrecord_review_date', search.Operator.ISEMPTY, null]

For empty list or record-reference fields, the special value @NONE@ is often used with ANYOF:

['custrecord_owner', search.Operator.ANYOF, '@NONE@']

If NetSuite reports WRONG_PARAMETER_TYPE, inspect both the operator and the third element. An operator valid for a text field may not be valid for a date or list field.

Date filters

Use date-specific operators such as ON, BEFORE, AFTER, WITHIN, ONORBEFORE and ONORAFTER.

When account formatting matters, format a JavaScript Date through N/format before inserting it into the expression:

const formattedToday = format.format({
  value: new Date(),
  type: format.Type.DATE,
});

const filters = [
  ['trandate', search.Operator.ONORAFTER, formattedToday],
];

Formula filters

Use a formula when the value needs to be transformed before comparison:

const compactReference = search.createFilter({
  name: 'formulatext',
  formula: "REPLACE({custrecord_reference}, ' ', '')",
  operator: search.Operator.IS,
  values: reference.replace(/\s/g, ''),
});

Oracle notes that list or record fields normally require internal IDs. If you must compare their displayed text, a formulatext filter is one supported option.

Filter-expression strings can also use a formula prefix:

const filters = [
  ['isinactive', search.Operator.IS, false],
  'and',
  [
    "formulanumeric: CASE WHEN {custrecord_status} = 'READY' THEN 1 ELSE 0 END",
    search.Operator.EQUALTO,
    1,
  ],
];

Keep values outside the formula whenever N/search provides an ordinary filter for the same condition. Interpolating user-controlled text into a formula introduces quoting problems and can change the expression’s meaning. If the comparison is fixed, keep it as a reviewed constant; if it is dynamic, prefer a native field filter and pass the value through the filter’s value element.

For the corresponding NetSuite UI syntax, see NetSuite Saved Search Formulas: Complete Guide and Examples and the focused CASE WHEN guide.

A debugging checklist

When a filter expression fails or returns the wrong records:

1. Write the intended logic in parentheses before translating it to arrays.
2. Give each logical group its own nested array.
3. Confirm the operator is valid for that field type.
4. Check whether the value should be null, @NONE@, an internal ID or a formatted date.
5. Confirm which record owns a joined field.
6. Replace pieces with simpler filters until the failing term is isolated.
7. Log the completed filter expression immediately before creating the search.

Most filter-expression bugs are not mysterious NetSuite behaviour. They are a mismatch between the intended Boolean expression and the array that was actually constructed.

If a valid-looking search is hidden behind a generic platform failure, work through An Unexpected SuiteScript Error Has Occurred: Diagnostic Guide.