Querying using Multiple Conditions with SuiteScript

Created: February 7, 2024

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

When building queries with SuiteScript, we will almost always need multiple Conditions. Given that the condition property of a Query only accepts a single Condition Object, we cannot pass an Array of Conditions like we do with our Query Columns.

Rather than building a list of multiple Conditions, the Query API provides methods for combining multiple Conditions into a single Condition instance using functions that represent the logical operators:

• AND
• OR
• NOT

To illustrate, we'll adjust our previous example to Query for Individual Customers with a specific Sales Rep.

We'll even go a step further and encapsulate the Query creation into a function that accepts an arbitrary Sales Rep ID.

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 .

/**
 * Query for Individual Customers with a specific Sales Rep
 *
 * Shows how to specify multiple Conditions using the logical operator AND.
 *
 * @author Eric T Grubaugh <eric@stoic.software>
 */
require(['N/query'], (q) => {
  const findCustomersByRep = (salesRepId) => {
    const customerQuery = q.create({
      type: q.Type.CUSTOMER,
      columns: [
        { fieldId: 'email' },
        { fieldId: 'entityid', alias: 'name' }
      ]
    })

    customerQuery.condition = customerQuery.and(
      customerQuery.createCondition({
        fieldId: 'isperson',
        operator: q.Operator.IS,
        values: true
      }),
      customerQuery.createCondition({
        fieldId: 'salesrep',
        operator: q.Operator.ANY_OF,
        values: salesRepId
      })
    )

    return customerQuery.run().asMappedResults()
  }

  const printCustomerNameAndEmail = (result) => {
    console.log(`${result.name} - ${result.email}`)
  }

  // Replace -5 with the internal ID of any Sales Rep ID in your account
  findCustomersByRep(-5).forEach(printCustomerNameAndEmail)
})

Functions for Readability

We have moved our Query creation inside a function that accepts a parameter for the Sales Rep:

const findCustomersByRep = (salesRepId) => {
  const customerQuery = q.create({
    type: q.Type.CUSTOMER,
    columns: [
      { fieldId: 'email' },
      { fieldId: 'entityid', alias: 'name' }
    ]
  })

  customerQuery.condition = customerQuery.and(
    customerQuery.createCondition({
      fieldId: 'isperson',
      operator: q.Operator.IS,
      values: true
    }),
    customerQuery.createCondition({
      fieldId: 'salesrep',
      operator: q.Operator.ANY_OF,
      values: salesRepId
    })
  )

  return customerQuery.run().asMappedResults()
}

The function creates and immediately executes the Query, returning the mapped ResultSet.

This pattern of creating the Query instance and returning it from a function named find* is a common design pattern I use to isolate my Query logic. I find that this makes it faster to comprehend, modify, and reuse my Queries.

This makes the execution and processing of our Query condense down to:

findCustomersByRep(-5).forEach(printCustomerNameAndEmail);

which I find to be clear and concise.

Combine Conditions using Logical Operators

Focusing in on our Condition, you can see that we've added a second Condition and passed both of them to the and method of our Query instance:

customerQuery.condition = customerQuery.and(
  customerQuery.createCondition({
    fieldId: 'isperson',
    operator: q.Operator.IS,
    values: true
  }),
  customerQuery.createCondition({
    fieldId: 'salesrep',
    operator: q.Operator.ANY_OF,
    values: salesRepId
  })
)

The and method provides logical conjunction functionality for Queries, similar to JavaScript's logical AND (&&) operator . There is a corresponding or method that provides logical disjunction functionality, similar to JavaScript's logical OR (||) operator.

Both methods accept any number of Condition parameters and also return a Condition instance. In this way, multiple Conditions can be combined quickly using the logical operators.

There is also a not method which provides logical negation of a single Condition, similar to JavaScript's logical NOT (!) operator.