Why your NetSuite Saved Search 'Case When' is Broken

When using SQL expressions, one of the most frequently used constructs 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 reason 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.

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

Here is the Word document with a basic '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.

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

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

You can see that the apostrophe used in the Word document is slightly different from when typed directly into NetSuite. It uses the same keyboard button, but the format when copied/pasted into NetSuite is slightly off.

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

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

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 can be fixed in two ways.

Option 1

Type your formula directly into the NetSuite formula field. This is the easiest method and is really what you will naturally do by default in most scenarios. Sometimes you will get a pretty lengthy 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 formulas

Option 2

Use a developer editor like VS Code. Using the editor ensures that the syntax is copied/pasted correctly, and it lets you organize your code before pasting it into the NetSuite saved search formula area. This is my preferred method because I always have VS Code open anyway, working on development. Plus, you can easily save the file as a JavaScript 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 an editor platform to perform consistently (codepen.io is a free option)

Hopefully, this post gives you something to work with as you try 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.