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.
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
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 to be out of place, but when you run the saved search, you will get this error message.
Lets take a closer look if you haven't seen the issue. It is a tough syntax error to catch.
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.
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.
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.