Searching for Missing Records with SuiteScript

Created: February 1, 2024

This is a sample chapter adapted from the "Advanced 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 .

The majority of the time that we're building NetSuite searches, we're searching for the existence of Records that meet our criteria. Once in a while, however, we actually need to search for the absence of something, and this can often be trickier.

For example, how do we find out which Employees have no Time Entries for this week?

If the Records don't exist, we can't find them directly. Because we are looking for the absence of a Record, we'll actually need to combine the Results from two different searches.

/**
 * Finds Employees that have no Time Entries for the last week.
 *
 * Combines the Results from two different searches in order to search
 * for the absence of Records.
 *
 * @author Eric T Grubaugh <eric@stoic.software> (https://stoic.software/)
 */
require(['N/search'], (s) => {
  const employeeSearch = s.create({
    type: s.Type.EMPLOYEE,
    filters: [
      ['isinactive', s.Operator.IS, 'F']
    ],
    columns: [
      { name: 'formulatext', formula: "{firstname} || ' ' || {lastname}" }
    ]
  })

  const timeSearch = s.create({
    type: s.Type.TIME_BILL,
    filters: [
      ['date', s.Operator.WITHIN, 'thisBusinessWeek'], 'and',
      ['type', s.Operator.ANYOF, 'A'] // 'A' => 'Actual Time'
    ],
    columns: [
      { name: 'employee', summary: s.Summary.GROUP }
    ]
  })

  // Display result counts from each search
  console.log(`# Employees = ${employeeSearch.runPaged().count}`)
  console.log(`# Employees with Entries = ${timeSearch.runPaged().count}`)

  // Assume there are less than 1,000 Employees
  const employees = employeeSearch.run().getRange({ start: 0, end: 1000 })
  const timeEntries = timeSearch.run().getRange({ start: 0, end: 1000 })

  // Does the given employee have at least one Time Entry in entries Array?
  const employeeHasEntry = (entries, employee) =>
    entries.some((entry) => {
      let entryEmployee = entry.getValue({
        name: 'employee',
        summary: s.Summary.GROUP
      })
      return (entryEmployee == employee.id)
    })

  // Returns a copy of employees Array but removes any Employees
  // that *do* have Time Entries in entries Array
  const findEmployeesWithNoEntries = (employees, entries) =>
    employees.filter((employee) => !employeeHasEntry(entries, employee))

  const printEmployeeName = (result) =>
    console.log(result.getValue({ name: 'formulatext' }))

  const employeesWithNoEntries = findEmployeesWithNoEntries(
    employees,
    timeEntries
  )

  console.log(`# Employees With No Entries = ${employeesWithNoEntries.length}`)
  employeesWithNoEntries.forEach(printEmployeeName)
})

To determine which Employees have zero time entries this week, we can find the logical difference between the list of all Employees and the list of Employees who do have time entries this week:

const employeeSearch = s.create({
  type: s.Type.EMPLOYEE,
  filters: [
    ['isinactive', s.Operator.IS, 'F']
  ],
  columns: [
    { name: 'formulatext', formula: "{firstname} || ' ' || {lastname}" }
  ]
})

const timeSearch = s.create({
  type: s.Type.TIME_BILL,
  filters: [
    ['date', s.Operator.WITHIN, 'thisBusinessWeek'], 'and',
    ['type', s.Operator.ANYOF, 'A'] // 'A' => 'Actual Time'
  ],
  columns: [
    { name: 'employee', summary: s.Summary.GROUP }
  ]
})

console.log(`# Employees = ${employeeSearch.runPaged().count}`)
console.log(`# Employees with Entries = ${timeSearch.runPaged().count}`)

const employees = employeeSearch.run().getRange({ start: 0, end: 1000 })
const timeEntries = timeSearch.run().getRange({ start: 0, end: 1000 })

We run two separate Searches to get our requisite data.

Now in order to determine which Employees don't have any Time Entries, we can "subtract" the Employees who do have Time Entries from the list of all Employees. This will leave us with Employees who do not have Time Entries.

To accomplish this, we leverage the JavaScript Array's filter and some methods.

First, we write the employeeHasEntry function that accepts an Array of Time Entry Search Results and a single Employee Search Result. Its job is to determine whether a specific Employee has a Time Entry:

const employeeHasEntry = (entries, employee) =>
  entries.some((entry) => {
    let entryEmployee = entry.getValue({
      name: 'employee',
      summary: s.Summary.GROUP
    })
    return (entryEmployee == employee.id)
  })

We use some to determine if the given employee exists within the entries Array.

Now that we can detect whether a single Employee has any Time Entries, we need to extend that over the full list of Employees. This falls to findEmployeesWithNoEntries:

const findEmployeesWithNoEntries = (employees, entries) =>
  employees.filter((employee) => !employeeHasEntry(entries, employee))

We use employeeHasEntry as the predicate of a filter to remove the Employees that do have a Time Entry from employees.

We can now pass in our two separate Result Arrays and list the Employees that have no Time Entries this week:

const employeesWithNoEntries = findEmployeesWithNoEntries(
  employees,
  timeEntries
)

console.log(`# Employees With No Entries = ${employeesWithNoEntries.length}`)
employeesWithNoEntries.forEach(printEmployeeName)

This will print out the number of Employees with no Time Entries, followed by the list of their names.