NetSuite Saved Search Formulas: Complete Guide and Examples

NetSuite saved-search formulas let you calculate, classify and format values that are not available as ordinary search fields.

A formula can be used in three main places:

Criteria to decide which records are returned.
Results to calculate a displayed column.
Highlighting to draw attention to matching rows or summary values.

The syntax resembles Oracle SQL, but saved searches support only a subset of Oracle expressions. Use NetSuite’s formula reference as the authority rather than assuming that every Oracle function will work.

Choose the formula type by its result

The selected formula field tells NetSuite what data type the expression returns.

Formula type	Use it for
Formula (Text)	Labels, codes and concatenated text
Formula (Numeric)	Counts, flags and calculations
Formula (Currency)	Monetary calculations
Formula (Percent)	Ratios intended to display as percentages
Formula (Date)	A date without a time component
Formula (Date/Time)	A date and time value
Formula (HTML)	Deliberately rendered HTML when the feature and permission are enabled

Choose the type from the value returned by the complete expression, not merely the type of the first referenced field.

For example, this returns words and belongs in Formula (Text):

CASE
    WHEN {isinactive} = 'T' THEN 'Inactive'
    ELSE 'Active'
END

This returns a number and belongs in Formula (Numeric):

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

Reference fields with braces

Saved-search fields are inserted with braces:

{entityid}

Joined fields include the join prefix:

{customer.email}

Enable Show Internal IDs in your NetSuite preferences to make script IDs easier to identify. Do not guess a field ID from its display label.

Use formulas in results

On the Results subtab, add the appropriate Formula field and enter the expression in its Formula column.

Replace a null value

NVL({salesrep.entityid}, 'Unassigned')

Join two text values

NVL({firstname}, '') ||
CASE WHEN {firstname} IS NOT NULL THEN ' ' ELSE '' END ||
NVL({lastname}, '')

Oracle-style concatenation uses two pipe characters: ||.

Calculate an extended value

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

Format a date for display

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

Because TO_CHAR returns text, use Formula (Text), not Formula (Date).

Use formulas in criteria

Formula criteria are useful when the condition needs an expression rather than a direct field comparison.

For example, to find records with no email value, add a Formula (Numeric) criterion with this formula:

CASE
    WHEN {email} IS NULL THEN 1
    ELSE 0
END

Then set the criterion operator to Equal to and its value to 1.

Prefer a normal field criterion when it can express the same condition. It is easier to understand and may perform better than calculating a formula for every candidate row.

Use formulas for highlighting

The Highlighting subtab accepts fields and formulas as conditions. NetSuite can apply background colour, text colour, bold styling or an icon to matching rows.

For example, a Formula (Numeric) condition can identify overdue transactions:

CASE
    WHEN {duedate} < SYSDATE AND NVL({amountremaining}, 0) > 0 THEN 1
    ELSE 0
END

Highlight where the formula equals 1.

For summary searches, the highlighting summary type must match the summary type configured on the Results subtab.

Handle nulls explicitly

Null is not an empty string and cannot be tested with = NULL.

Use:

{fieldid} IS NULL

or replace null with another value:

NVL({fieldid}, 'Not supplied')

Useful null-related functions include:

NVL(value, replacement)
NVL2(value, when_not_null, when_null)
COALESCE(value1, value2, ...)
NULLIF(value1, value2)

Make sure every possible branch returns a compatible type. Returning a number in one CASE branch and text in another commonly causes an invalid-expression error.

Summary formulas

Saved searches can group results and apply summary types such as Group, Sum, Count, Minimum, Maximum and Average.

To count active records by category, add the category as Group and this Formula (Numeric) result as Sum:

CASE
    WHEN {isinactive} = 'F' THEN 1
    ELSE 0
END

Do not mix aggregate and non-aggregate expressions in one formula unless every non-aggregate value is grouped appropriately. NetSuite documents this as a restriction of search formulas.

Formula (HTML) is deliberately restricted

Formula (Text) displays HTML markup as plain text. Rendered markup belongs in Formula (HTML), which requires the HTML Formulas in Search feature and the Create HTML Formulas in Search permission.

Keep HTML formulas simple and never place untrusted content into executable markup. NetSuite blocks <script> output in search formulas to reduce cross-site scripting risk.

Common formula errors

`ERROR: Invalid Expression`

Check for:

A missing END in a CASE expression.
Curly quotes copied from a document instead of straight quotes.
A field ID that is not available to this search type.
Mixed numeric, date and text return values.
An Oracle function that NetSuite saved searches do not support.

Formula works in Results but not Criteria

Confirm that the criterion’s formula type matches its return value and that the comparison value uses the same type.

Duplicate transaction rows

This is usually caused by transaction joins or main-line behaviour, not the formula itself. Simplify the search to native fields before changing the formula.

Summary search error

Once one result column uses a summary type, every displayed result must participate appropriately as Group, Sum, Count, Minimum, Maximum or Average.

A reliable workflow

Build the search with native fields first.
Add one formula at a time.
Start with a literal result such as 1 to confirm the selected formula type.
Add field references and functions progressively.
Preview before introducing summary types.
Add a custom label that explains the calculated value.
Test with empty, zero, negative and unexpected values.