Nine saved searches, one SuiteQL query

At some point, most NetSuite implementations run into the same problem:

You need a unified view across multiple transaction types : something operational, not exploratory and the first instinct is to reach for saved searches.

It works. Until it doesn’t.

In one such case, a Suitelet was responsible for showing line-level data across multiple transaction types filtered down to what was actionable “today.” The implementation relied on multiple saved searches stitched together in JavaScript.

It was functional, but slow enough that it stopped being useful.

Why saved searches break down??

Saved searches are excellent for interactive use. They’re not designed to act as a backend for operational tooling.

The friction shows up quickly:

• Limited join flexibility. Cross-transaction relationships often require workarounds - formula fields or post-processing in SuiteScript.
• Inconsistent field behavior. Some fields exist at the header level for certain records and at the line level for others, which makes normalization difficult.
• Cumulative governance cost. Multiple searches combined with per-row lookups lead to unnecessary overhead.

Individually, none of these are fatal. Together, they add up.

Moving the work into the database

The alternative was to collapse the logic into a single SuiteQL query.

Not because it’s shorter - it isn’t - but because it gives you:

• one execution plan
• one round-trip
• one place to reason about the result

A simplified version of the query looks something like this:

SELECT
  t.id,
  t.tranid,
  t.type,
  tl.linesequencenumber,
  tl.item,
  tl.location,
  tl.quantity,
  tl.quantityshiprecv,
  tl.expectedshipdate
FROM transaction t
INNER JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type IN ('SalesOrd', 'TrnfrOrd', 'VendRtrn')
  AND tl.mainline = 'F'
  AND NVL(tl.quantityshiprecv, 0) < tl.quantity
  AND tl.expectedshipdate <= TRUNC(SYSDATE)
ORDER BY tl.expectedshipdate, t.tranid;

The result

• Page load: ~1-1.5mins → under 5s.
• Suitelet governance: down by an order of magnitude.
• Operations actually uses the screen again.

The boring lesson, in case it needs repeating: when SuiteScript is doing too much work in JavaScript, the answer is often to push the work back down into the database. SuiteQL is right there.