Saved Search Formulas 2 - The CASE Statement

Note: this is the second in a series of blog posts relating to Formulas in NetSuite. It is recommended you read the first post here before reading this one.

In my last blog post on Formulas, we added a Formula (Text) field to a Saved Search to merge the Company and Primary Contact fields together, thus getting around NetSuite's limitation on the number of columns in a list portlet.

However, we ended up with a problem:

The fields were successfully merged with a hyphen between the Company and Contact, however the hyphen still shows up even when there's no Primary Contact for that Company. This is what we'll try to solve in this post, with the help of the powerful CASE expression.

The CASE expression allows you to return a different value in your row, depending on the outcome of a comparison. For you programmers out there, it's basically an IF statement.

There's a ton of info on the CASE expression online, including Google or even NetSuite's own Help Center. Here's my very quick take on it:

CASE WHEN comparison THEN value1 ELSE value2 END

What that means in English:

If comparison is true, then use value1, otherwise use value2.

comparison has to be a valid SQL comparison expression. This can get tricky for those who aren't familiar with basic programming, so I'll stick to our example here. I like to start by stating my CASE expression in plain English, then converting it to SQL. So here's what we're trying to do:

If the Primary Contact is empty, then show the Company, otherwise show the Company and Primary Contact separated by a hyphen.

Using pieces of the formula we've already built in the previous post, and using a little bit of SQL experience, the appropriate CASE expression for this would be the following:

CASE WHEN {contact} IS NULL THEN {entityid} ELSE {entityid}||' - '||{contact} END

Let's go back into our search and add this formula in. Edit your Saved Search, in the Results tab click on the Formula column of the Formula (Text) field, and replace the previous formula with the new one:

Save the Search and check it out on the Dashboard:

You'll notice that now, if the Company has no Primary Contact, there's no lonely hyphen at the end of the value (like "Kerry Furnishings & Design"), but if there is a Primary Contact, it still displays nicely (like "Patel Cafe - Anil Patel").

And there you have it! Not only do we have a Formula, but we have one that changes depending on the values of the fields! You can imagine how powerful this could be; the same concept can be used to compare currency/numeric/date values, filter out results, and a bunch of other cool applications!

Next blog post, I will build upon this example by using a Formula inside Criteria instead of just showing it in the Results.

1 comment:

  1. That's a good introduction to case statements… I think many people who have NetSuite fail to appreciate how powerful saved searches and formulas are. One tip I have for people who are learning formulas is to always test them in the results tab before moving them into the criteria. If you make a mistake in the results tab, the search will at least still run… If there is a problem in the criteria formula, the search will often fail to run at all.