Saved Search Formula Examples (NetSuite)

🔹 Why Use Formulas in Saved Searches?

Saved Searches are powerful, but sometimes the built-in filters and columns aren’t enough.
Formulas let you:

• Add custom logic (IF/ELSE with CASE).
• Transform field values (e.g., uppercase, substrings).
• Do date math (days open, overdue, etc.).
• Group, flag, or categorize results for reporting.

Formulas support Text, Numeric, and Date types.

🔹 Formula Basics

• Formula (Text) → returns text values.
• Formula (Numeric) → returns numbers.
• Formula (Date) → returns dates.

Tip: Always test in small searches first—NetSuite formula syntax is close to Oracle SQL, but with restrictions.

🔹 Common Formula Examples

1) ✅ Overdue Flag (Text)

CASE WHEN {duedate} < SYSDATE THEN 'Overdue' ELSE 'On Time' END

• Returns “Overdue” if due date is before today.
• Works in Formula (Text) column.

2) ✅ Days Open (Numeric)

TRUNC(SYSDATE) - TRUNC({trandate})

• Calculates number of days since transaction date.
• Use in Formula (Numeric).

3) ✅ Month Name from Date

TO_CHAR({trandate}, 'Month')

• Extracts month name of the transaction.
• Use in Formula (Text) column.

4) ✅ Current Year Transactions Only (Filter)

EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM SYSDATE)

• Filter for only this year’s transactions.
• Put under Formula (Numeric) filter → equal to EXTRACT(YEAR FROM SYSDATE).

5) ✅ Customer Category: Big vs Small

CASE WHEN {amount} >= 10000 THEN 'High Value'
     WHEN {amount} BETWEEN 5000 AND 9999 THEN 'Medium Value'
     ELSE 'Low Value'
END

• Classifies transactions into categories.
• Great for pivot reports in Excel.

6) ✅ Remove Time from Date

TRUNC({trandate})

• Returns only the date (midnight).
• Helpful for grouping by date without time noise.

7) ✅ First 5 Characters of Customer Name

SUBSTR({entityid}, 1, 5)

• Use in Formula (Text) to extract part of a string.

8) ✅ Show Only Weekday Transactions

CASE WHEN TO_CHAR({trandate}, 'DY') IN ('SAT','SUN') THEN 'Weekend' ELSE 'Weekday' END

• Splits transactions into Weekday vs Weekend.

9) ✅ Combine Multiple Fields

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

• Concatenates two fields (first + last name).
• Formula (Text).

10) ✅ Aging Buckets (0–30, 31–60, 61+)

CASE 
  WHEN TRUNC(SYSDATE) - TRUNC({duedate}) <= 30 THEN '0-30 Days'
  WHEN TRUNC(SYSDATE) - TRUNC({duedate}) BETWEEN 31 AND 60 THEN '31-60 Days'
  ELSE '61+ Days'
END

• Classic AR/AP report aging buckets.

🔹 Advanced Examples

11) ✅ Find Transactions on Last Day of Month

CASE WHEN {trandate} = LAST_DAY({trandate}) THEN 'Month End' ELSE 'Regular' END

12) ✅ Flag International Orders

CASE WHEN {shipcountry} != 'US' THEN 'International' ELSE 'Domestic' END

13) ✅ Extract Year-Month Key for Grouping

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

14) ✅ Compare Two Fields

CASE WHEN {total} != {amountpaid} THEN 'Open' ELSE 'Closed' END

🔹 Tips & Best Practices

✅ Use Formula (Text) for labels, categories, or concatenated fields.
✅ Use Formula (Numeric) for counts, days difference, math.
✅ Use Formula (Date) for calculated dates (add days, remove time).
✅ Wrap formulas in CASE for clean logic.
✅ Test in small searches before production.
✅ Remember: SYSDATE = today’s date.

✅ Summary

Saved Search formulas are like “mini SQL” inside NetSuite. They allow you to:

• Flag records,
• Classify data,
• Do math with dates and numbers,
• Make your reports smarter and easier to analyze.

With formulas, you can transform basic searches into powerful reports and dashboards without custom scripts.