Counting rows and summary types
How do I count the number of rows a NetSuite saved search returns?
Short answer
Add a Formula(Numeric) results column whose formula is the literal 1 and set its Summary to Sum. That total is the row count. Count does not give you one: it is COUNT(DISTINCT), so Count of {internalid} returns distinct transactions and Count of a constant returns 1.
Add a results column of type Formula(Numeric), put the literal 1 in the formula box, and set its Summary to Sum. The total of that column is the number of rows the search returned. Nothing else in the summary dropdown gives you a row count.
The column
On the Results tab, add a new column, choose Formula (Numeric) from the field list, and type a single character into the Formula box:
1
Set Summary to Sum. If the search is grouped, you get the row count per group and a correct grand total; if it is ungrouped and every other column is summarised, you get one row with the total.
The equivalent when you are building the search in script is a formulanumeric column with formula 1 and summary SUM. That is exactly how it was probed here, and it returns the row count in both the grouped and the ungrouped form.
Why none of the obvious options work
The Count summary is distinct-value counting, not row counting. That single sentence disposes of every alternative:
Count of {internalid} returns the number of distinct transactions, which on a transaction search is smaller than the number of rows as soon as a transaction contributes more than one line. In the account tested the gap was nearly fivefold, and the wrong number looked entirely reasonable.
Count of {line} returns the number of distinct line numbers, which is smaller still and means nothing.
Count of your Formula(Numeric) column containing 1 returns 1. Always. A constant has one distinct value, so Count of it is one, in every group, in every search. It is the cleanest demonstration of what Count actually does, and it is why the summary type on this column has to be Sum and not Count. The mechanism is set out in full on why Count returns fewer than the number of rows.
One more trap on the way in: the column type has to be Formula(Numeric). A Formula(Text) column with a Sum summary does not run at all, it is rejected with SSS_INVALID_SRCH_COLUMN_SUM.
Why this total can be trusted
Because it reconciles, and it was checked rather than assumed. A separate grouped probe on the same record type split every row into three buckets with a CASE formula and measured each bucket with Sum of 1. The three bucket totals add up to the ungrouped Sum of 1 for the whole search, exactly, with no rounding slack and no rows unaccounted for. Two different searches, two different shapes, same arithmetic.
That is the property you want from a row counter and the property Count does not have. Sum of a constant survives being split and re-totalled. Count of the same constant read 1 in each bucket of the grouped probe, and adding those up gets you the number of buckets.
How to check what your existing searches have been reporting
Take a saved search that already reports a count and add the Sum-of-1 column next to whatever count it currently shows. Run it once.
Count of {internalid} existing column
Sum of Formula(Numeric) = 1 the column you just added
If the two agree, that search has one row per record and its existing count was fine. If they disagree, the search has been under-reporting for as long as it has existed, and so has every export of it. Then delete the Count column rather than leaving both, because two numbers in one report that both claim to be a count is how the wrong one gets quoted.
Scope: one sandbox account on NetSuite 2026.1, Administrator role, transaction record type. The probes behind this page were built and run ad hoc through the SuiteScript search engine (nlapiCreateSearch / runSearch) without saving anything. The Sum-of-1 column has not been tested here on record types other than transaction, though nothing about it is transaction-specific.
How this was established
3 probe runs against one NetSuite 2026.1 sandbox account on the Administrator role, through the saved search UI and the SuiteScript search engine. Where this page draws a boundary around a finding, that boundary is the edge of what was actually run.
Related
- Why does the Count summary in my saved search return fewer than the number of rows?
Because Count is COUNT(DISTINCT), not a row count. It counts how many different values a column produces, so Count of {internalid} on a transaction search reports distinct transactions while the search returned lines. A Formula(Numeric) column of 1 summarised as Sum is the row count.
- What does "An nlobjSearchColumn contains an invalid column summary type" mean on a formula column?
You put Sum or Average on a Formula(Text) column. The message names the pair that failed, as in "formulatext: SUM.", under code SSS_INVALID_SRCH_COLUMN_SUM. Change the column to Formula(Numeric) or change the summary. Minimum and Maximum on a text formula are accepted and return a lexicographic result.
- Why does my Formula (Numeric) column return 39 decimal places?
Because Formula (Numeric) does not round at all. It hands back the raw Oracle NUMBER, which is what reaches your CSV export or SuiteScript variable, so 1/3 comes back with 39 decimal places. Formula (Currency) is not the fix: it rounded to 3 dp on some values and 2 on another. Use ROUND(x,2) in the expression.
From the blog
- Why Your NetSuite AP Aging Never Ties to the General Ledger
The AP Aging report and the accounts payable balance on the balance sheet disagree, and the difference is not a missing bill. Three specific traps in how NetSuite stores payables, and the SuiteQL that finds each one.