Why your NetSuite Saved Search 'Case When' is Broken

by Caleb Schmitz in , , May 11th, 2020

When using SQL expressions, one of the most frequently used pieces is the "Case When." These can often get lengthy so you may have decided to type the content in a word document. This is one of the reasons why you may be getting an error when running the search. If you are new to using NetSuite Saved Search SQL expressions, you can take a look at this free NetSuite beginner course lesson on saved searches.

Saved Search Tip

Let me show you some examples because it is hard to visually see the reason until I show some examples.

Here is the word document with a basic 'Case When' example

Case When Example

If you read through it, then you will see that it should work. I'll copy this into the saved search and you will notice that everything still appears correctly.

Saved Search Formula

You will notice that nothing appears to be out of place, but when you run the saved search, you will get this error message.

ERROR

Lets take a closer look if you haven't seen the issue. It is a tough syntax error to catch.

Issue for Saved Search

You can see that the apostrophe used in the word document is slightly different then when typed directly into NetSuite. It uses the same button on the keyboard, but the format of it being copy/pasted into NetSuite is slightly off.

When I copy the exact text from the word document into a VS code java script file, it is easier to see the difference. You can see that the apostrophe around each of the holidays have a different look than when you would type it directly into NetSuite.

Case When

The image below shows what it will look like if I type the apostrophe directly into that same VS Code java script file using the same button on the keyboard.

Fix for Case When

The Solution:

The issue can be challenging to debug because it is not easily visually easy to catch what is wrong. Luckily, it is a simple solution that is easy to fix with two options.

Option 1

Type your formula directly into NetSuite formula field. This is the easiest method and really is what you will naturally do by default for most scenarios. Sometimes you will get pretty lengthy in your formula(text) field in the saved search so it is useful to organize the information.

  • Pro: Direct into NetSuite
  • Con: Less ability to organize your longer formula's

Option 2

Use a developer editor like VS Code. Using the editor makes sure that the syntax is copy/pasted correctly and it will let you organize your code prior to pasting it into the NetSuite saved search formula area. This is my preferred method because I always have VS code open anyways working on development. Plus, you can easily save the file as a java script file and keep it as a reference backup for the next time you write a long formula.

  • Pro: Allows you to organize your formula to help keep logic straight
  • Con: Requires editor platform to perform consistently (codepen.io is a free option)

Hopefully, this post gives you something to work with while trying to understand NetSuite and what it can do for your business. If you have any questions and want some free consulting advice, feel free to contact our team at Anchor Group.

In fact, we pride ourselves on responsiveness so here is the cell phone number of Caleb, one of our NetSuite consultants!

Phone: (608) 553 - 1983

Rates starting as low as $120/hr for NetSuite implementations, consulting, training, and complex NetSuite development. One of the most affordable U.S.A. based NetSuite partners on the market.

Your cart
    Checkout