Enhancing NetSuite Saved Searches with Conditional Statements

NetSuite Saved Searches functionality is an invaluable tool for any NetSuite User. The ability to quickly identify specific, detailed pieces of information makes Saved Searches a critical lifeline. The basic search results are fairly straightforward. For example, if you want to identify the document number for the specified records, you will include the ‘Document Number’ field in your search results. While this is quite useful, there is additional power available through the use of Formula fields which can take Saved Searches to a new level of usefulness.

NetSuite Saves Searches Formula Fields and Functions

Formula fields allow users to utilize Oracle SQL functions to enhance search results. Using Formula fields, users can modify data, return conditional results, and more. For example, formulas can be used to modify data by rounding numbers, adding months to a date, or combining pieces of text, such as first and last name. With conditional statements, you can add logic to display different results based on a condition.

How to Use the CASE Function

One of the most useful SQL functions is the CASE function. The CASE function allows users to begin making conditional statements. This is comparable to using =IF in Microsoft Excel. Combining the CASE function with ‘WHEN’, ‘THEN’, ‘ELSE’ and ‘END’ allows users to make fully functioning conditional formulas, just like Excel. Consider the example below:
CASE
WHEN {item.taxschedule} = ‘Not Taxable’
THEN ‘No Taxes’
ELSE {taxamount}
END

In the case above, we are checking items’ taxability to determine whether the search should return the amount of taxes paid or the text string “No Taxes.” If the item is not taxable, we will see the search return “No Taxes.” Otherwise, the amount of taxes will be displayed. Variants of this scenario are used on a regular basis in NetSuite.

How to Use Multiple CASE Conditions

There may be scenarios where users require multiple conditions within a formula. In these cases, we can add additional scenarios by adding ‘WHEN’ and ‘THEN’, continuously. For example, consider the formula below:
CASE

WHEN – SCENARIO A
THEN – OUTCOME A

WHEN – SCENARIO B
THEN – OUTCOME B

WHEN – SCENARIO C
THEN – OUTCOME C

ELSE – OUTCOME IF NO SCENARIO APPLIES

END

The expression above allows us to test multiple scenarios before defaulting to the ‘ELSE’ outcome. This formula, with multiple conditions, can be utilized in many aspects of your business.

Benefits of CASE Statement

The CASE function can be utilized across many modules in NetSuite. It is not limited to Saved Search functionality, but this is where most users utilize the function.

Understanding how to operate the CASE function in Saved Searches will improve your ability to validate data, create useful, custom reports, and simplify previously complex formulas. NetSuite’s Saved Searches functionality is an incredibly flexible tool and adding SQL formulas and functions can greatly increase the usefulness of these Saved Searches.

Need help with NetSuite Saved Searches?

Consider the various conditional scenarios and outcomes in your business processes. How could you utilize conditional formulas like the CASE function to make your business process and reports more efficient? Rand Group can help you improve your efficiency, make processes run more smoothly, and take advantage of what NetSuite has to offer. Contact a Rand Group representative today.

– Software Delivered as Promised. No Surprises.

Latest Posts