Let's Talk

Let's Talk.

Looking for our offices? View locations.

Talking Tech: How to Use Decode Function in NetSuite Saved Search

Let’s Talk Tech.

Accordion’s “Talking Tech” series explores how different CFO Technology solutions can empower finance functions to support organizational strategic initiatives – by implementing business process recommendations, optimizing operations, and capitalizing on value creation opportunities.

Now, let’s take a look at how to leverage the DECODE function in NetSuite Saved Search.

NetSuite DECODE function in Search Formula

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

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

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 

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

These type of reports are quick to build and easily deployed to management team.

Need NetSuite Support? Let's Talk.