SuiteAnalytics: Using IN and NOT IN in formulas

Hi fellow NetSuite professionals!

Have you ever had a chance to use the CASE WHEN … IN () formula? You might have or might have not. Either way, I think this can be a useful tool at least once in your life as a NetSuite professional.

For example, there’s one thing that my fellow NetSuite Developers could relate to. We love searching for transactions using internal IDs. However, there are times when we had to look for more than one transaction through the use of saved searches.

This is where the CASE WHEN .. IN () syntax may come in. In my personal experience, it has tremendously increased my productivity as a NetSuite professional. It allows me to limit my search results to selected transactions only, without having to wait forever to select transactions on the Criteria box.
slow-saved-search-internalid
If you have been working with NetSuite long enough you might have experienced waiting forever to select a specific transaction/internal ID in the saved search filters

Below you will find its syntax. You will find it useful in some contexts like when you only need certain specific transactions to get returned in your search results.

CASE WHEN {internalid} IN (’24’, ’25’, ’26’, ’27’) THEN 1 ELSE 0 END

When you want the other way around, simply add the negator ‘NOT’ to the formula:

CASE WHEN {internalid} NOT IN (’24’, ’25’, ’26’, ’27’) THEN 1 ELSE 0 END

You’re all set. When you encounter a day in your life with NetSuite wherein you had to look for specific transactions only, you may use the above sample formula.

LEACC Consulting is a team of NetSuite Certified Developers with IT experience solely dedicated to NetSuite technologies for eight years. We are experts in both SuiteScript 2.0 and SuiteScript 1.0. Our team has worked with 400+ NetSuite scripts and workflows combined. If you need some consulting help of how you can design your NetSuite scripts better, contact us by filling out this form or by commenting below.

Do you have NetSuite Development needs? Do you need help with a solution like this? Click on the link below and let’s have a chat about your project requirements!

Contact us for your NetSuite needs!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s