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

Let’s Talk Tech.

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.

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

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.

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