NetSuite Saved Search Formulas: What Breaks and Why

A saved search rarely fails loudly. It returns a number. The number is wrong, and nothing on the screen suggests it might be. That is the whole problem with saved search formulas: the failure mode is a plausible answer.

This is a guide to the specific places where that happens. It assumes you have built saved searches before and are past the "Lists > Search > Saved Searches > New" stage.

The formula box is Oracle SQL, and that explains most of the strangeness

NetSuite saved searches compile down to SQL against an Oracle database. When you type into a Formula (Numeric) field, you are writing a SQL expression fragment. NetSuite substitutes every {fieldid} with a real column or a join, wraps the result in its own query, and hands it to Oracle.

Two consequences follow, and almost every confusing behaviour traces back to one of them.

First, Oracle functions work. NVL, DECODE, CASE WHEN, TO_CHAR, TRUNC, REGEXP_REPLACE, SUBSTR, ROUND, LISTAGG are all available even though nothing in the NetSuite UI advertises them. If a function exists in Oracle 19c, it will probably run here.

Second, Oracle's semantics apply, including the ones that differ from other SQL dialects. The most consequential: Oracle treats the empty string as NULL. There is no distinction. This single fact causes a category of saved search bugs that people spend hours on.

Finding the field ID, and why your three sources disagree

Before a formula can be wrong, you need the field ID to put in it. There are three ways to get one, and they return different sets of fields. Knowing which one you are looking at saves a lot of time.

Field Level Help. Turn on Setup > Company > Enable Features > SuiteCloud > Client SuiteScript, then check Show Internal IDs under Home > Set Preferences > General > Defaults. Now clicking any field label shows a popup with the field ID in it. This is the fastest method and the one to reach for by default. It only tells you about fields that are actually rendered on the form you are looking at.

The &xml=T trick. Append &xml=T to a record URL and NetSuite returns the record as XML with field IDs as tags. Fast, and it shows you the values alongside the IDs, which Field Level Help does not. Same limitation, and a sharper one: it reflects the form, so any field hidden by the current form or by a role restriction simply is not there. People conclude a field does not exist when it is only not displayed.

The Records Browser and the Records Catalog. These show the schema, which is the real answer. The Records Browser (versioned per NetSuite release) lists every standard field of every record type along with its join names and its search-vs-body distinction. The newer Records Catalog does the same in-account and, crucially, includes your custom fields. If a field is not in Field Level Help and not in &xml=T, this is where to look.

Custom fields carry their prefix as a type signal: custbody is a transaction body field, custcol a transaction column field, custentity an entity field, custitem an item field, custrecord a field on a custom record. A custcol field will be NULL on a main-line row. That is not a bug, and it is the single most common reason a formula that "should work" returns nothing.

NULL is contagious, except where it is not

Any arithmetic or comparison touching NULL produces NULL. Join across a record that does not exist for a given row and the entire expression collapses:

{quantity} * {custitem_unit_weight}

Every item with a blank weight gives NULL, not zero, and the summed total is quietly short. The fix is not glamorous:

NVL({quantity}, 0) * NVL({custitem_unit_weight}, 0)

Wrap the operands, not the result. NVL({quantity} * {custitem_unit_weight}, 0) compiles and gives you zeros where you wanted them, but it also hides the distinction between a genuine zero and missing data, which matters the moment someone asks why the report changed.

The exception is string concatenation. In Oracle, || treats NULL as an empty string, so this does what you expect:

{entity.firstname} || ' ' || {entity.lastname}

A missing first name gives you a leading space, not NULL. Convenient, and also the reason people incorrectly generalise that NULL is harmless.

Comparisons deserve their own warning. {custbody_approver} != 'jsmith' will not return rows where the approver is empty, because NULL is not equal to anything and is also not unequal to anything. If you want "everyone except jsmith, including blanks", you have to say so:

CASE WHEN NVL({custbody_approver}, 'x') != 'jsmith' THEN 1 ELSE 0 END

Main line, or why your revenue doubled

This is the most common transaction saved search bug in existence, and it has nothing to do with formulas.

NetSuite stores a transaction as a header plus lines, and it stores shipping, tax and cost of goods sold as additional lines. A transaction search with no line filter returns one row per line. Sum the amount column and you have counted the shipping cost, the tax and in some cases the COGS as revenue.

The controls are three criteria fields:

• Main Line = true gives one row per transaction, header values only. Line-level fields, including every custcol, come back NULL.
• Main Line = false gives one row per line, including the shipping, tax and COGS lines.
• Shipping Line = false and Tax Line = false remove those.

So a line-level search that sums correctly usually needs all three: Main Line = false, Shipping Line = false, Tax Line = false. A header-level search needs Main Line = true and nothing else.

The trap is the middle case. You add Main Line = false because you need an item column, the total goes up rather than down, and it looks like the filter did the opposite of what it should. It did not. You removed the header row and added the shipping and tax rows in one move.

There is a related version of this with COGS on inventory items: Cost of Goods Sold = false exists as a criterion for exactly this reason, and it is worth checking whenever an inventory-heavy search reports numbers that are too large by an amount that does not correspond to anything.

Criteria, Results and Summary are three different points in the query

This distinction is not explained anywhere in the UI, and it determines both correctness and speed.

A formula on the Criteria tab becomes part of the WHERE clause. It runs against every candidate row before anything is grouped. The pattern is always the same: add Formula (Numeric), set the formula to a CASE WHEN ... THEN 1 ELSE 0 END, and set the comparison to equal to 1.

A formula on the Results tab becomes part of the SELECT list. It runs on rows that already survived the criteria.

The Summary subtab of the Criteria tab, which most people never open, becomes the HAVING clause. It filters after grouping. This is how you express "customers with more than five orders" in a single search. Trying to do it with an ordinary criterion cannot work, because at WHERE time the group does not exist yet.

The practical consequence for performance: a formula criterion cannot use an index. Oracle has to evaluate the expression per row. If your search is CASE WHEN TO_CHAR({trandate}, 'YYYY') = '2026' THEN 1 ELSE 0 END, the database reads every transaction ever created and calls TO_CHAR on each one. The same search expressed as a plain Date within this year criterion uses the index on trandate and touches a fraction of the table.

The rule that follows: filter on real indexed fields first (date ranges, type, status, subsidiary), and use formula criteria only for the residue that cannot be expressed any other way. On an account with millions of transactions this is the difference between two seconds and a timeout.

The declared type matters more than the expression

Formula (Text), Formula (Numeric), Formula (Date), Formula (Currency), Formula (Percent) and Formula (Duration) are not cosmetic. They tell NetSuite what type to cast the result to, and the cast happens whether or not it makes sense.

Put {trandate} in a Formula (Text) and you get a locale-formatted string, which sorts alphabetically. December sorts before February. If you need a sortable text date, be explicit:

TO_CHAR({trandate}, 'YYYY-MM-DD')

Put a CASE WHEN returning strings into a Formula (Numeric) and you get ORA-01722: invalid number, which at least fails loudly. The quieter failure is a Formula (Numeric) that returns a number as text via a stray ||, which Oracle will implicitly convert until the day one row contains something unconvertible.

Formula (Currency) and Formula (Percent) are Formula (Numeric) with display formatting attached. They do not do currency conversion. Multi-subsidiary amounts are handled by the Consolidated Exchange Rate field on transaction searches in OneWorld, which defaults to Per-Account and quietly changes every number in the report if someone sets it to None.

The 4000-character wall

Formula fields cap at 4000 characters. Long CASE WHEN ladders and generated IN lists hit this faster than you expect: a list of internal IDs as quoted strings runs about 8 characters each, so roughly 450 IDs and you are done.

The workaround is to split across multiple criteria and combine them in the expression editor with OR, using the parenthesis and AND/OR columns that appear when you check Use Expressions on the Criteria tab. It works, it is ugly, and it should be a signal. If you are pasting 450 internal IDs into a saved search, the actual answer is usually a custom field, a group, or a saved search that computes the set rather than enumerating it.

Three things that silently do nothing

A summary search will not work as a dashboard reminder. Reminders on a grouped saved search always display 0. There is no warning. If a reminder is stuck at zero, check whether the underlying search has any Summary Type set on its Results tab before debugging anything else.

A scheduled email without Summarize Scheduled Emails sends one email per result row. A search that matches a thousand records sends a thousand emails, and once the schedule has fired there is no way to stop it mid-flight. Check the box.

Available Filters do not restrict anything. The Available Filters tab adds interactive filters for the person viewing the results. It is not a security control and it is not a criterion. Restriction happens in Criteria, and the Public checkbox plus the Audience tab control who can see the search at all.

When to stop writing formulas and switch to SuiteQL

Saved searches are a query builder with a join model that was designed around the record hierarchy, not around SQL. Some questions do not fit that model, and formulas become a way to fight it rather than use it.

Reach for SuiteQL when you need a self-join, a subquery in the select list, a window function, a union of two record types, or a join path NetSuite does not expose. SELECT ... FROM transaction t JOIN transactionline tl ON tl.transaction = t.id in the SuiteQL query tool answers in one statement what takes three saved searches and a spreadsheet.

Reach for a saved search when the result needs to be a dashboard portlet, a reminder, a sublist filter, a workflow condition, an email schedule, or a parameter to a SuiteScript. SuiteQL cannot be any of those things. It is a query language, not a NetSuite object.

The honest summary is that most people should be using both, and that the deciding factor is what consumes the result, not which one is more elegant.

The part that is actually hard

None of this is intellectually difficult. NVL is not a hard concept. Main line is not a hard concept. What makes saved searches expensive is that the knowledge above is distributed across a schema browser, a release-versioned reference, a support article, three community threads and whatever your predecessor happened to know. Every one of these mistakes is obvious in retrospect and invisible in advance.

That gap is the reason we built MokuBot, an AI agent that runs inside your NetSuite session and builds searches from a description of what you want. It resolves field IDs against your account rather than against a generic schema, so custom fields are just there, and it sets main line correctly because it knows what you asked for. You still review the search before saving it. The point is not that the model is smarter than you. The point is that it has read the Records Catalog and you have not.

If you would rather keep writing them by hand, the single highest-return habit is to build every transaction search twice: once at header level with Main Line = true, once at line level with the three line filters set, and check that the totals agree. When they do not, the difference tells you exactly which line type you forgot.