Standard saved search filters are excellent for straightforward queries, but to unlock the full potential of NetSuite's search capabilities, you must master formula criteria. Formulas allow you to filter on calculated values, compare fields against each other, and implement complex, conditional logic that standard filters cannot handle. This guide provides a fact-checked and expanded look at using SQL expressions, particularly CASE WHEN statements, in your saved search criteria.

The Power of Formula Criteria

Instead of selecting a field and a static value, you add a special filter type—Formula (Numeric) or Formula (Text)—and provide a SQL expression. For criteria, the most common and powerful technique is to write a formula that returns a specific value (e.g., 1) when your desired conditions are met. You then filter the search to include only the rows that return that value.

The Core Pattern: `CASE WHEN`

The CASE WHEN statement is the cornerstone of conditional logic in NetSuite formulas. It functions like an IF/THEN/ELSE statement directly within your search. The typical pattern for a search criterion is:

CASE WHEN {condition} THEN 1 ELSE 0 END

You would add this to a Formula (Numeric) filter and set the criterion to is equal to 1. This effectively tells NetSuite to include only the rows where your specified condition is true.

Practical `CASE WHEN` Examples

The following examples from the original article have been verified for accuracy and are common use cases for formula criteria.

1. Transactions Older Than 90 Days

Filters for records with a transaction date more than 90 days in the past.

CASE WHEN {trandate} < TRUNC(SYSDATE) - 90 THEN 1 ELSE 0 END

2. Amount Exceeds Budget by More Than 10%

Finds transactions where the amount is over 110% of a custom budget field. The `NVL` function is used to handle cases where the budget field might be empty.

CASE WHEN {amount} > NVL({custbody_budget}, 0) * 1.1 THEN 1 ELSE 0 END

3. Invoices in a Specific Aging Bucket (31-60 Days)

Uses `AND` to combine multiple conditions to isolate a specific aging range.

CASE WHEN {daysoverdue} > 30 AND {daysoverdue} <= 60 THEN 1 ELSE 0 END

4. Case-Insensitive Text Search in Memo

Uses the `UPPER` function to convert the memo text to uppercase before matching, effectively making the search case-insensitive.

CASE WHEN UPPER({memo}) LIKE '%RUSH%' THEN 1 ELSE 0 END

5. Customer Has No Email Address

Checks for both truly empty (`NULL`) fields and fields that contain only empty spaces.

CASE WHEN {customer.email} IS NULL OR LENGTH(TRIM({customer.email})) = 0 THEN 1 ELSE 0 END

6. Advanced: Transaction in Current Fiscal Period

This complex formula uses a subquery to dynamically find the ID of the current accounting period. While powerful, be aware that subqueries can impact search performance.

CASE WHEN {postingperiod.id} = (SELECT id FROM accountingperiod WHERE isquarter = 'F' AND isyear = 'F' AND startdate <= TRUNC(SYSDATE) AND enddate >= TRUNC(SYSDATE)) THEN 1 ELSE 0 END

Verified SQL Functions Reference

The original article provided a list of common SQL functions. We have verified this list against Oracle NetSuite's official documentation. The functions listed are accurate and supported.

Date Functions

Text Functions

Null-Handling Functions

Numeric Functions

Implementation and Best Practices

Adding the Formula:

1. On the Criteria subtab of your search, add a new filter line.

2. In the Filter dropdown, select Formula (Numeric) or Formula (Text).

3. In the Formula popup, enter your CASE WHEN expression or other SQL formula.

4. Set the comparison and value (e.g., is equal to and 1).

Debugging Tip: If your formula criterion isn't working, add the exact same formula to the Results tab. Run the search and examine the output for each row. This will help you see what the formula is actually returning (e.g., 0, 1, null, or an error) and adjust your logic accordingly.

Performance: Formulas are evaluated for every row returned by the search. To ensure your search runs efficiently, always apply as many standard, indexed criteria as possible (e.g., `Date`, `Status`, `Subsidiary`) to reduce the dataset before the formula is applied.