NetSuite Saved Search Formulas: Advanced Techniques

Saved Search formulas unlock advanced reporting capabilities in NetSuite. Using SQL-like syntax, you can create calculated fields, conditional logic, and complex data transformations directly in your searches.

Formula Basics

NetSuite formulas use Oracle SQL syntax. The most common formula types are:

Formula Type	Use Case	Example
Formula (Text)	String manipulation	Concatenation, CASE for text
Formula (Numeric)	Calculations	Math, aggregations
Formula (Date)	Date operations	Date math, formatting
Formula (Currency)	Money calculations	Sums, conversions
Formula (Percent)	Percentages	Ratios, margins

CASE Statements

CASE statements are essential for conditional logic:

Simple CASE

CASE {status}
  WHEN 'Pending Fulfillment' THEN 'Awaiting Ship'
  WHEN 'Pending Billing' THEN 'Ready to Invoice'
  WHEN 'Billed' THEN 'Complete'
  ELSE 'Other'
END

Searched CASE (with conditions)

CASE
  WHEN {amount} > 10000 THEN 'Large Order'
  WHEN {amount} > 1000 THEN 'Medium Order'
  WHEN {amount} > 0 THEN 'Small Order'
  ELSE 'Zero Value'
END

Nested CASE

CASE {type}
  WHEN 'Sales Order' THEN
    CASE
      WHEN {status} = 'Pending Fulfillment' THEN 'SO - Awaiting'
      WHEN {status} = 'Billed' THEN 'SO - Complete'
      ELSE 'SO - Other'
    END
  WHEN 'Invoice' THEN
    CASE
      WHEN {status} = 'Open' THEN 'INV - Unpaid'
      ELSE 'INV - Paid'
    END
  ELSE 'Other Transaction'
END

Date Formulas

Days Between Dates

-- Days since order was created
TRUNC(SYSDATE) - TRUNC({datecreated})
 
-- Days until due date
TRUNC({duedate}) - TRUNC(SYSDATE)

Date Aging Buckets

CASE
  WHEN TRUNC(SYSDATE) - TRUNC({duedate}) <= 0 THEN 'Current'
  WHEN TRUNC(SYSDATE) - TRUNC({duedate}) <= 30 THEN '1-30 Days'
  WHEN TRUNC(SYSDATE) - TRUNC({duedate}) <= 60 THEN '31-60 Days'
  WHEN TRUNC(SYSDATE) - TRUNC({duedate}) <= 90 THEN '61-90 Days'
  ELSE 'Over 90 Days'
END

Extract Date Parts

-- Year
TO_CHAR({trandate}, 'YYYY')
 
-- Month name
TO_CHAR({trandate}, 'Month')
 
-- Quarter
'Q' || TO_CHAR({trandate}, 'Q') || ' ' || TO_CHAR({trandate}, 'YYYY')
 
-- Week number
TO_CHAR({trandate}, 'IW')
 
-- Day of week
TO_CHAR({trandate}, 'Day')

First/Last Day of Month

-- First day of current month
TRUNC({trandate}, 'MONTH')
 
-- Last day of month
LAST_DAY({trandate})
 
-- First day of year
TRUNC({trandate}, 'YEAR')

Date Arithmetic

-- Add 30 days
{trandate} + 30
 
-- Add 3 months
ADD_MONTHS({trandate}, 3)
 
-- Next Monday
NEXT_DAY({trandate}, 'MONDAY')

Text Formulas

Concatenation

-- Combine fields
{firstname} || ' ' || {lastname}
 
-- With conditional
{firstname} || ' ' || NVL({middlename} || ' ', '') || {lastname}

String Functions

-- Uppercase
UPPER({companyname})
 
-- Substring (first 10 chars)
SUBSTR({memo}, 1, 10)
 
-- Replace text
REPLACE({description}, 'Old Text', 'New Text')
 
-- Trim whitespace
TRIM({companyname})
 
-- Length
LENGTH({ponumber})

Pattern Matching

-- Contains text (returns 1 or 0)
CASE WHEN {memo} LIKE '%URGENT%' THEN 'Urgent' ELSE 'Normal' END
 
-- Starts with
CASE WHEN {tranid} LIKE 'SO%' THEN 'Sales Order' ELSE 'Other' END
 
-- Regular expression (advanced)
CASE WHEN REGEXP_LIKE({email}, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
  THEN 'Valid Email'
  ELSE 'Invalid Email'
END

Numeric Formulas

Basic Math

-- Margin calculation
({amount} - {cost}) / NULLIF({amount}, 0) * 100
 
-- Quantity * Rate
{quantity} * {rate}
 
-- With rounding
ROUND(({amount} - {cost}) / NULLIF({amount}, 0) * 100, 2)

Aggregate Functions

In summary searches, use these in Formula columns:

-- Sum
SUM({amount})
 
-- Average
AVG({amount})
 
-- Count distinct
COUNT(DISTINCT {entity})
 
-- Max/Min
MAX({trandate})
MIN({trandate})

Handling NULLs

-- Replace NULL with 0
NVL({quantity}, 0)
 
-- Replace NULL with specific value
NVL({memo}, 'No memo')
 
-- COALESCE (first non-null)
COALESCE({altphone}, {phone}, 'No phone')
 
-- Avoid division by zero
{amount} / NULLIF({quantity}, 0)

Practical Examples

Customer Health Score

CASE
  WHEN {customer.balance} = 0 AND {customer.overduebalance} = 0 THEN 'Excellent'
  WHEN {customer.overduebalance} > 0 AND
       {customer.overduebalance} / NULLIF({customer.balance}, 0) < 0.1 THEN 'Good'
  WHEN {customer.overduebalance} / NULLIF({customer.balance}, 0) < 0.25 THEN 'At Risk'
  ELSE 'Critical'
END

Order Priority Flag

CASE
  WHEN {shipcomplete} = 'T' AND {amount} > 5000 THEN '1-HIGH'
  WHEN {custbody_rush_order} = 'T' THEN '1-HIGH'
  WHEN {customer.isperson} = 'F' AND {amount} > 2000 THEN '2-MEDIUM'
  ELSE '3-NORMAL'
END

Inventory Status

CASE
  WHEN {quantityavailable} <= 0 THEN 'Out of Stock'
  WHEN {quantityavailable} < {reorderpoint} THEN 'Reorder Now'
  WHEN {quantityavailable} < ({reorderpoint} * 1.5) THEN 'Running Low'
  ELSE 'In Stock'
END

Sales Rep Commission Tier

CASE
  WHEN SUM({amount}) >= 100000 THEN 'Platinum (15%)'
  WHEN SUM({amount}) >= 50000 THEN 'Gold (12%)'
  WHEN SUM({amount}) >= 25000 THEN 'Silver (10%)'
  ELSE 'Bronze (8%)'
END

Fulfillment SLA Status

CASE
  WHEN {shipdate} IS NULL AND TRUNC(SYSDATE) - TRUNC({trandate}) > 3 THEN 'SLA Breach'
  WHEN {shipdate} IS NULL AND TRUNC(SYSDATE) - TRUNC({trandate}) > 2 THEN 'At Risk'
  WHEN {shipdate} IS NOT NULL AND TRUNC({shipdate}) - TRUNC({trandate}) <= 2 THEN 'Met SLA'
  WHEN {shipdate} IS NOT NULL THEN 'Late Shipment'
  ELSE 'On Track'
END

Revenue Recognition Period

TO_CHAR({revenuerecognitionrule.revrecstartdate}, 'Mon YYYY') ||
' - ' ||
TO_CHAR({revenuerecognitionrule.revrecenddate}, 'Mon YYYY')

Using Formulas in Criteria

You can also use formulas in search criteria (filters):

-- Filter: Orders over 30 days old
TRUNC(SYSDATE) - TRUNC({trandate}) > 30
 
-- Filter: Specific month
TO_CHAR({trandate}, 'YYYY-MM') = '2026-01'
 
-- Filter: Weekday orders only
TO_CHAR({trandate}, 'DY') NOT IN ('SAT', 'SUN')

Performance Tips

Avoid formulas on large datasets - They compute per row
Use filters to limit rows first - Then apply formulas
Index-friendly criteria - Put direct field filters before formula filters
Pre-calculate when possible - Store values in custom fields if used frequently
Test with limited results - Use "Maximum Results" while developing

Common Errors and Solutions

Error	Cause	Solution
"Invalid Expression"	Syntax error	Check quotes, parentheses, field IDs
"ORA-00936: missing expression"	Incomplete statement	Ensure CASE has END, all parentheses match
"Division by zero"	Dividing by NULL or 0	Wrap denominator in NULLIF(field, 0)
"Literal does not match format string"	Date format mismatch	Use TO_DATE with explicit format

Debugging Formulas

Start simple - build formula incrementally
Test with single result first
Use NVL to surface NULL issues
Check field IDs with formula: {fieldid} (returns value or error)

Next Steps

Master these formula techniques and explore:

SuiteAnalytics Workbook for complex analytics
Scheduled Search Exports for automation
Search Scripting for programmatic access

Need help building complex reports? Our team builds custom NetSuite reporting solutions. Contact us for a consultation.