NetSuite Formula – DECODE function

NetSuite DECODE function in Search Formula

On our previous post, we talked about CASE statement. Here, we like to introduce DECODE function you can use in your NetSuite Saved Search. It is very similar to CASE statement with slight differences in function and usage:

  • It performs equality check only.
  • CASE statement is more readable compared.
  • CASE statement can handle more complex logic.

DECODE function was introduced first and CASE statement came later in Oracle Database (NetSuite backend database system). This means CASE statement can do everything DECODE can do but not vice versa.

Syntax Explained

Sample 1

DECODE({isperson},'Yes','Checked','Not Checked')

If “Is Individual” field ({isperson}) is checked, return “Checked”. Return “Not Checked” by default if no match is found.

Sample 2

DECODE({lastname},'Smith','Last name is Smith','Lee','Last name is Lee','Pitt','Last name is Pitt','Unknown')

In this example, we build dictionary of key/value pair based on value of {lastname} field of the record. If {lastname} value is “Smith” return “Last name is Smith”, if value is “Lee” return “Last name is Lee”, if value is “Pitt” return “Last name is Pitt” and finally if none of the value matches, return “Unknown”.

Use Case

CFO would like to see a report of total amount and count of invoices and cash sales for a given year broken down by month for each active customers.

We start off by building out the Search Criteria.

Decode function sample search criteria

Decode function sample search criteria

For the result column definition, we are going to create two formula columns per each month in a year and group by customer.

Decode Function search result definition

Decode Function search result definition

DECODE(TO_CHAR({trandate},'Mon'),'Jan',{amount},0)

Currency formula column will return amount value if transaction date matches the Month. By default it will return 0 as value. TO_CHAR({trandate},’Mon’) returns three character abbreviation of the month. For example, Jan, Feb, Mar and etc. Returned amount values are summarized using SUM.

CASE WHEN {amount} > 0 and TO_CHAR({trandate},'Mon') = 'Jan' then 
   1 
else 
  0 
end

Numeric formula column will return value of 1 if transaction date matches the Month and value of transaction is greater than 0. Returned values are summarized using SUM.

Here are screen shots of executed search result.

Decode Function executed Result

Decode Function executed Result

Decode Function executed Result

Decode Function executed Result

Decode Function executed Result

Decode Function executed Result

Decode Function executed Result

Decode Function executed Result

These type of reports are quick to build and easily deployed to management team. Do you need complex reports built? or improve existing ones? Give us a call!

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.