Where Is The “IF” Statement In A Saved Search Formula?

Saved searches are a powerful tool to fetch and display data in NetSuite. However, there may be times with the display of our saved search data where we want to transform the data into something else.

To display data in a format based on a condition we need to use the Formula fields in the Result tab.

Within the Formula field for us to apply a condition we can use either the following formulas:

• DECODE
• CASE

DECODE NetSuite Function

The DECODE function in NetSuite’s saved searches allows you to apply a simple equality comparison. The parameters of the function are as follows:

DECODE({field_name}, equality_result, true_response, false_response)

For example, if I have Formula (Text) result column a simple condition based on whether an employee is a Sales Rep, would be:

DECODE({issalesrep}, 'T', 'Sales Rep', 'Not Sales Rep')

Let’s explain this example briefly:

1. The first parameter is the field we want to have our condition checked, in our case it’s the Sales Rep field on an Employee Record {issalesrep} .
2. The second parameter is what you want the value of this field to be, if it’s checked we use T .
3. The third parameter is what you want to show if the condition is TRUE , here you display the text 'Sales Rep' .
4. The fourth parameter is what you want to show if the condition is FALSE , here you display the text 'Not Sales Rep' .

The DECODE function is the simplest conditional check you can perform on your result data set.

If you require more or even nested conditions then you’ll want to apply the other type of conditional form in a Formula field.

CASE NetSuite Statement

The CASE statement is the most common type of condition check in a NetSuite saved search Formula field. It provides greater flexibility in the conditional area of the CASE statement.

Here is the schema of a CASE statement in the Formula field:

CASE WHEN conditional_statement_1 THEN
  value_if_true
WHEN conditional_statement_2 THEN
  value_if_second_condition_true
ELSE
  default_result_if_no_conditions_met
END

Nested CASE Statements

If you’d like to nest conditional statements within a CASE statement you can easily do so by starting another CASE statement inside, like so:

CASE WHEN original_condition THEN
    CASE WHEN nested_condition_1 THEN
        nested_case_result_if_true
    ELSE
        nested_case_result_if_false
    END
ELSE
    original_condition_if_false
END

You can continue to nest CASE statements provided you open the CASE statement within the THEN clause of the parent CASE statement.

Conclusion

In this article you have learned how to apply conditions on your saved search results using the Formula fields. You have seen the two types of conditions you can use within your formula, namely:

1. DECODE
2. CASE

And how you can apply the right function or statement according to the complexity of the conditional check or checks you want to undertake.

You can continue to learn more about CASE statements by reading about the following similar post on nested CASE statement with aggregation.