NetSuite Saved Search Formula Functions List
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.
| Function | Category | Formula field type | Syntax | Example | Result note |
|---|---|---|---|---|---|
NVL | null handling | Any | NVL(expr, default) | NVL({quantityshiprecv}, 0) | Default when the field is empty; needed before arithmetic. |
NVL2 | null handling | Text, Numeric | NVL2(expr, if_not_null, if_null) | NVL2({duedate}, 'Has due date', 'None') | Both results must be the same type. |
COALESCE | null handling | Any | COALESCE(a, b, ...) | COALESCE({custbody_ship_date}, {shipdate}, {trandate}) | First non-empty value. |
NULLIF | null handling | Numeric | NULLIF(a, b) | {amount} / NULLIF({quantity}, 0) | Avoids divide by zero errors. |
CASE WHEN | conditional | Any | CASE WHEN cond THEN a ELSE b END | CASE WHEN {status} = 'Billed' THEN {amount} ELSE 0 END | {status} returns the label text in formulas. |
DECODE | conditional | Any | DECODE(expr, s1, r1, ..., default) | DECODE({type}, 'Invoice', 1, 'Credit Memo', -1, 0) | Exact match only; use CASE for ranges. |
GREATEST | conditional | Numeric, Date | GREATEST(a, b, ...) | GREATEST({quantity} - {quantityshiprecv}, 0) | Returns NULL if any argument is NULL; wrap with NVL. |
LEAST | conditional | Numeric, Date | LEAST(a, b, ...) | LEAST({quantityavailable}, {quantity}) | Same NULL rule as GREATEST. |
ABS | numeric | Numeric, Currency | ABS(n) | ABS({amount}) | Absolute value; removes negative signs on credit lines. |
ROUND | numeric | Numeric, Currency, Date | ROUND(n [, d]) | ROUND({amount} * 0.075, 2) | Half rounds away from zero. |
TRUNC (number) | numeric | Numeric | TRUNC(n [, d]) | TRUNC({quantity} / 12) | Cuts decimals without rounding. |
TRUNC (date) | date | Date | TRUNC(date [, 'MM' | 'YYYY' | 'IW']) | TRUNC({trandate}, 'MM') | First day of the month; good for grouping. |
CEIL | numeric | Numeric | CEIL(n) | CEIL({quantity} / 24) | Rounds up: cartons needed. |
FLOOR | numeric | Numeric | FLOOR(n) | FLOOR(({today} - {trandate}) / 30) | Rounds down: age buckets in 30-day blocks. |
MOD | numeric | Numeric | MOD(n, m) | MOD({quantity}, 24) | Remainder: loose units after full cartons. |
SIGN | numeric | Numeric | SIGN(n) | SIGN({amount}) | -1, 0 or 1. |
POWER | numeric | Numeric | POWER(n, m) | POWER(1.05, 3) | n raised to m. |
SQRT | numeric | Numeric | SQRT(n) | SQRT({quantity}) | Square root; argument must not be negative. |
TO_CHAR (date) | conversion | Text | TO_CHAR(date, format) | TO_CHAR({trandate}, 'YYYY-MM') | Month label that sorts correctly. |
TO_CHAR (number) | conversion | Text | TO_CHAR(n, format) | TO_CHAR({amount}, '999,999,990.00') | Formatted text; no longer sums as a number. |
TO_DATE | conversion | Date | TO_DATE(text, format) | TO_DATE('2026-12-31', 'YYYY-MM-DD') | Always give the format mask. |
TO_NUMBER | conversion | Numeric | TO_NUMBER(text) | TO_NUMBER({custbody_ref_number}) | Fails the whole formula if any row holds non-numeric text. |
SUBSTR | text | Text | SUBSTR(text, start [, length]) | SUBSTR({item}, 1, 3) | Positions start at 1. |
INSTR | text | Numeric | INSTR(text, search) | INSTR({memo}, 'RUSH') | 0 when not found; case sensitive. |
LENGTH | text | Numeric | LENGTH(text) | LENGTH({tranid}) | NULL for empty fields, not 0. |
UPPER | text | Text | UPPER(text) | UPPER({otherrefnum}) | Use before comparing free text. |
LOWER | text | Text | LOWER(text) | LOWER({email}) | Clean email matching. |
INITCAP | text | Text | INITCAP(text) | INITCAP({shipcity}) | Capitalises each word. |
LTRIM / RTRIM / TRIM | text | Text | TRIM(text) | TRIM({otherrefnum}) | Removes leading and trailing spaces. |
REPLACE | text | Text | REPLACE(text, find, with) | REPLACE({item}, '-', '') | Replaces every occurrence. |
LPAD / RPAD | text | Text | LPAD(text, length, pad) | LPAD({line}, 3, '0') | Zero-pad line numbers for sorting. |
REGEXP_SUBSTR | text | Text | REGEXP_SUBSTR(text, pattern) | REGEXP_SUBSTR({memo}, '[0-9]+') | First match of the pattern; test in your account. |
REGEXP_REPLACE | text | Text | REGEXP_REPLACE(text, pattern, with) | REGEXP_REPLACE({phone}, '[^0-9]', '') | Strip all non-digits; test in your account. |
|| (concatenate) | text | Text | a || b | {tranid} || ' - ' || {entity} | Use || for more than two parts; CONCAT takes only two. |
ADD_MONTHS | date | Date | ADD_MONTHS(date, n) | ADD_MONTHS({trandate}, 12) | Warranty or contract end dates. |
MONTHS_BETWEEN | date | Numeric | MONTHS_BETWEEN(later, earlier) | ROUND(MONTHS_BETWEEN({today}, {datecreated}), 1) | Fractional months. |
LAST_DAY | date | Date | LAST_DAY(date) | LAST_DAY({trandate}) | Month-end date. |
NEXT_DAY | date | Date | NEXT_DAY(date, 'DAY') | NEXT_DAY({trandate}, 'MONDAY') | Next given weekday after the date. |
SYSDATE | date | Date, Numeric | SYSDATE | TRUNC(SYSDATE) - {trandate} | Server date and time; time zone can differ from the user. |
{today} | date | Date, Numeric | {today} | {today} - {duedate} | NetSuite placeholder for the current date; days overdue as a number. |
CURRENT_DATE | date | Date | CURRENT_DATE | CURRENT_DATE - {trandate} | Session date; prefer {today} for user-facing results. |
SUM / COUNT / MAX / MIN / AVG | aggregate/analytic | Numeric, Currency, Date (summary) | SUM(expr) with a summary type | COUNT(DISTINCT {internalid}) | Aggregates inside a formula need a summary type on the column, usually Maximum. |
MAX(...) KEEP (DENSE_RANK LAST ORDER BY ...) | aggregate/analytic | Text, Numeric, Date (summary) | MAX(value) KEEP (DENSE_RANK LAST ORDER BY sort_field) | MAX({rate}) KEEP (DENSE_RANK LAST ORDER BY {trandate}) | Value from the latest row per group, such as last price paid; set summary type Maximum. |
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.
Formula throwing Invalid Expression?
Send us the formula and the search type and we will return a corrected version with the reason.
Optional. The tool and every export work without this. No spam.
How to use it
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}.
- 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
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
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.
- Not equal: != or <>
- Absolute value: ABS({amount})
- Text literals use single quotes
Where this lives in NetSuite
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.
Frequently Asked Questions
How do I get absolute value in a NetSuite formula?
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?
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?
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?
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.
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.
Related Tools
NetSuite Saved Search Formula Generator
Fill in your field IDs and thresholds and get copy-ready formula snippets for NetSuite saved search results and criteria.
NetSuiteNetSuite Saved Search Summary Types and Summary Criteria Reference
What each Summary Type returns, plus the formula patterns that do count distinct, conditional sums, latest value per group and HAVING-style filters.
NetSuiteNetSuite Transaction Status Codes Reference (SuiteQL and Search)
Status letter, search filter value and label for sales orders, invoices, purchase orders, fulfillments, bills, RMAs, transfer orders and more.
NetSuiteNetSuite SuiteQL BUILTIN Functions Reference
The ten BUILTIN functions Oracle documents for SuiteQL, with exact parameter order and one-line examples, plus the Oracle SQL functions you will use beside them.
More NetSuite tools
See all- NetSuite CSV Import Template Guide
- NetSuite Role Permission Matrix Worksheet
- NetSuite Inventory Costing Method Reference
- NetSuite Month End Close Checklist
- NetSuite License Cost Estimator
- NetSuite User Access Request Form Template
- NetSuite Custom Record Design Spec Template
- NetSuite SuiteScript Deployment Handover Template
- NetSuite Sandbox and Release Change Log Worksheet
Keep exploring
Free tools
- NetSuite Saved Search Formula GeneratorFill in your field IDs and thresholds and get copy-ready formula snippets for NetSuite saved search results and criteria.
- NetSuite Saved Search Summary Types and Summary Criteria ReferenceWhat each Summary Type returns, plus the formula patterns that do count distinct, conditional sums, latest value per group and HAVING-style filters.
- NetSuite Saved Search vs Report vs SuiteQL vs Workbook AssessmentScore your reporting need on joins, volume, delivery, users and integration to see whether a saved search, workbook, SuiteQL or SuiteAnalytics Connect fits best.
ERP fixes and how-tos
- How to use formula fields in a NetSuite saved searchHow to build formula (text), formula (numeric) and formula (date) fields in a NetSuite saved search, with CASE WHEN, DECODE and NVL examples that actually work.
- Fix NetSuite Saved Search Invalid Expression and ORA-00904Saved search shows Invalid Expression or ORA-00904? The formula uses a field id that does not exist in that join or a type mismatch. Here is how to fix it.
- How to write and run SuiteQL queries in NetSuiteRun SuiteQL from Analytics > SuiteQL Query Tool or via N/query in SuiteScript. Syntax basics, joins, pagination and real examples for NetSuite reporting.