Logical Operators and Grouping Criteria in SuiteScript 2.1 Searches

Created: January 30, 2024

This is a sample chapter adapted from the "Basic Searching with SuiteScript 2.1" Cookbook, part of the SuiteScript by Example series.

This code example uses the require function for importing modules. This allows you to copy and paste the snippets directly into the debugger or your browser's developer console and run them. For more on how to test SuiteScript in your browser's console, watch my tutorial video .

As SuiteScript developers, we often need to create complex combinations of filters in our NetSuite searches in order to generate the appropriate report or make accurate calculations. The N/search module provides us with Filter Expressions to accomplish this.

Using Filter Expressions, we can use logical grouping and operators like AND and OR in order to produce the desired conditions. Filter Expressions are the foundation we build our complex criteria on.

Let's create a Search with a filter structure of A AND (B OR C):

/**
 * Creates and executes a Customer search that finds all Customers for a specific
 * Sales Rep that are either on Credit Hold or have an Overdue Balance.
 *
 * Shows how to specify OR relationship between multiple criteria in Filter Expressions
 *
 * Shows how to logically group criteria, like using "Parens" in the UI
 *
 * @author Eric T Grubaugh <eric@stoic.software>
 */
require(['N/search'], (s) => {
  const findProblemCustomersByRep = (salesRepId) =>
    s.create({
      type: s.Type.CUSTOMER,
      filters: [
        ['salesrep', s.Operator.ANYOF, salesRepId], 'and',
        [
          ['overduebalance', s.Operator.GREATERTHAN, 0], 'or',
          ['credithold', s.Operator.ANYOF, 'ON']
        ]
      ],
      columns: ['entityid', 'email']
    }).run()

  const printCustomerName = (result) => {
    console.log(result.getValue({ name: 'entityid' }))
    return true
  }

  // Replace "17" with the internal ID of any Sales Rep ID in your account
  findProblemCustomersByRep('17').each(printCustomerName)
})

You can copy and paste this code into your browser console on any NetSuite page that supports SuiteScript. For more on testing SuiteScript in the browser console, see my other articles:

SuiteScript Filter Expressions

Filter Expressions are an Array of Arrays. Each inner Array represents one Filter. An individual Filter takes the form:

[fieldNameA:string, operatorA:string, valueA1:any, valueA2:any]

The operator value will typically come from the search.Operator enumeration.

Multiple filters are combined by inserting a logical operator as a string in between them, like so:

filters: [
  filterA, logicalOperator1, filterB, logicalOperator2, filterC
]

The logicalOperator value can be one of 'AND' or 'OR', depending on the logical operator you want to use to combine those filters.

`OR` Relationships

Focusing in on the innermost filters, we want to construct the B OR C portion of our filter criteria.

['overduebalance', s.Operator.GREATERTHAN, 0], 'or',
['credithold', s.Operator.ANYOF, 'ON']

With these filters, our Search will find Customers that meet either (or both) of these criteria.

The 'or' operator behaves like JavaScript's logical OR (||) operator.

Logically Grouping Criteria

We have the (B OR C) portion of our Filters, but we also want to filter our results down by a specific Sales Rep to get the A AND portion.

We do that by first nesting our overduebalance and credithold filters within their own Array, thus logically grouping them together:

[
  ['overduebalance', s.Operator.GREATERTHAN, 0], 'or',
  ['credithold', s.Operator.ANYOF, 'ON']
]

Then we add our Sales Rep filter and combine it to the filter gropu with an AND operation:

['salesrep', s.Operator.ANYOF, salesRepId], 'and',
[
  ['overduebalance', s.Operator.GREATERTHAN, 0], 'or',
  ['credithold', s.Operator.ANYOF, 'ON']
]

Although it's not necessary to write it out so verbosely, it might help to walk through it this way:

const salesRepFilter = ['salesrep', s.Operator.ANYOF, salesRepId] // => A
const overdueFilter = ['overduebalance', s.Operator.GREATERTHAN, 0] // => B
const creditFilter = ['credithold', s.Operator.ANYOF, 'ON'] // => C

const problemCustomerFilter = [overdueFilter, 'or', creditFilter] // => B OR C

customerSearch.filters = [salesRepFilter, 'and', problemCustomerFilter]
// => A AND (B OR C)

By using variables to name each filter separately, we potentially improve the readability of our code by documenting the purpose of each individual filter.

Favor Filter Expressions

There is no way to create an OR relationship using the Object syntax for Filters; there is also no way to logically group Filters using the Object syntax. These can only be accomplished with Filter Expressions.

Because of this power and flexibility that Filter Expressions have over the Object syntax, and the more concise nature of Filter Expressions, I only use the Object syntax when absolutely necessary.

In all other cases, I use Filter Expressions to define my Search Filters.