# NetSuite Saved Search Formula Functions List

> Source: https://netray.co/tools/netsuite-saved-search-formula-functions-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Every common SQL function for Formula (Text), (Numeric), (Date), (Currency) and (Percent) fields, with syntax and an example using real NetSuite field ids.

## Category

netsuite

## Erp

NetSuite

## Audience

- NetSuite administrator
- NetSuite developer
- Finance analyst

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### Function

**Key:** function

**Type:** text

#### Category

**Key:** category

**Type:** text

#### Formula field type

**Key:** formula_type

**Type:** text

#### Syntax

**Key:** syntax

**Type:** text

#### Example

**Key:** example

**Type:** text

#### Result note

**Key:** result_note

**Type:** text

**Rows:**

**Function:** NVL

**Category:** null handling

**Formula type:** Any

**Syntax:** NVL(expr, default)

**Example:** NVL({quantityshiprecv}, 0)

**Result note:** Default when the field is empty; needed before arithmetic.

**Function:** NVL2

**Category:** null handling

**Formula type:** Text, Numeric

**Syntax:** NVL2(expr, if_not_null, if_null)

**Example:** NVL2({duedate}, 'Has due date', 'None')

**Result note:** Both results must be the same type.

**Function:** COALESCE

**Category:** null handling

**Formula type:** Any

**Syntax:** COALESCE(a, b, ...)

**Example:** COALESCE({custbody_ship_date}, {shipdate}, {trandate})

**Result note:** First non-empty value.

**Function:** NULLIF

**Category:** null handling

**Formula type:** Numeric

**Syntax:** NULLIF(a, b)

**Example:** {amount} / NULLIF({quantity}, 0)

**Result note:** Avoids divide by zero errors.

**Function:** CASE WHEN

**Category:** conditional

**Formula type:** Any

**Syntax:** CASE WHEN cond THEN a ELSE b END

**Example:** CASE WHEN {status} = 'Billed' THEN {amount} ELSE 0 END

**Result note:** {status} returns the label text in formulas.

**Function:** DECODE

**Category:** conditional

**Formula type:** Any

**Syntax:** DECODE(expr, s1, r1, ..., default)

**Example:** DECODE({type}, 'Invoice', 1, 'Credit Memo', -1, 0)

**Result note:** Exact match only; use CASE for ranges.

**Function:** GREATEST

**Category:** conditional

**Formula type:** Numeric, Date

**Syntax:** GREATEST(a, b, ...)

**Example:** GREATEST({quantity} - {quantityshiprecv}, 0)

**Result note:** Returns NULL if any argument is NULL; wrap with NVL.

**Function:** LEAST

**Category:** conditional

**Formula type:** Numeric, Date

**Syntax:** LEAST(a, b, ...)

**Example:** LEAST({quantityavailable}, {quantity})

**Result note:** Same NULL rule as GREATEST.

**Function:** ABS

**Category:** numeric

**Formula type:** Numeric, Currency

**Syntax:** ABS(n)

**Example:** ABS({amount})

**Result note:** Absolute value; removes negative signs on credit lines.

**Function:** ROUND

**Category:** numeric

**Formula type:** Numeric, Currency, Date

**Syntax:** ROUND(n [, d])

**Example:** ROUND({amount} * 0.075, 2)

**Result note:** Half rounds away from zero.

**Function:** TRUNC (number)

**Category:** numeric

**Formula type:** Numeric

**Syntax:** TRUNC(n [, d])

**Example:** TRUNC({quantity} / 12)

**Result note:** Cuts decimals without rounding.

**Function:** TRUNC (date)

**Category:** date

**Formula type:** Date

**Syntax:** TRUNC(date [, 'MM' | 'YYYY' | 'IW'])

**Example:** TRUNC({trandate}, 'MM')

**Result note:** First day of the month; good for grouping.

**Function:** CEIL

**Category:** numeric

**Formula type:** Numeric

**Syntax:** CEIL(n)

**Example:** CEIL({quantity} / 24)

**Result note:** Rounds up: cartons needed.

**Function:** FLOOR

**Category:** numeric

**Formula type:** Numeric

**Syntax:** FLOOR(n)

**Example:** FLOOR(({today} - {trandate}) / 30)

**Result note:** Rounds down: age buckets in 30-day blocks.

**Function:** MOD

**Category:** numeric

**Formula type:** Numeric

**Syntax:** MOD(n, m)

**Example:** MOD({quantity}, 24)

**Result note:** Remainder: loose units after full cartons.

**Function:** SIGN

**Category:** numeric

**Formula type:** Numeric

**Syntax:** SIGN(n)

**Example:** SIGN({amount})

**Result note:** -1, 0 or 1.

**Function:** POWER

**Category:** numeric

**Formula type:** Numeric

**Syntax:** POWER(n, m)

**Example:** POWER(1.05, 3)

**Result note:** n raised to m.

**Function:** SQRT

**Category:** numeric

**Formula type:** Numeric

**Syntax:** SQRT(n)

**Example:** SQRT({quantity})

**Result note:** Square root; argument must not be negative.

**Function:** TO_CHAR (date)

**Category:** conversion

**Formula type:** Text

**Syntax:** TO_CHAR(date, format)

**Example:** TO_CHAR({trandate}, 'YYYY-MM')

**Result note:** Month label that sorts correctly.

**Function:** TO_CHAR (number)

**Category:** conversion

**Formula type:** Text

**Syntax:** TO_CHAR(n, format)

**Example:** TO_CHAR({amount}, '999,999,990.00')

**Result note:** Formatted text; no longer sums as a number.

**Function:** TO_DATE

**Category:** conversion

**Formula type:** Date

**Syntax:** TO_DATE(text, format)

**Example:** TO_DATE('2026-12-31', 'YYYY-MM-DD')

**Result note:** Always give the format mask.

**Function:** TO_NUMBER

**Category:** conversion

**Formula type:** Numeric

**Syntax:** TO_NUMBER(text)

**Example:** TO_NUMBER({custbody_ref_number})

**Result note:** Fails the whole formula if any row holds non-numeric text.

**Function:** SUBSTR

**Category:** text

**Formula type:** Text

**Syntax:** SUBSTR(text, start [, length])

**Example:** SUBSTR({item}, 1, 3)

**Result note:** Positions start at 1.

**Function:** INSTR

**Category:** text

**Formula type:** Numeric

**Syntax:** INSTR(text, search)

**Example:** INSTR({memo}, 'RUSH')

**Result note:** 0 when not found; case sensitive.

**Function:** LENGTH

**Category:** text

**Formula type:** Numeric

**Syntax:** LENGTH(text)

**Example:** LENGTH({tranid})

**Result note:** NULL for empty fields, not 0.

**Function:** UPPER

**Category:** text

**Formula type:** Text

**Syntax:** UPPER(text)

**Example:** UPPER({otherrefnum})

**Result note:** Use before comparing free text.

**Function:** LOWER

**Category:** text

**Formula type:** Text

**Syntax:** LOWER(text)

**Example:** LOWER({email})

**Result note:** Clean email matching.

**Function:** INITCAP

**Category:** text

**Formula type:** Text

**Syntax:** INITCAP(text)

**Example:** INITCAP({shipcity})

**Result note:** Capitalises each word.

**Function:** LTRIM / RTRIM / TRIM

**Category:** text

**Formula type:** Text

**Syntax:** TRIM(text)

**Example:** TRIM({otherrefnum})

**Result note:** Removes leading and trailing spaces.

**Function:** REPLACE

**Category:** text

**Formula type:** Text

**Syntax:** REPLACE(text, find, with)

**Example:** REPLACE({item}, '-', '')

**Result note:** Replaces every occurrence.

**Function:** LPAD / RPAD

**Category:** text

**Formula type:** Text

**Syntax:** LPAD(text, length, pad)

**Example:** LPAD({line}, 3, '0')

**Result note:** Zero-pad line numbers for sorting.

**Function:** REGEXP_SUBSTR

**Category:** text

**Formula type:** Text

**Syntax:** REGEXP_SUBSTR(text, pattern)

**Example:** REGEXP_SUBSTR({memo}, '[0-9]+')

**Result note:** First match of the pattern; test in your account.

**Function:** REGEXP_REPLACE

**Category:** text

**Formula type:** Text

**Syntax:** REGEXP_REPLACE(text, pattern, with)

**Example:** REGEXP_REPLACE({phone}, '[^0-9]', '')

**Result note:** Strip all non-digits; test in your account.

**Function:** || (concatenate)

**Category:** text

**Formula type:** Text

**Syntax:** a || b

**Example:** {tranid} || ' - ' || {entity}

**Result note:** Use || for more than two parts; CONCAT takes only two.

**Function:** ADD_MONTHS

**Category:** date

**Formula type:** Date

**Syntax:** ADD_MONTHS(date, n)

**Example:** ADD_MONTHS({trandate}, 12)

**Result note:** Warranty or contract end dates.

**Function:** MONTHS_BETWEEN

**Category:** date

**Formula type:** Numeric

**Syntax:** MONTHS_BETWEEN(later, earlier)

**Example:** ROUND(MONTHS_BETWEEN({today}, {datecreated}), 1)

**Result note:** Fractional months.

**Function:** LAST_DAY

**Category:** date

**Formula type:** Date

**Syntax:** LAST_DAY(date)

**Example:** LAST_DAY({trandate})

**Result note:** Month-end date.

**Function:** NEXT_DAY

**Category:** date

**Formula type:** Date

**Syntax:** NEXT_DAY(date, 'DAY')

**Example:** NEXT_DAY({trandate}, 'MONDAY')

**Result note:** Next given weekday after the date.

**Function:** SYSDATE

**Category:** date

**Formula type:** Date, Numeric

**Syntax:** SYSDATE

**Example:** TRUNC(SYSDATE) - {trandate}

**Result note:** Server date and time; time zone can differ from the user.

**Function:** {today}

**Category:** date

**Formula type:** Date, Numeric

**Syntax:** {today}

**Example:** {today} - {duedate}

**Result note:** NetSuite placeholder for the current date; days overdue as a number.

**Function:** CURRENT_DATE

**Category:** date

**Formula type:** Date

**Syntax:** CURRENT_DATE

**Example:** CURRENT_DATE - {trandate}

**Result note:** Session date; prefer {today} for user-facing results.

**Function:** SUM / COUNT / MAX / MIN / AVG

**Category:** aggregate/analytic

**Formula type:** Numeric, Currency, Date (summary)

**Syntax:** SUM(expr) with a summary type

**Example:** COUNT(DISTINCT {internalid})

**Result note:** Aggregates inside a formula need a summary type on the column, usually Maximum.

**Function:** MAX(...) KEEP (DENSE_RANK LAST ORDER BY ...)

**Category:** aggregate/analytic

**Formula type:** Text, Numeric, Date (summary)

**Syntax:** MAX(value) KEEP (DENSE_RANK LAST ORDER BY sort_field)

**Example:** MAX({rate}) KEEP (DENSE_RANK LAST ORDER BY {trandate})

**Result note:** Value from the latest row per group, such as last price paid; set summary type Maximum.

**Notes:** Saved search formulas are Oracle SQL expressions with NetSuite field ids in braces. Not-equal can be written != or <>. Comparisons with an empty field return NULL, so wrap fields in NVL before comparing. Functions marked test in your account (REGEXP_*) and the KEEP DENSE_RANK pattern are widely used but check them in a sandbox search first; an unsupported function shows an Invalid Expression error.

## Content

**Intro:** A lookup of the Oracle SQL functions that work in NetSuite saved search Formula fields, grouped by job and shown with real field ids. Use it when you know what you want to calculate and need the exact syntax; the companion formula generator builds full formulas for you.

**Sections:**

#### How to use it

**Body:** Pick the Formula field type first, because it decides how NetSuite treats the result: Formula (Text) for labels and concatenation, Formula (Numeric) for counts and ratios, Formula (Currency) for amounts that should show currency formatting, Formula (Date) for dates and Formula (Percent) for ratios shown as percentages. Then search this table by category or name and copy the example, swapping in your field ids. Field ids go inside braces, such as {amount}, {trandate} or {custbody_ship_via}.

**Bullets:**

- Results tab: add Formula (Type), paste the formula
- Criteria tab: Formula (Type) with a condition, such as is greater than 0
- Use the Formula builder pop-up to insert field ids without typos

#### Worked example: days overdue bucket

**Body:** On an open invoice search, add a Formula (Text) column: CASE WHEN {today} - {duedate} <= 0 THEN 'Current' WHEN {today} - {duedate} <= 30 THEN '1-30' WHEN {today} - {duedate} <= 60 THEN '31-60' ELSE '60+' END. Subtracting two dates returns the number of days, so no DATEDIFF function is needed (Oracle has none). Add a Formula (Numeric) column with NVL({today} - {duedate}, 0) if you also want the raw days for sorting.

#### Common mistakes

**Body:** Comparing {status} with a letter: in formulas {status} returns the label, so write 'Billed' not 'G'. Mixing types inside CASE, such as returning a number in one branch and text in another, which raises an error. Using CONCAT with three arguments: Oracle CONCAT takes two, use || instead. Forgetting NULLs: NULL plus anything is NULL, and NULL = NULL is not true. Using TO_CHAR on an amount and then summarising it: it is text now, so Sum no longer works.

**Bullets:**

- Not equal: != or <>
- Absolute value: ABS({amount})
- Text literals use single quotes

#### Where this lives in NetSuite

**Body:** Formula fields appear in saved searches under Reports > Saved Searches > All Saved Searches > New, on the Criteria and Results subtabs. Workflow conditions and Set Field Value formulas on server triggers use the same SQL syntax; client triggers use SuiteScript formulas instead. Help topic SQL Expressions lists the Oracle function families NetSuite supports in formulas.

**Cta:** If people keep asking you for one more saved search, we can set up a private AI layer over NetSuite where they ask in plain English and see the logic behind each answer.

**Faq:**

#### How do I get absolute value in a NetSuite formula?

**Answer:** Use ABS(), for example ABS({amount}) in a Formula (Currency) or Formula (Numeric) column. It is useful on credit memos and other lines where NetSuite stores a negative amount. In SuiteScript, use Math.abs() in JavaScript instead.

#### How does NVL work in a saved search formula?

**Answer:** NVL({field}, default) returns the default when the field is empty. Use it before arithmetic or comparisons, for example NVL({quantityshiprecv}, 0), because NULL in any calculation makes the whole result NULL and the row looks blank.

#### How do I concatenate fields in a NetSuite formula?

**Answer:** Use || in a Formula (Text) field: {tranid} || ' - ' || {entity}. CONCAT also works but takes only two arguments, so nesting gets messy. Wrap fields in NVL if any part can be empty.

#### Can I use DECODE in a saved search?

**Answer:** Yes. DECODE({type}, 'Invoice', 1, 'Credit Memo', -1, 0) maps exact values. It cannot test ranges or conditions, so switch to CASE WHEN for anything beyond exact matches. Both work in criteria and results formulas.

## Search terms

netsuite saved search formula function list, netsuite formula functions, netsuite formula nvl, netsuite formula text functions, netsuite saved search decode, netsuite formula concat, netsuite saved search case when, netsuite formula absolute value

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json