Five ways a NetSuite cash forecast goes quietly wrong

The business case is in the 13-week cash forecast lenders now expect. The build is a Suitelet dashboard with a scheduled job that collects bank, receivable, payable and payroll data into custom records. What follows are the things that made it look right while being wrong.

1. The same status filter means different things in different places

Open invoices are status A. Test the query through a SuiteQL connector or the REST endpoint and it returns thousands of rows:

SELECT COUNT(*) FROM transaction
WHERE type = 'CustInvc' AND status = 'A'

Deploy the same query inside a script and it returns zero. No error. Inside server-side SuiteScript, transaction status comes back in its prefixed form — CustInvc:A, not A — so the bare filter matches nothing. The forecast rendered, with no receivables at all.

The fix is to accept both forms, everywhere a status is filtered:

WHERE t.type = 'CustInvc'
  AND t.status IN ('CustInvc:A', 'A')

The same applies to every transaction type: VendBill, SalesOrd, PurchOrd, return authorizations. If a query is validated in one context and run in another, it needs both.

2. An amount that is populated outside the script is null inside it

The obvious column for what’s still owed on an invoice came back populated through the connector and null in the deployed script for a subset of rows. Summed, null is zero, and the forecast under-counted receivables without saying so.

The reliable fallback is to compute the open amount from the posting accounting lines:

NVL(t.amount_unpaid,
    (SELECT SUM(x.amountunpaid)
     FROM transactionaccountingline x
     WHERE x.transaction = t.id AND x.posting = 'T'))

3. Long text can come back truncated

The bank feed’s raw responses are stored in a long-text field: full transaction lists and balances as JSON. Read through runSuiteQL, very long values came back cut short. JSON.parse failed on some days and not others, depending on how much activity there had been.

The fix reads through SuiteQL first, and falls back to a direct field lookup when the value looks short or fails to parse:

let raw = queryLongText(id);
if (!raw || !looksComplete(raw)) {
    raw = search.lookupFields({ type: REC, id, columns: [FIELD] })[FIELD];
}

Related: that audit data only lives about ninety days. Anything the forecast needs for history gets copied into its own records on every run, not read from the source later.

4. The bank feed is a data source that lies by omission

Opening cash comes from the bank feed, because the GL lags the bank. While building the feed monitor we found two failures in the live feed itself, neither visible in the normal reconciliation screens:

• A three-week gap. For twenty-two days no transactions arrived for one bank’s accounts, while card accounts kept updating normally. Nothing alerted.
• Mirrored accounts. After the connection was re-established, three different accounts at that bank all returned the same transaction list and the same current balance. Two accounts were being fed the operating account’s activity.

The root cause sat upstream, in the bank’s own entitlements for the aggregator. The NetSuite mapping screens looked correct throughout, which is exactly why nobody had caught it. The monitor now checks for both conditions on every run: days since the last transaction per account, and identical transaction sets or balances across accounts that should differ.

5. Double counting between the run-rate and the schedule

Much of a company’s spending never becomes a vendor bill, so the forecast includes a run-rate of non-bill outflows, calibrated from bank history. Loan payments are also entered as scheduled items, because they’re known amounts on known dates. Without care, every loan payment is counted twice: once in the schedule and again inside the run-rate.

The run-rate now excludes the GL accounts that scheduled entries cover. The same idea applies to payroll and any other stream with its own explicit source: one source per dollar.

Smaller ones worth knowing

• Deployments that share a start time are refused on deploy for some scheduled script configurations; stagger them.
• A health check that runs before its first data will report “no downloads” on day one. Distinguish “never run” from “ran and found nothing.”
• An AI briefing with a low output limit stops mid-sentence. Size the limit to the brief you want, and check for a truncated stop reason.
• Long jobs need the page to poll. A user should never have to refresh to see whether the rebuild finished.

What we built in as a result

The dashboard has a Data sources panel on its settings tab: for each feed — open receivables, open payables, open orders, payroll, each bank account — the row count, the date range and the last successful read. A forecast resting on zero open invoices now says so in plain text instead of drawing a chart.

That panel is the real lesson. Silent failures can’t be prevented, but they can be made visible.

Related: the 13-week cash forecast lenders now expect, and an empty list is not an error message.