Advanced Techniques in N/SEARCH: Working with Filters, Columns, and Large Result Sets

The N/SEARCH module is designed to allow SuiteScript developers to search for records in NetSuite, filter them based on specific criteria, and retrieve information in a structured way. It uses the concept of Saved Searches, which can be saved and reused, but also allows for dynamic searches that don’t require saving them to the system.

You can use this module to query:

• Standard NetSuite records (e.g., Customer, Sales Order, Invoice, etc.).
• Custom records (custom record types you’ve created).
• Custom fields and formulas.

Key Concepts in N/SEARCH

• Search Types: A search type is the type of record you’re querying, such as search.Type.CUSTOMER, search.Type.SALES_ORDER, etc.
• Filters: Filters are criteria that narrow down the search results based on field values (e.g., customers who have a specific status or sales orders over a certain amount).
• Columns: Columns define the data you want to retrieve from the records. These are similar to the fields in the record you are querying.
• Saved Searches: You can create a saved search programmatically, save it to the system, and then retrieve it for future use.

Common Methods in N/SEARCH

• create(options):
• Creates a new search. You can specify the search type (e.g., search.Type.CUSTOMER), filters, and columns.

var search = require('N/search');


var customerSearch = search.create({
  type: search.Type.CUSTOMER,
  filters: [
    ['entitystatus', 'anyof', [1, 13]] // e.g., Active and Pending customers
  ],
  columns: [
    'entityid', // Customer name
    'email' // Customer email
  ]
});

load(options):

• Loads an existing saved search by its ID.

var savedSearch = search.load({
  id: 'customsearch_my_saved_search'
});

run():

• Executes the search and returns an object that allows you to iterate over the results.

var resultSet = customerSearch.run(); resultSet.each(function(result) { log.debug(result.getValue('entityid')); // Access individual column values return true; // continue iterating });

runPaged():

• Executes the search in pages, which is useful for handling large result sets efficiently.
• runPaged() returns a paged result set, allowing you to work with smaller chunks of data (useful when you’re querying a large number of records).

var pagedResultSet = customerSearch.runPaged({
  pageSize: 1000
});


pagedResultSet.pageRanges.forEach(function(pageRange) {
  var page = pagedResultSet.fetch({ index: pageRange.index });
  page.data.forEach(function(result) {
    log.debug(result.getValue('entityid'));
  });
});

each():

• Iterates over search results one by one.
• Returns true to continue iterating through the results. If false is returned, it stops iterating.

var resultSet = customerSearch.run();


resultSet.each(function(result) {
  log.debug('Customer Name', result.getValue('entityid'));
  return true; // Continue iterating
});

Filters and Operators

Filters in N/SEARCH allow you to specify criteria for which records are included in the search results. You can use operators to define conditions.

• Basic Operators:
• equals: For exact matches (e.g., entitystatus = '13').
• contains: For partial matches (e.g., searching for a name that contains “John”).
• greaterthan: To find values greater than a certain amount.
• lessthan: To find values less than a certain amount.
• anyof: Matches any of the provided values (e.g., a list of statuses).
• noneof: Excludes values in the provided list.

var customerSearch = search.create({
  type: search.Type.CUSTOMER,
  filters: [
    ['entitystatus', 'noneof', [13, 1]], // Exclude customers with statuses 13 or 1
    'AND',
    ['email', 'contains', '@example.com'] // Filter customers whose email contains '@example.com'
  ],
  columns: [
    'entityid', // Customer name
    'email'      // Customer email
  ]
});

Columns and Results

Columns define what data you want to retrieve from each record in the search. You can include standard fields or custom fields. You can also apply formulas to calculate values dynamically.

• Standard Columns: These are standard fields such as entityid, email, etc.
• Formula Columns: You can apply formulas like CASE, IF, COUNT, and other SQL-like functions to your search.

Example of formula column:

var customerSearch = search.create({
  type: search.Type.CUSTOMER,
  filters: [
    ['entitystatus', 'anyof', [1, 13]] // Active or Pending customers
  ],
  columns: [
    'entityid',
    search.createColumn({
      name: 'email',
      summary: search.Summary.GROUP // Group by email
    }),
    search.createColumn({
      name: 'amount',
      summary: search.Summary.SUM // Sum of amounts
    })
  ]
});

Handling Large Result Sets

For large datasets, you may encounter performance issues. NetSuite provides paged results, which let you process the results in smaller chunks (pages).

var resultSet = customerSearch.runPaged({
  pageSize: 100 // Page size to limit the number of records per page
});


resultSet.pageRanges.forEach(function(pageRange) {
  var page = resultSet.fetch({ index: pageRange.index });
  page.data.forEach(function(result) {
    log.debug('Customer', result.getValue('entityid'));
  });
});

Saved Search Integration

You can integrate saved searches into SuiteScript. Saved searches allow users to create custom searches via the UI, and you can load them programmatically using load().

Example of loading and using a saved search:

var customerSearch = search.load({
  id: 'customsearch_my_saved_search' // Saved search ID
});


customerSearch.run().each(function(result) {
  log.debug('Customer', result.getValue('entityid'));
  return true; // Continue iterating
});

Summary:

• N/SEARCH is used to search for and retrieve records based on filters and columns.
• Filters narrow down records to meet specific conditions (e.g., status, email).
• Columns define the data returned from each record.
• You can create dynamic searches, or load and execute saved searches.
• Paged results help efficiently process large data sets.