NetSuiteFree generatorPDF, Excel, Word exportNetSuite

NetSuite Saved Search Formula Generator

NetSuite saved search formulas use Oracle style SQL expressions with field IDs in curly braces. This generator gives you the patterns people use most often so you can fill in your own field IDs and paste the result into a Formula column or a criteria row.

Fill in

Field ID in braces without the braces, e.g. duedate, trandate, shipdate.

Generated code
Download:

Overdue flag (Formula Text)

CASE WHEN {duedate} IS NOT NULL AND {today} - {duedate} > 30 THEN 'Overdue' ELSE 'OK' END

Days past date (Formula Numeric)

CASE WHEN {duedate} IS NULL THEN 0 ELSE {today} - {duedate} END

Date bucket for grouping (Formula Text)

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

Unit price with divide by zero guard (Formula Currency)

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

Null to zero (Formula Numeric)

NVL({amount}, 0)

Status label match (Formula Numeric, use as criteria = 1)

CASE WHEN {status} = 'Pending Billing' THEN 1 ELSE 0 END

Text contains check (Formula Numeric, use as criteria = 1)

CASE WHEN INSTR(LOWER({memo}), 'rush') > 0 THEN 1 ELSE 0 END

First 20 characters of text (Formula Text)

SUBSTR({memo}, 1, 20)

Replace the sample field IDs with the IDs from your record. Wrap each field in braces exactly as shown in the output. Pick the formula type that matches the result: Text, Numeric, Date, Currency or Percent. Test on a small saved search first, and note that field IDs differ between record types and joined fields.

Skip the formula syntax

Optional: see how plain-English questions can be turned into grounded NetSuite queries, with the query shown for review.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter the IDs of the fields you want to work with, a threshold in days and a date format for bucketing. The output lists ready formulas. In your saved search, add a Formula (Text), Formula (Numeric), Formula (Date), Formula (Currency) or Formula (Percent) column or criterion and paste the expression into the Formula box. The formula type must match the value returned, otherwise the column shows blanks or an error.

How the patterns work

CASE WHEN returns different values depending on a test. NVL replaces nulls so arithmetic does not return blank. NULLIF turns a zero denominator into null, which stops divide by zero errors. TO_CHAR with a format string converts a date into text for grouping by month, year or ISO week. INSTR and SUBSTR give basic text matching. The word today in braces returns the current date.

  • Use 1 or 0 results with a criterion of equals 1 to filter on a formula
  • Group by a TO_CHAR date bucket for monthly summaries
  • Prefer Formula (Numeric) when you will sum or sort the output

Reading and debugging results

If a column returns blank, check the formula type first, then field IDs, then whether a joined field needs its join prefix. Field IDs for joined records use the join name followed by a dot, such as customer.email. For long formulas, build them in steps in separate columns, then combine them once each step returns correct values.

Common mistakes

The most common errors are using the field label instead of its ID, forgetting single quotes around text literals, using double quotes, mixing text and numbers in one CASE, and summing a text formula. Another is using a formula in criteria on a large saved search, which can slow it down. Filter with standard criteria first and keep the formula for the last step.

Frequently Asked Questions

What language do NetSuite saved search formulas use?

They use Oracle style SQL expressions, with field IDs wrapped in curly braces. Functions such as CASE, NVL, NULLIF, DECODE, TO_CHAR, SUBSTR and INSTR are available. Which function set is available depends on the formula type you choose.

How do I calculate days overdue in a saved search?

Subtract the date field from the current date using the today keyword, wrapped in a CASE so null dates are handled. Use a Formula (Numeric) column and apply it as a criterion when you want to show only records over your threshold.

Why does my formula return blank?

Typically the formula type does not match the result, a field ID is wrong, or a nested null breaks the expression. Add NVL around nullable fields and check IDs from the record browser or the field's help popup.

Can I use a formula as a filter?

Yes. Add the formula as a criterion and compare it to a value. A reliable approach is returning 1 or 0 from a CASE and setting the criterion equals 1, which works for text and numeric tests.

If you build many of these for the same questions, Netray can put a natural-language layer over your NetSuite data that writes and shows the query for you, read-only by default.

Talk to an ExpertExplore More Free Tools

Related Tools

NetSuite

NetSuite Work Order Status Reference

Lookup table of NetSuite work order statuses and the transactions and records around them, with what each one means and what to check.

NetSuite

NetSuite CSV Import Template Guide

Generate a one-page CSV import plan and column specification document for a NetSuite record type, ready to hand to whoever prepares the data.

NetSuite

NetSuite Role Permission Matrix Worksheet

An editable worksheet that records which role holds which permission level on each record area and scores the access risk for review.

NetSuite

NetSuite Month End Close Checklist

A practical NetSuite period close checklist covering subledgers, inventory and manufacturing, accruals, reconciliations and period lock.

More NetSuite tools

See all
Back to All Tools