ANS-0059 · SAVED SEARCHES & FORMULAS
What Statements Can Be Used in NetSuite CASE WHEN Functions?
NetSuite's CASE WHEN functions support various logical and comparison statements to build robust conditional logic for data analysis and custom fields.
Short answer
NetSuite's CASE WHEN function supports AND for multiple conditions, OR for alternative conditions, IN for enumerations, LIKE for string patterns, and BETWEEN for value ranges. Multiple WHEN/THEN clauses or DECODE can be used for varied results, and NULL negates operators like NOT IN or NOT LIKE.
Scenario
Users often need to implement complex conditional logic within NetSuite saved searches, custom fields, or workflows. This requires evaluating multiple criteria or specific data patterns to return different results based on various conditions. Understanding the appropriate statements for CASE WHEN functions is crucial for building effective and dynamic expressions.
Solution
The CASE WHEN function in NetSuite supports several key statements for conditional logic:
The AND statement is used when multiple parameters in a logical test must all be true for the specified result to be returned. For example:
CASE WHEN {type}='Inventory Adjustment' AND {quantity} >= 0 THEN …The OR statement is used when at least one of the parameters in the logical test must be true for the specified result to be returned. For example:
CASE WHEN {account} LIKE '%5143%' OR {memo} LIKE '%Transport inventaire%' THEN …The IN statement is used for enumerations, providing a concise way to check for multiple values without using multiple OR statements. For example:
When there is only one parameter:
CASE WHEN {accounttype} = 'Income' THEN …When there is more than one parameter:
CASE WHEN {type} IN ('Cash Sale','Invoice') THEN …The LIKE statement is used to search for a specific string of characters within a field value. It supports various pattern matching scenarios. For example:
Exact match:
CASE WHEN ({memo} LIKE 'Insurance') THEN …Starts with:
CASE WHEN ({memo} LIKE 'Insurance%') THEN …Ends with:
CASE WHEN ({memo} LIKE '%Insurance') THEN …Contains:
CASE WHEN ({memo} LIKE '%Insurance%') THEN …The BETWEEN statement is used when a logical test must evaluate values within a specific range, avoiding the need for 'greater than AND smaller than' statements. For example:
CASE WHEN {trandate} BETWEEN {accountingperiod.startdate} and {accountingperiod.enddate}Multiple WHEN/THEN combinations or the DECODE function can be utilized when different values in the logical test are expected to return different results. For example:
CASE WHEN {accounttype} = 'Accounts Receivable' THEN {customer.representingsubsidiary} WHEN {accounttype} = 'Accounts Payable' THEN {vendorline.representingsubsidiary} ELSE NULL ENDDECODE({accounttype},'Accounts Receivable',{customer.representingsubsidiary},'Accounts Payable',{vendorline.representingsubsidiary},null)The NULL statement is used to negate an operator. For example:
NOT NULL is the opposite of NULL
NOT IN is the opposite of IN
NOT LIKE is the opposite of LIKE
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support