Stop Loading Records One at a Time: Bulk Fetch with SuiteQL and Group-By

NetSuite has had SuiteQL since 2020. Half the SuiteScript code I read still does this:

the-wrong-way.js

const results = search.create({
  type: 'invoice',
  filters: [['status', 'is', 'paidInFull']],
  columns: ['internalid'],
}).run().getRange({ start: 0, end: 100 });

for (const r of results) {
  const inv = record.load({ type: 'invoice', id: r.id });
  // ... read fields, read lines, do work ...
}

Every record.load is an HTTP round-trip and 10 governance units. With 100 invoices, you've burned 1000+ units before doing any real work. With 1000 invoices, you've blown your entire daily budget.

The right shape is one SuiteQL query that returns invoice header plus line data in a single round-trip, then group the flat rows into nested objects in JavaScript. This post is that pattern.

The query

SuiteQL lets you join transaction, transactionline, item, and customer in one statement. Pull every field you need in one shot.

bulk-fetch.sql

SELECT
  t.id                              AS internalid,
  t.tranid                          AS tranid,
  TO_CHAR(t.trandate, 'YYYY-MM-DD') AS trandate,
  c.email                           AS email,
  c.companyname                     AS companyname,
  BUILTIN.DF(tl.item)               AS itemlabel,
  i.itemid                          AS productid,
  tl.rate                           AS rate
FROM Transaction t
JOIN TransactionLine tl ON t.id    = tl.transaction
JOIN Item i             ON tl.item = i.id
JOIN Customer c         ON t.entity = c.id
WHERE t.type            = 'CustInvc'
  AND BUILTIN.DF(t.status) = 'Invoice : Paid In Full'
  AND tl.mainline         = 'F'
  AND tl.itemtype         IN ('Assembly', 'InvtPart')
ORDER BY t.id, tl.linesequencenumber

Three things worth knowing:

BUILTIN.DF() resolves an enum value to its display label. Without it, tl.item returns the internal ID. With it, you get "Heating Element 5kW" or whatever the user sees in the UI. BUILTIN.DF(t.status) resolves the transaction status code to its display name. Cheapest way to get human-readable values without a separate lookup.
tl.mainline = 'F' excludes the summary row. Every transaction in NS has a "mainline" row that's the header-level totals. You want detail lines only.
itemtype IN ('Assembly', 'InvtPart') filters to physical goods. Skip service items, discount items, subtotal items. Each transaction line has an item type and you almost always want a subset.

The group-by in JavaScript

SuiteQL returns flat rows. One row per invoice line means an invoice with 5 lines shows up 5 times. You want a nested structure: one object per invoice with a lines array.

group-rows.js

const rows = query.runSuiteQL({ query: sql }).asMappedResults();

const invoices = Object.values(
  rows.reduce((map, row) => {
    const id = row.internalid;
    if (!map[id]) {
      map[id] = {
        internalid: id,
        tranid:      row.tranid,
        trandate:    row.trandate,
        email:       row.email,
        companyname: row.companyname,
        lines:       [],
      };
    }
    map[id].lines.push({
      itemlabel: row.itemlabel,
      productid: row.productid,
      rate:      row.rate,
    });
    return map;
  }, {})
);

One pass through the rows. Build a map keyed by invoice ID. Push each line into its array. The result is an array of invoice objects ready to process.

For 100 invoices with ~5 lines each (500 rows total), the whole sequence costs about 10 governance units and one round-trip. The naive record.load approach for the same 100 invoices is 1000+ units and 100 round-trips. The difference shows up the moment you scale past a handful of records.

The NULL NOT IN gotcha

If you add a "skip rows where some field is in this list" filter, watch out for Oracle's three-valued logic. NULL NOT IN (1, 2, 3) evaluates to UNKNOWN, not TRUE, and Oracle drops UNKNOWN rows. Which means any transaction without a value in that field gets silently excluded.

The fix is an explicit NULL guard:

AND (t.employee IS NULL OR t.employee NOT IN (139, 245, 312))

This bit me on a production report once. We were excluding internal test reps and 30% of the transactions just disappeared. Turned out they were entered without a rep assigned at all. The audit trail looked correct. The data was wrong. The fix was one extra clause.

SuiteQL vs saved searches

Saved searches are still the right tool when:

The customer's NetSuite admin owns the query and wants to edit it in the UI.
The result set is displayed in NetSuite (portlets, dashboard tiles, list pages).
You need the search's permission scoping to respect the calling user's role.

SuiteQL is the right tool when:

The query is consumed by SuiteScript and you don't need humans to edit it.
You're joining tables in ways saved searches can't (or where the search's joined-result format is awkward to consume).
You want lower governance cost and faster results.
You need a structure (sub-select, BUILTIN function, CASE WHEN) saved searches don't support.

For an integration script that runs as the system, SuiteQL is almost always the better choice. For an end-user-facing dashboard, saved searches still win.

asMappedResults vs the paged iterator

query.runSuiteQL({ query }).asMappedResults() returns the full result set as an array of objects with column names as keys. Easy to work with, but it materializes everything in memory at once.

For result sets above a few thousand rows, prefer the paged iterator:

const pagedData = query.runSuiteQLPaged({ query: sql, pageSize: 1000 });
pagedData.pageRanges.forEach(pageRange => {
  const page = pagedData.fetch({ index: pageRange.index });
  page.data.asMappedResults().forEach(row => {
    // process row
  });
});

Below a few thousand rows, asMappedResults() is fine and easier to reason about. Above it, the paged form keeps memory bounded. Pick the right one for the size of the query.