How-toOracle NetSuiteSaved Search / Reporting

How to use formula fields in a NetSuite saved search

Question
how to use formula fields in NetSuite saved search

Also searched as

  • netsuite saved search formula (text) field examples
  • netsuite saved search CASE WHEN formula
  • netsuite saved search formula date field
  • how to add a calculated column to a netsuite saved search

Short answer

In the saved search Results tab, add a column, set Field to "Formula (Text)", "Formula (Numeric)", "Formula (Date)" or "Formula (Currency)", then type an Oracle SQL expression into the Formula box using curly braces around field IDs, e.g. {trandate} or {item.custitem_weight}. Formula fields can also go on the Criteria tab so you can filter on the calculated value itself.

Applies to: NetSuite all current versions (2023.x-2026.x), any edition with saved search access. Formula fields use Oracle SQL syntax, not JavaScript.

Build a formula field in a saved search

  1. 1Open the saved search (Lists > Search > Saved Searches, or Reports > New Saved Search) and go to the Results tab.
  2. 2In a new row, set Field to the matching Formula type: Formula (Text), Formula (Numeric), Formula (Date), Formula (Currency), or Formula (Percent).
  3. 3Click the Formula field that appears and enter an Oracle SQL expression, referencing NetSuite fields in curly braces, e.g. CASE WHEN {amount} > 1000 THEN 'Large' ELSE 'Small' END.
  4. 4For joined record fields, prefix with the join field ID, e.g. {customer.category} or {item.custitem_lead_time}.
  5. 5Use Summary Type (Group, Sum, Count, Max) on the formula row if the search has any summary/grouped column elsewhere - NetSuite forces every column into summary mode once one column is summarized.
  6. 6To filter on the calculated value, add the same formula expression on the Criteria tab as a Formula (Text)/(Numeric) filter with its own operator and value.
  7. 7Test with Preview before saving; formula syntax errors show as a SuiteScript-style SQL error banner at the top of the results, not per row.
  8. 8For dates, wrap arithmetic in TO_DATE/TO_CHAR as needed, e.g. CASE WHEN {trandate} < TO_DATE(SYSDATE) - 30 THEN 'Overdue' ELSE 'Current' END.

Formula type determines what functions are allowed

NetSuite validates formula expressions against the declared type. A Formula (Numeric) field that returns a string, or a Formula (Text) field wrapped in TO_NUMBER on a null, will throw a search error rather than a blank cell. Pick the type that matches the final output of the expression, not the type of the input fields.

CASE WHEN...END is the most common pattern for bucketing values (aging buckets, size tiers, status labels). DECODE is a shorter equivalent for simple equality checks: DECODE({status}, 'A', 'Open', 'B', 'Closed', 'Other'). NVL({field}, 0) is the standard null-guard before doing arithmetic, since NULL + anything in Oracle SQL returns NULL, not the other operand.

-- Aging bucket on a transaction search
CASE WHEN {daysoverdue} <= 0 THEN 'Current'
     WHEN {daysoverdue} BETWEEN 1 AND 30 THEN '1-30'
     WHEN {daysoverdue} BETWEEN 31 AND 60 THEN '31-60'
     ELSE '60+' END

Joined fields and multi-level joins

Any field on a related record that appears as a join in the saved search's join dropdown can be referenced with dot notation inside the formula: {customer.terms}, {item.custitem_category}, {salesrep.email}. The join has to already be reachable from the record type the search is built on - if NetSuite does not offer it in the Field list, it usually is not reachable via a single join and needs a second saved search or a SuiteQL query instead.

Custom fields keep their script ID inside the braces exactly as defined, including the custitem/custbody/custrecord prefix. Checkbox custom fields return 'T'/'F' in formulas, so a CASE WHEN {custbody_field} = 'T' pattern is required rather than treating it as a boolean.

Formula summary rules and performance

Once any column in the search has a Summary Type set (Group, Sum, Count, Average), every other displayed column must also declare a summary type, including formula columns - usually Group for text/date bucket columns and Sum for numeric ones. This is the single most common reason a working formula search suddenly errors after someone adds a subtotal.

Formula fields execute per row inside the search engine, not client side, so a search with several nested CASE expressions and string functions across a large result set can slow noticeably. For recurring heavy reporting, move the logic into a SuiteQL query (Analytics > SuiteQL or via N/query in a script) which pushes the same SQL to the database more efficiently and can be scheduled or cached.

Common formula functions worth knowing

BUILTIN.DF({field}) returns the display value/name of a list or reference field instead of the internal ID - useful when {status} returns a raw code but you want the label. TO_CHAR({trandate}, 'YYYY-MM') buckets transactions by month for a pivot-style summary search. INSTR and SUBSTR let you parse text fields such as memo or external ID.

ROUND, TRUNC and standard arithmetic operators work as expected on Formula (Numeric) columns. For percent-of-total style formulas, note NetSuite has no native window function support in saved search formulas - that kind of comparison against a grand total needs either a second search or a SuiteQL query with analytic functions.

BUILTIN.DF({status})
TO_CHAR({trandate}, 'YYYY-MM')

Common pitfalls

  • !Mismatched formula type and returned data type causes an opaque search error banner rather than a row-level failure.
  • !Forgetting to add a Summary Type to a formula column once any other column in the search is summarized.
  • !Using a custom checkbox field as if it returns a real boolean instead of the string 'T'/'F'.
  • !Referencing a joined field that is not actually available from the base record, which NetSuite silently treats as null rather than erroring.
  • !Heavy nested formulas on large datasets causing search timeouts - move that logic to SuiteQL instead.
  • !Forgetting curly braces around field IDs, which NetSuite treats as literal text rather than a field reference.

How an ERP-grounded AI assistant handles this

ERPray can be asked in plain language for the same output a formula field would produce - "show open sales orders more than 30 days old grouped by sales rep" - and it drafts the underlying saved search or SuiteQL, including the CASE WHEN aging bucket, against the actual NetSuite account's custom fields rather than a generic example. It is still worth a human review before saving, since custom field IDs and join paths vary by account.

See ERPray

Frequently asked questions

Can I use a formula field as a saved search filter, not just a result column?

Yes. Add the same formula expression to the Criteria tab with type Formula (Text)/(Numeric)/(Date), set an operator and value, and NetSuite filters rows on the calculated result rather than a raw field.

Why does my formula field show blank instead of the expected value?

Usually a null in one of the referenced fields propagating through arithmetic or concatenation. Wrap numeric fields in NVL({field}, 0) and text fields in NVL({field}, '') before combining them.

Can saved search formulas call custom SuiteScript functions?

No. Formula fields only support Oracle SQL functions available in the saved search engine (CASE, DECODE, NVL, TO_CHAR, BUILTIN.DF, string and math functions). Custom logic needs a scripted process or a SuiteQL query instead.

What is the difference between a formula field and a SuiteQL query for reporting?

Formula fields live inside the saved search UI and are limited to what the search engine exposes per record type. SuiteQL runs closer to raw SQL against the underlying tables, supports joins and functions saved search does not, and is better for heavy or recurring reports.

Related

How-to

How to write and run SuiteQL queries in NetSuite

SuiteQL is NetSuite's read-only SQL dialect over the underlying record tables, run either interactively from Analytics > SuiteQL Query Tool (or the older /app/suiteanalytics query page), through REST at /services/rest/query/v1/suiteql, or programmatically via the N/query module in SuiteScript 2.x. It supports standard SELECT, JOIN, WHERE, GROUP BY and window functions against table names that mostly match record type IDs (transaction, transactionline, item, customer).

How-to

How to build a workflow in NetSuite with SuiteFlow

Go to Customization > Workflow > Workflows > New, pick the record type and select Server, Client, or both as the trigger context, then build states and transitions on the workflow diagram, attaching actions (Set Field Value, Send Email, Create Record, Custom Action Script) to each state or transition. Release the workflow (top right dropdown, Testing to Released) once validated, since a workflow left in Testing only fires for the workflow owner.

How-to

How to create an assembly build or work order in NetSuite

For simple manufacturing with no routing or WIP tracking, use Transactions > Manufacturing > Build Assemblies (or Enter Assembly Build), select the assembly item, quantity, and location, and NetSuite consumes the BOM components and receives the finished item in one transaction. For multi-step production needing routing, WIP accounting or partial completions, create a Work Order (Transactions > Manufacturing > Work Orders) and process it through Work Order Completion, which requires the Advanced Manufacturing feature (SuiteSuccess Manufacturing or WMS add-on) or the Manufacturing bundle.

How-to

How to run intercompany transactions in NetSuite (OneWorld)

Enable the Intercompany Framework and Automated Intercompany Management features (Setup > Company > Enable Features > Company), then create an Intercompany Sales Order or Purchase Order between two subsidiaries in the same NetSuite account; NetSuite auto-generates the matching intercompany transaction on the counterparty subsidiary and posts the elimination journal entries during period close if Advanced Intercompany Journal Entries is also enabled.

Error fix

Fix NetSuite SSS_USAGE_LIMIT_EXCEEDED Error

SSS_USAGE_LIMIT_EXCEEDED fires when a SuiteScript execution consumes all the governance units (usage points) allotted to its script type before it finishes. Fix it by checking runtime.getCurrentScript().getRemainingUsage() before expensive calls, yielding or rescheduling in Scheduled scripts, and moving heavy record-count work into Map/Reduce, which yields automatically across stages.

Error fix

Fix NetSuite INVALID_FLD_VALUE Error

INVALID_FLD_VALUE means NetSuite rejected a value you tried to set on a field because it does not match the field's expected type, list option, or reference record. Fix it by confirming the internal ID or text value actually exists on that field's source list and matches the field's value type (text versus list versus record reference) before setting it.

AI for ERP

AI for NetSuite, Beyond the Built-In Text Tools

NetSuite's built-in AI covers text generation, not grounded answers on your own data. See how a private LLM over SuiteQL adds real Q&A and controls.

Stuck on Oracle NetSuite?

Talk to engineers who work inside Oracle NetSuite every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles