NetSuite Saved Search Formula Examples and Tips

NetSuite saved search formulas let you calculate, classify, format, and filter search results with SQL-style expressions, usually using field references in braces such as {amount}. Common examples include a conditional flag with CASE WHEN, a calculation such as {amount}/{quantity}, and null-safe text using NVL({memo}, 'No memo'); choose the matching Formula field type when adding one to a search.

How saved search formulas work

A saved search formula is an expression evaluated for records returned by a search. It is not a complete SQL query: you do not write a SELECT or FROM clause. Instead, refer to NetSuite fields with curly-brace field IDs and combine them with supported SQL expressions and functions. NetSuite uses Oracle-style SQL syntax for these formulas, but the available fields and behavior depend on the search type and record.

To add one, open or create a saved search, go to its Results tab, add a result column, and select a formula field such as Formula (Numeric), Formula (Text), Formula (Date), Formula (Percent), or Formula (Currency). Enter the expression in that row, give the column a useful label, and save or preview the search. The exact navigation to the saved search list can differ by role and account configuration.

Field tokens are not necessarily the labels shown on screen. In the formula editor, use the field picker where available, or check the field ID for the record type. A token that works in an invoice search might not be available in a customer or item search. Formula type also matters: a text expression belongs in Formula (Text), while a date expression should be returned as a date in Formula (Date).

Basic calculation examples

For a transaction search, this unit-value example divides an amount by quantity. Choose Formula (Currency) if the result represents currency; if you want a plain numeric ratio, choose Formula (Numeric). With a line amount of 240 and quantity of 6, the result is 40.

CASE WHEN {quantity} IS NULL OR {quantity} = 0 THEN 0 ELSE {amount} / {quantity} END

The zero and null checks prevent a divide-by-zero error and define what to return when there is no usable quantity. Returning zero is only one possible business rule: if zero could be mistaken for a real unit value, return null instead by using THEN NULL, or use a separate flag to identify incomplete data.

To calculate a 10% surcharge from a line amount, use Formula (Currency). For an amount of 75, the expression returns 7.50. This is a calculation for reporting; it does not change the transaction or add a charge to it.

{amount} * 0.10

Check how the search represents amounts before relying on a result. Transaction searches can include different line types, and a transaction amount may be positive or negative depending on the record and accounting context. Do not assume that a formula result is automatically an invoice total or a posting amount.

Conditional labels and flags

CASE is useful when a result should depend on a condition. This Formula (Text) example labels amounts of at least 1,000 as “Large” and everything else as “Standard.” An amount of 1,250 returns Large; an amount of 800 returns Standard.

CASE WHEN {amount} >= 1000 THEN 'Large' ELSE 'Standard' END

For a numeric flag, return 1 or 0. This Formula (Numeric) example marks transaction rows whose amount is over 1,000. It can be useful for filtering or for a summary count, but remember that transaction searches may return multiple rows per transaction.

CASE WHEN {amount} > 1000 THEN 1 ELSE 0 END

When checking whether a text or list field has a particular value, confirm what the formula exposes: it may be an internal value rather than the familiar display label. A condition that compares a status to a guessed label can silently miss records. Test the expression on a small set and inspect the returned values before building important reporting around it.

Dates, aging, and null values

To show the number of days between a due date and today, use a Formula (Numeric) expression. For a due date 12 days before the current date, this returns 12. A future due date returns a negative number, so the result is elapsed days relative to today, not automatically a positive “days overdue” measure.

TRUNC(SYSDATE) - TRUNC({duedate})

For invoices, a more targeted overdue label can check both the date and remaining balance. Use Formula (Text); the example returns Overdue only when a due date exists, is before today, and the remaining amount is greater than zero. Otherwise it returns Not overdue. Confirm that the selected transaction search exposes {amountremaining} and {duedate} for the records you need.

CASE WHEN {duedate} IS NOT NULL AND TRUNC({duedate}) < TRUNC(SYSDATE) AND NVL({amountremaining}, 0) > 0 THEN 'Overdue' ELSE 'Not overdue' END

NVL(value, replacement) substitutes a value when the first argument is null. It is especially useful for numeric calculations, comparisons, and text output. Choose a replacement with care: treating a missing amount as zero can be appropriate for a total, but treating a missing date as today could conceal a data-quality problem. A formula should express the intended business rule, not merely suppress an error.

Text formatting and date grouping

For a Formula (Text) column that combines a record name and memo, Oracle-style concatenation uses two vertical bars. This example inserts a separator and substitutes “No memo” if the memo is blank. The entity field’s displayed value depends on the search context, so preview it to ensure the output is the identifier users expect.

{entity} || ' - ' || NVL({memo}, 'No memo')

To return the first day of a transaction’s month as a date, use Formula (Date) with TRUNC. For a transaction dated 18 March 2026, the result is 1 March 2026. This can help create a consistent month-level date column, although grouping and date display depend on the search’s result and summary settings.

TRUNC({trandate}, 'MM')

Do not confuse a formatted date string with a date value. A Formula (Text) expression using a date-to-text function may look convenient, but text sorts differently from dates and may not behave like a date in filters or summaries. Keep the return type as Date when users need date sorting or date-based grouping.

Summary searches and choosing the right formula type

Formula fields can appear in detail searches and summary searches, but a formula that works on individual rows can produce a misleading summary if it is aggregated incorrectly. For example, a unit value calculated separately on each line and then averaged is not necessarily the overall unit value. The overall calculation is generally total amount divided by total quantity, not the simple average of each line’s unit value.

In a summary search, decide whether the formula should be evaluated per record and then summed, averaged, or grouped, or whether the calculation needs to be based on summarized totals. Set the result row’s Summary Type deliberately, and compare the output with a small hand-checkable sample. When a weighted ratio is important, show the summarized numerator and denominator separately or use a supported summary formula approach validated for that search type; do not assume an average of row-level percentages equals the overall percentage.

Use Formula (Numeric) for counts-as-flags, day differences, and arithmetic without currency formatting.
Use Formula (Currency) for monetary calculations that should display as currency.
Use Formula (Percent) when the result is a proportion intended to display as a percentage; check the display and scaling with known values.
Use Formula (Text) for labels and concatenated values, and Formula (Date) for actual date results.

A formula field changes what the search displays or evaluates; it does not update the underlying record. If a value needs to be stored, used in workflows, or written back to a transaction, a saved search formula alone is not the mechanism for doing that.

Common errors and how to troubleshoot them

Formula errors often come from a mismatched field ID, an unsupported field on that record type, a return value that does not match the selected formula type, or an expression that assumes every value is populated. Begin with the simplest possible expression, preview it, then add conditions in small steps. If one token causes an error, remove it and confirm the field is available in that search.

Check spelling and braces around field IDs, including any joined-record prefix.
Use single quotes around text literals in SQL expressions and keep parentheses balanced.
Guard divisions against zero and use NVL where null handling is part of the intended result.
Test dates around month boundaries, due dates, and records with no date.
Check transaction line behavior, main-line settings, and joins to avoid duplicate or unexpected rows.
Preview with known records and compare formula output against the record itself or a manual calculation.

A common misconception is that a formula field is a spreadsheet formula or can use any Excel function. It cannot: NetSuite saved search formulas use the supported formula syntax and available record fields, not Excel cell references or the full Excel function library. Another frequent issue is assuming a formula label affects filtering. A formula result can be used in search criteria only where the search interface supports that formula type and expression; placing a formula on the Results tab alone does not filter records.

Frequently Asked Questions

Can I use CASE WHEN in a NetSuite saved search?
Yes. CASE expressions are commonly used to return conditional labels or values. Match the formula field type to the result, and include an ELSE branch when you want a defined result for records that do not meet the condition.

How do I handle a blank value in a saved search formula?
Use a null check such as IS NULL or a suitable NVL replacement. The right replacement depends on the business meaning: zero, a text label, and a missing value are not interchangeable.

Can a saved search formula calculate days overdue?
Yes. Subtract a due date from TRUNC(SYSDATE) for a day difference, then add conditions for a past due date and a positive remaining balance if the result should mean an unpaid overdue invoice specifically.

Why does my formula work in one search but not another?
Search types expose different fields and joins, and some field tokens may be unavailable or represent different data in another context. Verify the field ID and test the expression in the exact search where it will run.

Do saved search formulas change NetSuite records?
No. They calculate values for the search result or criteria; they do not write those values back to the source record. Use an appropriate record update, workflow, or other process if the value must be stored.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.