Advanced Saved Search Formulas in NetSuite

📝 Why Use Formulas in Saved Searches?

While basic saved searches use filters and columns, formulas unlock advanced calculations and logic. They allow you to:

• Calculate days between dates.
• Categorize transactions by value.
• Combine text fields into one.
• Build conditional logic like SQL CASE statements.

NetSuite supports formula fields in Criteria (filters) and Results (columns).

✅ Example 1: Days Since Transaction

Goal: Show how many days a transaction has been open.

{today} - {trandate}

Usage:

• Add to Results as Formula (Numeric).
• Shows the number of days since the transaction date.

✅ Example 2: Conditional Value Label

Goal: Flag high-value transactions as “High Value” or “Standard”.

CASE WHEN {amount} > 5000 THEN 'High Value' ELSE 'Standard' END

Usage:

• Add to Results as Formula (Text).
• Useful for dashboards and categorization.

✅ Example 3: Customer Full Name

Goal: Combine first name and last name into one field.

{firstname} || ' ' || {lastname}

Usage:

• Add to Results as Formula (Text).
• Helpful for reports and exports.

✅ Example 4: Aging Buckets

Goal: Categorize invoices into aging buckets.

CASE 
  WHEN {duedate} < {today}-30 THEN 'Over 30 Days' 
  WHEN {duedate} < {today}-60 THEN 'Over 60 Days' 
  ELSE 'Current' 
END

Usage:

• Add to Results as Formula (Text).
• Used in A/R collections dashboards.

✅ Example 5: Month of Transaction

Goal: Extract the month name from a transaction date.

TO_CHAR({trandate}, 'Month')

Usage:

• Add to Results as Formula (Text).
• Great for grouping sales by month.

💡 Pro Tips

• Choose the right formula type → Text, Numeric, Date, Percent.
• Use CASE statements for conditional logic.
• Keep formulas simple — test step by step.
• Save formula snippets for reuse in multiple searches.

📌 Key Takeaways

• Formulas extend the power of Saved Searches with calculations and conditions.
• You can build aging reports, customer categorizations, and combined fields.
• Start with simple examples, then move into advanced SQL-style logic.

👉 Next Page: Custom Financial Reports