NetSuite CASE WHEN Formula

If you have been hammering vanilla saved searches for a while and you have reached the limit of what you can get out of them then I have some good news – You have only just scratched the surface.

Using summary types, functions and formulas you can get so much more out of the system and present data in ways you wouldn’t think possible in a simple query tool.

This article, which will only serve as an introductory step, is one of many on NetFreak that look at the more complex features of NetSuite saved searches. To start you on your wild and wonderful journey to advanced saved searches we will begin with some simple formulas.

The NetSuite Gods, when the world was created, blessed us with the beautiful ability of querying the database with not only simple saved search criteria but also custom formulas in Oracle SQL. One day you are going to be an utter whizz with SQL and be able to throw out searches at the drop of hat, but your journey must start somewhere.

I think a great place to begin is the CASE WHEN statement. For a beginner this may look daunting but it’s actually the formula I, and many other admins, use the most. It’s very useful to have in your arsenal.

How To Use A CASE WHEN Statement in NetSuite Saved Search

Before getting into the syntax, you will notice that in the list of fields under the results tab of a saved search there are various different Formula fields. You will need to select the Formula field that matches the data type of the results you want to display. In the example I will work through below I am displaying Invoice values so I will select Formula (Currency).

In simple terms a CASE WHEN statement allows you to show different results in the same column depending on a criteria such as:

Transaction Search

When the record type is Invoice show me the total Amount.

When the record type is Credit Memo show me ‘0’.

Without using formulas you would only be able to show either the amount or a ‘0’ in the same column..

A CASE WHEN statement is made up of the following syntax.

CASE WHEN [criteria] THEN [result] ELSE [alternative result] END

Using the example of Invoices and Credit Memos we will insert the written text within the square brackets to give you a visual idea of how the statement is made up. The assumption here is that your search criteria means you are only showing Invoices and Credit Memos.

CASE WHEN [the record type is Invoice] THEN [show the total Amount] ELSE [show '0'] END

Not so daunting when you see it like that, is it? To make this real, you now just need to insert the actual field IDs. Your formula will then look something like this.

CASE WHEN {type} = 'Invoice' THEN {amount} ELSE 0 END

The results of your search will now only show Invoice values in that column and show ‘0’ for Credit Memos.

Using a CASE WHEN in NetSuite to split search results across two columns

This is a very simple start to using the CASE WHEN statement but it’s important to understand the basics before bolting more features on. This statement alone can be used in so many different ways and will often be the skeleton used to support other more complex syntax. Once you have mastered the use of the CASE WHEN you will find yourself defaulting to it in so many scenarios to consolidate columns and make the saved search more presentable and readable to non NetSuite users. Bear in mind also that, if you have admin status, SQL can be used in various other parts of NetSuite including certain workflow actions.

More Advanced Saved Search Formulas

The following articles are a selection from NetFreak that will teach you more formulas to use in your NetSuite saved search. You can also search the database if you have a particular formula you need help with. If you can’t find what you are looking for, feel free to contact us and we may be able to help.

NetSuite CASE WHEN Formula With Multiple Criteria and Date Parameters

Highlight a Column in NetSuite Saved Search

NetSuite SQL Formula Cheat Sheet

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *