skcah
Writing / Netsuite

NetSuite CASE WHEN Formula: Multiple Conditions and Examples

Write NetSuite saved-search CASE formulas with multiple WHEN branches, AND, OR, ELSE, null handling, numeric flags, summaries and DECODE alternatives.

Ryan Sheehy5 minute read

NetSuite saved searches do not provide an Excel-style IF() formula. For conditional results, use an Oracle SQL CASE expression or, for a simple equality mapping, DECODE().

The most reusable form of CASE is:

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE default_result
END

NetSuite checks the WHEN branches from top to bottom and returns the result from the first matching branch.

A simple text result

Use Formula (Text):

CASE
    WHEN {isinactive} = 'T' THEN 'Inactive'
    ELSE 'Active'
END

Checkbox fields normally use 'T' and 'F' in search formulas rather than JavaScript booleans.

Multiple WHEN conditions

Order the conditions from most specific to least specific:

CASE
    WHEN {quantityavailable} IS NULL THEN 'Not tracked'
    WHEN {quantityavailable} <= 0 THEN 'Out of stock'
    WHEN {quantityavailable} = 1 THEN 'Last item'
    WHEN {quantityavailable} < 20 THEN 'Low stock'
    ELSE 'In stock'
END

If the broad < 20 branch appeared before = 1, the value 1 would never reach the more specific branch.

Combine conditions with AND

CASE
    WHEN {duedate} < SYSDATE
         AND NVL({amountremaining}, 0) > 0
    THEN 'Overdue'
    ELSE 'Current'
END

Each WHEN can contain several comparisons.

Combine alternatives with OR

CASE
    WHEN {type} = 'Invoice'
         OR {type} = 'Cash Sale'
    THEN 'Sale'
    WHEN {type} = 'Credit Memo'
         OR {type} = 'Cash Refund'
    THEN 'Credit'
    ELSE 'Other'
END

Use parentheses when AND and OR appear together:

CASE
    WHEN ({type} = 'Invoice' OR {type} = 'Cash Sale')
         AND NVL({amount}, 0) > 1000
    THEN 'Large sale'
    ELSE 'Other'
END

Return a numeric flag for criteria

Formula criteria are often easiest to reason about when they return 1 or 0:

CASE
    WHEN {email} IS NULL
         OR {email} NOT LIKE '%@%'
    THEN 1
    ELSE 0
END

Add it as Formula (Numeric), then compare it with Equal to 1.

The same flag can be summed in a summary search to count matching rows.

Handle null values before comparisons

This condition does not match null amounts:

{amountremaining} = 0

If null should behave like zero, make that choice explicit:

NVL({amountremaining}, 0) = 0

If null has a different business meaning, give it its own branch:

CASE
    WHEN {amountremaining} IS NULL THEN 'Not applicable'
    WHEN {amountremaining} = 0 THEN 'Paid'
    ELSE 'Outstanding'
END

Use compatible return types

Avoid mixing text and numbers:

-- Avoid: returns text in one branch and a number in another.
CASE
    WHEN {amount} > 0 THEN 'Positive'
    ELSE 0
END

Return text consistently:

CASE
    WHEN {amount} > 0 THEN 'Positive'
    ELSE 'Zero or negative'
END

or numbers consistently:

CASE
    WHEN {amount} > 0 THEN 1
    ELSE 0
END

Then choose Formula (Text) or Formula (Numeric) to match.

Aggregate values with CASE and SUM

To count open transactions per customer, use Formula (Numeric) with the Sum summary type:

CASE
    WHEN NVL({amountremaining}, 0) > 0 THEN 1
    ELSE 0
END

Add the customer field as Group. Each matching row contributes 1, so the summed formula becomes the open-transaction count.

To total only matching amounts:

CASE
    WHEN {type} = 'Invoice' THEN NVL({amount}, 0)
    WHEN {type} = 'Credit Memo' THEN -1 * NVL({amount}, 0)
    ELSE 0
END

Apply the Sum summary type to the formula.

Nested CASE versus additional conditions

A nested expression is valid:

CASE
    WHEN {isinactive} = 'F' THEN
        CASE
            WHEN NVL({balance}, 0) > 0 THEN 'Active with balance'
            ELSE 'Active with no balance'
        END
    ELSE 'Inactive'
END

But a flat sequence is often easier to maintain:

CASE
    WHEN {isinactive} = 'F' AND NVL({balance}, 0) > 0
        THEN 'Active with balance'
    WHEN {isinactive} = 'F'
        THEN 'Active with no balance'
    ELSE 'Inactive'
END

Prefer the flat form unless the nested structure communicates a genuine hierarchy.

When DECODE is shorter

DECODE maps exact values:

DECODE(
    {status},
    'A', 'Active',
    'I', 'Inactive',
    'Unknown'
)

Use CASE for ranges, null-specific branches or combined Boolean conditions. Use DECODE for a small exact-value mapping when it remains easier to read.

Common mistakes

  • Forgetting the final END.
  • Using == instead of SQL’s =.
  • Testing null with = NULL instead of IS NULL.
  • Putting a broad branch before a more specific one.
  • Returning incompatible data types.
  • Selecting Formula (Text) for a numeric summary flag.
  • Using display labels instead of {fieldid} references.
  • Mixing summary and non-summary result columns incorrectly.

Start with the smallest working CASE, preview the search, and add one branch at a time.