Why Master Saved Search Formulas?
Saved search formulas extend NetSuite's reporting capabilities far beyond standard columns. With formulas, you can calculate margins, days outstanding, conditional text, and complex aggregations without leaving the saved search interface or writing SuiteScript. Formulas run at query time and can reference any field available in the search context. This guide provides 25+ copy-paste examples you can adapt for finance, sales, and operations reporting.
Formula Syntax Basics
Formulas use SQL-like syntax. Reference fields with {alias.fieldname}—for example, {mainline.amount} for the transaction total, {transaction.type} for the transaction type. Use standard functions: CASE, SUM, CONCAT, TO_NUMBER, TO_CHAR, ROUND, NVL, and COALESCE. Enclose text in single quotes. The formula result type (Currency, Text, Date, Integer) must match the column type you define in the Results tab. Invalid type mismatches will cause the search to fail or return unexpected results.
| Function | Purpose | Example |
|---|---|---|
| CASE WHEN | Conditional logic | CASE WHEN {amount} > 0 THEN 'Debit' ELSE 'Credit' END |
| CONCAT | String concatenation | CONCAT({name}, ' - ', {entityid}) |
| COALESCE / NVL | Handle nulls | COALESCE({memo}, 'No memo') |
| ROUND | Rounding | ROUND({amount}, 2) |
| TO_CHAR | Date/format conversion | TO_CHAR({trandate}, 'YYYY-MM') |
Example 1: Days Between Two Dates
CASE WHEN {trandate} IS NOT NULL AND {duedate} IS NOT NULL THEN {duedate} - {trandate} ELSE NULL END
Use for invoice aging, order cycle time, or project duration. NetSuite date fields are often stored as date IDs; subtracting them yields days. For more accurate results with timestamp fields, use SYSDATE or built-in date functions. Combine with aging buckets: CASE WHEN {duedate} - {trandate} > 90 THEN 'Over 90' WHEN {duedate} - {trandate} > 60 THEN '61-90' ... END.
Example 2: Conditional Text (Status Label)
CASE WHEN {amount} > 10000 THEN 'High Value' WHEN {amount} > 1000 THEN 'Medium' ELSE 'Standard' END
Use for bucketing transactions, customer tiers, or custom status labels. Replace {amount} with any numeric field. For multi-criteria: CASE WHEN {amount} > 10000 AND {custentity_risk} = 'Low' THEN 'Approve' ... END.
Example 3: Percentage of Parent (Margin)
In a transaction line search: CASE WHEN {amount} <> 0 THEN ROUND(({grossprofit} / {amount}) * 100, 2) ELSE NULL END
Use for margin %, discount %, or line-level ratios. Always guard against division by zero. For percentage of transaction total: reference mainline.amount in a transaction line search using the mainline alias.
Example 4: Sum with Conditions (Summary Search)
Use a Summary search with Type = "Group" and Summary Type = "Sum" for numeric aggregation. Group by the dimension (customer, item, subsidiary); add a Summary column with Summary Type = "Sum" for the amount. For conditional sums, add a Formula column that returns the value or 0 based on criteria, then summarize that formula column.
Example 5: Concatenate with Delimiter
CONCAT({item}, ', ', {quantity}) — useful for combined display columns. Use COALESCE to handle nulls: CONCAT(COALESCE({customer}, 'N/A'), ' - ', {trandate}). For multi-field display: CONCAT({tranid}, ' | ', {entity}, ' | ', TO_CHAR({trandate}, 'Mon DD, YYYY')).
Example 6: Extract Year and Month
For grouping: TO_CHAR({trandate}, 'YYYY-MM') or TO_CHAR({trandate}, 'YYYY') for year only. Helps with period-based reporting and trend analysis. Use in Summary searches to group by period.
Example 7: Running Total (Summary Search)
Create a Summary search with Summary Type = "Sum" and order by the relevant dimension. Running totals across rows typically require SuiteScript for full flexibility; within Summary searches you can approximate with multiple summary columns. For true running totals (e.g., cumulative YTD), consider a scheduled script that populates a custom record or a Workbook with calculated columns.
Example 8: Subquery (Related Record Lookup)
Use subqueries for "exists in another record" logic. Example: find customers who have an open invoice: subquery on Transaction where type = Invoice and status not in (Paid, Voided). Syntax varies by NetSuite version; check SuiteAnswers for current subquery support. Subqueries can be resource-intensive—use indexed fields in subquery criteria.
Examples 9–15: Quick Reference
- Age in days:
TRUNC(SYSDATE) - {trandate} - Negative amount flag:
CASE WHEN {amount} < 0 THEN 'Credit' ELSE 'Debit' END - Fiscal period from date: Use a custom period lookup or
TO_CHAR({trandate}, 'YYYY') || '-' || LPAD(TO_CHAR(MONTH({trandate})/3), 2, '0')for quarters - Multi-currency display:
{amount} || ' ' || {currency} - First/last order date: Requires Summary search with Min/Max on trandate grouped by entity
- Tier-based label: Nested CASE for multiple tiers
- Null-safe math:
NVL({amount}, 0) + NVL({adjamount}, 0)
Performance Tips
- Avoid formulas on high-volume searches—pre-calculate in custom fields when possible
- Use indexed fields in criteria to reduce rows before formula evaluation
- Limit result columns; each formula adds processing
- Test with small date ranges first
25+ More Examples
Additional formulas: age in days, tier-based commission, fiscal period from date, multi-currency conversion display, first/last order date from customer, negative amount flag, and more. The key is understanding your field IDs and combining CASE, CONCAT, and math appropriately. Refer to NetSuite's formula function reference in Help for the full list.
YRK Consulting helps design and optimize saved searches for complex reporting needs. Get in touch.