Talking Tech: How to Use CASE Statement in NetSuite Saved Search

Let’s Talk Tech.

NetSuite Saved Search Formula – Case Statement

Saved Search Formula is one of the coolest feature in NetSuite. It gives the users the ability to use Oracle SQL Functions and/or Statements when creating your search. One of the most popular one is CASE Statement. It allows you to incorporate logic into your formula to return static or dynamic value. This can be used on your saved search filter or result column.

There are two different formats in NetSuite Formula Fields:

• Simple CASE: “This Or That”

  case when [Formula expression that results in boolean] then
    [True Value]
  else
    [False Value]
  end
  

• Nested CASE: “This Or That Or Them… “

  case  when [Formula expression that results to boolean] then
      [True Value]
  when [2nd Formula expression that results to boolean] then
      [2nd True Value]
  when [3rd Formula expression that results to boolean] then
      [3rd True Value]
  else
      [False Value]
  end
  

It is important to note that what ever value your CASE statement returns, it must match the formula type. For example, if you are writing a case statement to return Text value, your formula column or filter should be Formula (Text).

Sample Code
Below sample formula grabs total number of records that have a checkbox field checked vs not checked. This example use case is useful if you want to count number of rows in the summary search results that have same value. As you know, COUNT summary type ONLY counts unique values.

//Individual Customer Formula
CASE WHEN {isperson}='T' THEN 
   0 
ELSE 
   1 
END

//Company Customer Formula
CASE WHEN {isperson}='T' THEN 
   1 
ELSE 
   0 
END

The Result shows

that Total number of customers in the system as well as those that are Individual vs those that are Company type.