Nine saved searches, one SuiteQL query

The brief for the Ready to Fulfill screen was simple on paper: show every line — across Sales Orders, Transfer Orders, and Vendor RMAs — that the warehouse could actually pick today. The existing implementation was nine saved searches stitched together in a Suitelet, each one separately governed, separately slow, and separately confused about what “today” meant.

Loading the page took fifteen seconds. Sometimes more. Operations had stopped using it.

Why saved searches were the wrong abstraction

Saved searches are great when one user is exploring data interactively. They’re a bad fit when you’re trying to build a unified operational view from three different transaction types, with different join paths into inventory, locations, and ship-date fields.

The specific failures:

• No real joins. Every cross-transaction relationship had to be hacked through formula columns or post-processed in JavaScript.
• Field availability is uneven. expectedshipdate lives on the header for some record types and on the line for others. Saved searches paper over this badly.
• Governance dies a death of a thousand cuts. Nine searches × per-row property lookups = thousands of search.lookupFields calls.

What SuiteQL changed

The replacement is one query. It’s longer than I’d usually want, but it’s one query — meaning one round-trip, one execution plan, one place to reason about the result set.

SELECT
  t.id              AS transaction_id,
  t.tranid          AS document,
  t.type            AS tran_type,
  tl.linesequencenumber AS line_no,
  tl.item           AS item_id,
  tl.location       AS location_id,
  tl.quantity       AS qty_ordered,
  tl.quantityshiprecv AS qty_fulfilled,
  tl.expectedshipdate AS line_ship_date
FROM transaction t
INNER JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type IN ('SalesOrd', 'TrnfrOrd', 'VendRtrn')
  AND t.status NOT IN ('SalesOrd:F', 'TrnfrOrd:H')
  AND tl.mainline = 'F'
  AND NVL(tl.quantityshiprecv, 0) < tl.quantity
  AND tl.expectedshipdate <= TRUNC(SYSDATE)
ORDER BY tl.expectedshipdate, t.tranid;

Two things worth pointing out:

1. TransactionLine.expectedshipdate is what you want for line-level scheduling. The header field exists but isn’t reliable when the order has been partially committed.
2. NVL(tl.quantityshiprecv, 0) — without the NVL, lines that have never been touched end up with a NULL in the comparison and silently fall out of the result. Took me longer than I’d like to admit to track down.

The result

• Page load: ~15s → 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.

A residual mystery

The new query returns 35 rows where the legacy saved search returned 33. I have not yet figured out which is right. I suspect the saved search was silently filtering on a stale “Mark for Fulfillment” flag that doesn’t really map to whether something is fulfillable. But that’s a story for a different post.