NetSuite Saved Search Formula – CASE Statement

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
Case Statement Column

Case Statement Column

The Result shows

Case Statement Result

Case Statement Result

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

Feel free to contact us if you need help building out complex reports in NetSuite!

About Abaci
Abaci is a premier NetSuite Solution Provider with deep knowledge in both business process and technology architecture. Our team is passionate about NetSuite and has an exclusive commitment to NetSuite and to providing the highest level of system expertise on its full suite of cloud-based business management applications.