Filters and criteria
Why does a Formula (Text) criteria on internal id match far too many rows?
Short answer
Because a text formula makes every operator a string operator. Formula(Text) {internalid} contains '2' is a substring scan over the digits of the id, so it matches 2, 12, 20, 102 and everything else containing a 2. In the account tested it matched more than half the rows in the search, with no error. Use anyof, or Formula(Numeric).
Because the formula type decides the comparison, and on Formula(Text) every operator is a string operator. contains '2' is not "the id is 2" and it is not "the id is near 2". It is a substring scan over the digits: it matches 2, and also 12, 20, 21, 102, 1234 and every other id with a 2 anywhere in it.
In the account tested, Formula(Text) {internalid} contains '2' matched more than half of the rows the same search returns, around 54 percent of them. It raised no error. The row count came back looking like a real answer, which is the entire problem.
Minimal reproduction
Three criteria on the same transaction search, the same field, the same formula type. Only the literal and the operator change:
Formula(Text) {internalid} is '2' -> the lines of internal id 2
Formula(Text) {internalid} is '02' -> nothing at all
Formula(Text) {internalid} contains '2' -> more than half the searchThe middle line is the one that tells you what you are dealing with. '02' is the same number as '2', and it matches zero rows, because nothing here is a number. There is no numeric coercion anywhere in the text path, no zero-padding tolerance, no trimming. It is a literal string comparison against the digits as rendered.
The bottom line is the one that costs money. It ran, it returned a plausible row count, and nothing warned.
Use anyof, or Formula(Numeric)
For internal ids, use the field directly with anyof and skip the formula. anyof takes an array, so a single id and a list of ids are the same filter, and equalto is rejected outright on this field for reasons covered on its own page.
If a formula is genuinely required, because the id is one term in a larger expression, use Formula(Numeric) and never Formula(Text):
Formula(Numeric) {internalid} equalto 2That returned exactly the rows of internal id 2, the same rows that Formula(Text) {internalid} is '2' returned. Note that equalto was accepted here and refused on the plain internalid field in the same run, so the formula and the field it wraps do not take the same operators.
Why nobody notices until it is wrong
Because on valid input the two formula routes agree. Formula(Numeric) {internalid} = 2 and Formula(Text) {internalid} is '2' selected the identical rows in this run. A search built with the text form, tested against one known id, passes its test perfectly.
The divergence only appears when the operator changes. Someone widens the filter from one id to a range of them, reaches for contains, and the type of the formula quietly reinterprets the request. Nothing was edited except the operator. Nothing warned. The formula type was chosen weeks earlier by a different person, and it is the thing that decided the answer.
That is the general shape of the trap and it is not specific to internal ids. Any numeric field pulled through a text formula compares as text from that point on. This run measured that for is and for contains only, and did not test the ordering operators, so treat the rule as being about the formula type rather than about one operator on one field.
How to tell if you are affected
Open your saved searches and look down the Criteria sublist for the words Formula (Text) sitting next to an operator that is not is. A text formula over a numeric field is doing string work on digits whatever the operator says.
The quick confirmation on any one of them: change the criteria to Formula (Numeric) with the equivalent numeric operator, run it, and compare the row counts. If the two agree, the search was fine. If the text version returns dramatically more rows, it was never filtering on what its author believed. Count the rows with a summary Sum of a constant 1 rather than a Count, which measures something else on a transaction search.
Scope
One sandbox account on NetSuite 2026.1, Administrator role, transaction search, {internalid} as the formula field. These searches were built and run ad hoc through the SuiteScript search engine (nlapiCreateSearch / runSearch) without saving anything. The proportion above is what one account's data happened to produce; the mechanism is not proportional to anything, and in an account with different id ranges the same criteria would match a different share of the table.
How this was established
1 probe run 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 is equalto not a valid operator on internalid in a saved search filter?
Because NetSuite does not accept equalto on internalid: the filter is refused with SSS_INVALID_SRCH_OPERATOR and the search returns an error instead of rows. Use anyof, which takes an array and scales to a list. is also works, on a bare string, which is the part most people do not expect.
- Why does {type} return "Opportunity" but {type.id} returns "Opprtnty"?
Because {field} gives the display name and {field.id} gives the internal code. They are two different strings for the same value. {field.name} does not exist and fails silently. Note the trap: SuiteQL returns the code by default where a saved search formula returns the label, so literals copied between the two engines match nothing.
- How do I count the number of rows a NetSuite saved search returns?
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.