NetSuite Saved Search Formulas: Getting Started

How do you use a formula in a Netsuite saved search?

There are several different types of formulas you can use in a Netsuite saved search. They vary according to the result you are seeking to produce from numbers, text, dates and even HTML.

You would use a formula when trying to calculate something that needs an operation or filter applied to a field that cannot be obtained from other fields or sources. A formula can be used created in the Criteria , Result and Highlight areas within a Saved Search.

Creating Saved Search Formula

In NetSuite, creating a saved search formula involves setting up criteria and defining the results you want to see. Here’s a breakdown of the process:

• NetSuite navigation for creating a saved search formula: To create a saved search formula, log in to NetSuite and go to Lists > Search > Saved Searches > New . You will then be directed to the Saved Search page where you can then select the type of record you would like to operate on.
• Setting up saved search criteria: In the Criteria tab, you can set up the conditions that the saved search formula will follow in order to generate the desired results. This can include defining record types, defining filter criteria such as date ranges or specific values, and combining criteria with logical operators such as "AND" and "OR" .
• Defining saved search results columns: Once you have set up the criteria, move to the Results tab where you can define the columns that will generate the desired results. This can include specific fields from records, formula columns, summary types, or inline editing. For example, to create a report of your companies sales, you can select Transaction saved search, then in the Result tab select columns like Sales Representative , Amount , and the Date Created .

By following these steps, you can easily create your own basic NetSuite saved search.

The next step is to understand the saved search formula syntax in order to customise your formula to best meet your needs.

Understanding Saved Search Formula Syntax

To get the most out of saved search formulas in NetSuite, it’s essential to understand the syntax elements that make up the formulas. Here are the key syntax elements you need to know:

• Operators: These are symbols or words that represent actions to be performed on fields or values in the formula. Examples of operators include the plus ( + ) sign for addition, minus ( - ) sign for subtraction, multiplication sign ( * ) and division sign ( / ).
• Functions: These are pre-defined operations that perform specific calculations and return a result. Examples include the SUM function for adding values, the CONCAT function for joining text, and the DECODE function for checking conditions and returning specific values (an IF statement equivalent).
• Formulas: These are expressions that use operators, functions, and field IDs to calculate and return a result. NetSuite provides a formula editor that simplifies the process of creating formulas by providing a list of available functions and field IDs, as well as syntax highlighting and error checking.
• Field IDs: These are codes that identify specific fields in NetSuite . You can find them by opening up a record and clicking on the label. The ID of the field is found in the top-right hand corner of the window that pops up providing more context about the field. For example, if I wanted to use the PO# field in my saved search I could open up an empty Sales Order and click on the PO# field and by looking at the FieldID value I can see that this is represented as otherrefnum . I would then insert {otherrefnum} into my formula if I wanted to use this value in my saved search formula.

If you can’t see the FieldID upon clicking the field label, check your preferences are set to view these ID’s.

Navigate over the little house icon and click on Set Preferences from the menu that pops up. Then on the General tab ensure the box for Show Internal IDs is ticked, like so:

Use of Operators in Saved Search Formula

Operators are a crucial component of saved search formulas in NetSuite. They enable you to perform calculations, comparisons, and manipulations on field values, literals, and other formula elements.

Here are some examples of operators that you can use in saved search formulas:

• Comparison Operators: These are used for comparing two values and returning a Boolean (true/false) result. Examples include the equals (=), not equals (!=), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=) operators.
• Logical Operators: These are used for combining multiple comparison statements and returning a Boolean result. Examples include the AND , OR , and NOT operators.
• Concatenation Operator: This is used for joining two or more text strings into a single string. The operator is represented by a pipe ( | ) symbol.

Examples of Commonly Used Saved Search Formulas

Saved search formulas can be quite complex, but some are more common than others. Here are a few examples of commonly used saved search formulas:

• CASE Statement: This formula allows you to check specific conditions and return different results based on those conditions. The syntax for the CASE statement is CASE WHEN x THEN y ELSE z END
• DECODE Formula : This formula works like a case statement but with only two possible outcomes. The syntax for the DECODE function is DECODE(expression, search, result_true, result_false)

By knowing the key syntax elements, operators, and functions for saved search formulas in NetSuite, you can create more complex and powerful search results that help you make better business decisions.

Optimising saved search performance

As with any software system, there are ways to optimize your use of NetSuite’s saved search formula feature. Here are some tips to help you make the most of saved search formulas:

Limited use of formula fields

Formula fields can contain complex calculations and can impact the performance of a saved search. Therefore, it’s important to limit the number of formula fields used in a saved search wherever possible. Instead, use native fields (fields provided by NetSuite) or summary fields to achieve the desired results, as these are generally faster and more efficient.

Simple expressions for formula fields

Wherever possible, use simple expressions for formula fields. Avoid using nested functions and other complicated calculations, as these will take longer to compute and will negatively impact the performance of your saved search.

Avoiding nested functions

If you do need to use functions in your formula fields, try to avoid using nested functions (functions within functions). These can be difficult to read and maintain, and will again impact the performance of your saved search.

By following these tips, you can ensure that your saved search formulas are as efficient and effective as possible, saving you time and frustration in the long run.