CASE WHEN statements in NetSuite saved searches are an extremely useful way to compare fields when displaying results. These are complex and require a lot of testing to get what you want. But mastering the CASE WHEN statement will unleash some serious power in your saved searches.

The key parts of the CASE WHEN statement go like this

CASE WHEN {fieldtocheck} is some value to check THEN do something ELSE do something else END

Example, Using Field FORMULA (NUMERIC)

CASE WHEN {totalquantityonhand} is NULL AND {quantitycommitted} is NULL THEN (0 - {reorderpoint})       WHEN {totalquantityonhand} is NULL AND {quantitycommitted} is NOT NULL THEN ((0 - {quantitycommitted}) - {reorderpoint})    WHEN {totalquantityonhand} is NOT NULL AND {quantitycommitted} is NULL THEN (({totalquantityonhand} - 0) - {reorderpoint})      ELSE (({totalquantityonhand} - {quantitycommitted}) - {reorderpoint}) END

The above example checks for values that are NULL to make sure they are counted as a value of zero; zero and null being different values. These statements can become cumbersome and require a lot of logical thinking and testing to get the results you are looking for.

Styling results on a formula field enhance the results even further. This lets you add images, hyperlinks, or background colors.

Example, Using Field FORMULA (TEXT)

CASE WHEN {amount} > 275 THEN '<div style="color:white;background-color:green;padding:5px;text-align:center;">' || '★ Great' ELSE '<div style="color:black;background-color:yellow;padding:5px;text-align:center;">' || '☆ OK' || '</div>'END

The above example makes the results of a simple value check a bit more visually appealing with CSS.

More Advanced Saved Search Examples:

Creating a link to a sales order number:

'<a style="color:#0074D9;" href=https://XXXXXX.app.netsuite.com/app/accounting/transactions/salesord.nl?id=' || {transaction.internalid} || ' target=_blank>' || {transaction.tranid} || '</a>'

I did the above example on an item search as a way to directly link to the sales order. Originally the link to document number was going to the ITEM and not the SALES ORDER.

XXXXXX = would be replaced by your NetSuite company ID.

Display an image when an order is Amazon Prime:

CASE WHEN {custbodycustbody_amazon_prime} = 'T' THEN '<div><img src="link_to_photo.png" width="75px" /></div>' ELSE '<div> </div>' END

This one verifies if a custom field, a checkbox, is checked or not and displays the Amazon Prime logo if it is.

There are thousands of ways to use NetSuite advanced saved searches. I’ve only shown a few examples but I hope it provides a starting point to adding more features to your saved searches.

Feel free to contact me with questions or ideas or ways you’ve used CASE WHEN statements in NetSuite to enhance saved searches for your business.