skcah
Writing / Netsuite

NetSuite Saved Search Formula Functions Reference

A practical reference for supported NetSuite saved-search functions, with copyable examples for text, numbers, dates, nulls, conditions and summary results.

Ryan Sheehy9 minute read

NetSuite saved-search formulas use Oracle SQL syntax, but they do not support every Oracle function. The safest reference is NetSuite’s own SQL Expressions documentation, which lists the functions accepted in search formulas and custom formula fields.

This guide groups the functions you are most likely to need and gives each one a saved-search example you can adapt.

Quick function finder

Goal Function or syntax Example
Replace a null NVL NVL({amount}, 0)
Return one value when populated and another when null NVL2 NVL2({email}, 'Yes', 'No')
Use the first available value COALESCE COALESCE({mobilephone}, {phone}, 'None')
Map exact values DECODE DECODE({isinactive}, 'T', 'Inactive', 'Active')
Apply several conditions CASE CASE WHEN {amount} > 0 THEN 'Positive' ELSE 'Other' END
Convert a date or number to text TO_CHAR TO_CHAR({trandate}, 'YYYY-MM')
Convert text to a number TO_NUMBER TO_NUMBER({custbody_number_text})
Round a number ROUND ROUND({amount}, 2)
Remove the decimal portion TRUNC TRUNC({amount})
Find text length LENGTH LENGTH({entityid})
Find text inside text INSTR INSTR({email}, '@')
Extract part of text SUBSTR SUBSTR({entityid}, 1, 10)
Change letter case UPPER or LOWER LOWER({email})
Remove surrounding spaces TRIM TRIM({entityid})
Work with the current date SYSDATE, {today} or {now} TRUNC(SYSDATE) - TRUNC({trandate})
Find a month boundary LAST_DAY LAST_DAY({trandate})
Count matching rows SUM with CASE SUM(CASE WHEN condition THEN 1 ELSE 0 END)

Choose the Formula field type from the value returned by the whole expression. For example, TO_CHAR returns text even when its input is a date.

NVL: replace a null

NVL({amountremaining}, 0)

The replacement must be compatible with the original value. Use a number for a numeric field and text for a text field:

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

NVL2: branch on whether a value exists

NVL2(value, result_when_not_null, result_when_null) is useful for labels and flags:

NVL2({email}, 'Email supplied', 'Missing email')

Use Formula (Text) for this example.

COALESCE: use the first non-null value

COALESCE({mobilephone}, {phone}, {altphone}, 'No phone')

This is more readable than nesting several NVL calls. All candidate results should have compatible data types.

NULLIF: turn a matching value into null

NULLIF({quantity}, 0)

This returns null when quantity is zero. One practical use is protecting a division from a zero denominator:

{amount} / NULLIF({quantity}, 0)

Conditional functions

CASE: ranges and multiple conditions

CASE
    WHEN {amountremaining} IS NULL THEN 'Not applicable'
    WHEN {amountremaining} <= 0 THEN 'Paid'
    WHEN {duedate} < SYSDATE THEN 'Overdue'
    ELSE 'Open'
END

CASE is the best choice for ranges, AND/OR conditions and several ordered branches. See the complete NetSuite CASE WHEN guide for criteria and summary examples.

DECODE: map exact values

DECODE(
    {isinactive},
    'T', 'Inactive',
    'F', 'Active',
    'Unknown'
)

DECODE compares one expression with a sequence of exact values. The final argument is the default. Prefer CASE when the condition is more complicated than equality.

Text functions

Combine text with ||

Concatenation is an operator rather than a function:

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

UPPER, LOWER and INITCAP

LOWER(TRIM({email}))
UPPER({entityid})
INITCAP({companyname})

These are useful for display and normalised comparisons. A formula criterion using a function may be less efficient than an equivalent native criterion, so use the normal field filters when they express the same rule.

LENGTH: count characters

LENGTH(TRIM({entityid}))

Use Formula (Numeric). To identify values longer than 20 characters, use the formula in Criteria and select greater than 20.

SUBSTR: extract characters

SUBSTR({entityid}, 1, 10)

Oracle positions start at 1, not 0. A negative starting position counts backwards from the end:

SUBSTR({email}, -4)

INSTR: find a substring

INSTR({email}, '@')

This returns the position of the first match, or 0 when it is not found. A simple email-quality flag is:

CASE
    WHEN INSTR({email}, '@') > 1 THEN 0
    ELSE 1
END

REPLACE: replace matching text

REPLACE({phone}, ' ', '')

You can nest calls for light cleanup:

REPLACE(REPLACE({phone}, ' ', ''), '-', '')

Avoid turning a formula into a full data-cleaning pipeline. Correct reusable values on the record when possible.

Numeric functions

ROUND: control decimal precision

ROUND(NVL({amount}, 0), 2)

Use 0 decimal places for the nearest whole number and a negative precision to round to tens or hundreds:

ROUND({amount}, -2)

TRUNC, FLOOR and CEIL

These functions are not interchangeable for negative numbers:

TRUNC({quantity}, 0)

TRUNC removes decimal places. FLOOR returns the greatest integer at or below the value, while CEIL returns the smallest integer at or above it.

ABS: remove the sign

ABS({amount})

This is useful when magnitude matters but debit or credit direction does not.

Protect calculations from null and zero

ROUND(
    NVL({grossprofit}, 0) / NULLIF({amount}, 0) * 100,
    2
)

Use Formula (Percent) only after checking how NetSuite formats the returned ratio. Depending on the required display, you may need to return a decimal ratio rather than multiplying by 100.

Date functions

Current date and time

NetSuite provides formula tags such as {today} and {now} as well as supported SQL date expressions such as SYSDATE. Be deliberate about whether the calculation needs a date or a timestamp.

To calculate whole calendar days:

TRUNC(SYSDATE) - TRUNC({trandate})

Use Formula (Numeric).

TO_CHAR: format a date as text

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

Other useful format models include:

TO_CHAR({trandate}, 'YYYY-MM-DD')
TO_CHAR({datecreated}, 'YYYY-MM-DD HH24:MI')

Because the result is text, select Formula (Text).

TRUNC: remove the time or find a period boundary

TRUNC({datecreated})
TRUNC({trandate}, 'MM')

The second expression returns the first day of the month containing the transaction date.

LAST_DAY, ADD_MONTHS and MONTHS_BETWEEN

LAST_DAY({trandate})
ADD_MONTHS({trandate}, 3)
MONTHS_BETWEEN(SYSDATE, {datecreated})

These cover month-end reporting, renewal dates and approximate age calculations without hard-coding a number of days per month.

Conversion functions

TO_CHAR

Convert a number to formatted text:

TO_CHAR({amount}, 'FM999999990.00')

Use Formula (Text). Once converted, the result sorts as text rather than as a number.

TO_NUMBER

TO_NUMBER({custbody_number_text})

Only use this when every populated value can be converted. One malformed value can cause the search to fail.

TO_DATE

TO_DATE('2026-07-20', 'YYYY-MM-DD')

Prefer actual date fields and relative-date filters over converting hard-coded text where possible.

Aggregate and analytic functions

For a summary search, it is normally clearer to choose Sum, Count, Minimum, Maximum or Average in the Summary Type column rather than embedding the aggregate in the formula.

For example, add this as Formula (Numeric):

CASE WHEN NVL({amountremaining}, 0) > 0 THEN 1 ELSE 0 END

Then choose Sum to count open rows within each grouped result.

NetSuite also documents analytic functions such as RANK and DENSE_RANK, including OVER (PARTITION BY ... ORDER BY ...) forms. Test these carefully on the exact search type and dataset; joins and transaction lines can change what constitutes a row.

Do not combine aggregate and non-aggregate functions improperly in one formula. NetSuite explicitly restricts formulas that mix the two without the necessary grouping.

Saved-search variables

Useful contextual variables include:

Variable Meaning
{today} or {now} Current date or time in the user’s context
{me}, {user} or {user.id} Current user’s internal ID
{userrole} or {userrole.id} Current role ID
{user.department} Current user’s department
{user.location} Current user’s location
{user.subsidiary} Current user’s subsidiary
{usercurrency} Current user’s currency

These variables make one saved search adapt to its viewer. Remember that user-dependent results may make troubleshooting and scheduled output less obvious.

Limits and common errors

The formula is too long

NetSuite documents a 1,000-character limit per formula expression. A formula may preview while editing and then fail when the search runs. If a formula approaches the limit, simplify it, split the output into separate columns or calculate a reusable value elsewhere.

ERROR: Invalid Expression

Check for:

  • An unsupported function copied from generic Oracle, SQL Server or SuiteQL documentation.
  • A misspelled or unavailable field ID.
  • Smart quotes instead of straight SQL quotes.
  • An unfinished CASE expression.
  • Incompatible result types across CASE, NVL, NVL2, COALESCE or DECODE.
  • Text being converted to a number or date when some rows contain invalid values.
  • Aggregate and non-aggregate expressions being mixed incorrectly.

SuiteQL and saved-search formulas are different query environments. In particular, SuiteQL BUILTIN.* functions are not a saved-search function catalogue. Validate saved-search functions against NetSuite’s SQL Expressions page or the formula editor’s Function list.

The output sorts incorrectly

TO_CHAR converts values to text. Text such as 1, 10, 2 sorts lexically, not numerically. Keep the result numeric or date-based when its natural order matters.

A reliable way to test a function

  1. Start with a simple non-summary search and native result fields.
  2. Add one formula column with a representative field reference.
  3. Choose the Formula type from the function’s return value.
  4. Test nulls, zeros, negative values and malformed text.
  5. Add joins, criteria and summary types one at a time.
  6. Verify results under any roles, languages or subsidiaries that will run the search.