NetSuite’s IF Formula In Saved Search

Where is the IF function in Netsuite’s saved search area?

It can seem quite odd at first trying to find the standard IF function in Netsuite’s saved search area, but you are not losing your eyesight, there is no IF function in Netsuite’s formulas.

Instead of using an IF function you have two other types of functions available:

• CASE statement
• DECODE function

Let’s explore these in a little more detail for your use case.

Where Is NetSuite’s IF Formula?

The NetSuite IF formula does not exist in the Saved Search section.

Instead you need to learn how to use the CASE statement which is helpful if your usage will involve IF-ELSEIF-ELSE type logic, or the DECODE function if you have a simple logic check and want to display one result if true and another if false.

Here is the syntax for each expression.

CASE Statement

The CASE statement is syntax that is structured as follows:

CASE WHEN condition1 THEN do_this
     WHEN condition2 THEN do_that
     ELSE do_the_other 
END

Here’s what the above code means:

• CASE : this is essential as it defines the beginning of the conditional checks that will be performed.
• WHEN condition : this is where you define your logic condition.
• THEN do_something : this is where you define what to return should the condition preceding be true .
• WHEN condition_N : this is where you can continue to define more conditions.
• THEN do_something_N : this is where you attach what you want to return according to the preceding condition.
• ELSE do_else : this is the last result you would like to return should all previous conditions fail.
• END : the essential code to define the closure of the CASE statement. This is useful should you want to nest multiple CASE statements.

Nesting Multiple IF Formulas

The beauty of the CASE statement is that you can nest multiple IF conditions within the statement.

Here’s how this would look:

CASE WHEN condition_1 THEN
    CASE WHEN sub_condition_1A THEN do_this
    WHEN sub_condition_1B THEN do_that
    END
WHEN condition_2 THEN
    CASE WHEN sub_condition_2A THEN do_this
    ELSE do_that 
    END
ELSE do_the_other
END

As you can see you can have as many nested IF conditions using the CASE statement in Netsuite’s Saved Search area. However, do be mindful it can be difficult to read and you may want to use a text editor which swaps out tabs for spaces so that when the formula is pasted into your Saved Search you can read it somewhat easier.

Notice also that it’s very important to use the END clause corresponding to each CASE to ensure the proper closure of the conditions.

DECODE Function

Another simpler form of using an IF condition in your Netsuite saved search is the DECODE function .

The syntax for the DECODE function is as follows:

DECODE(expression, search_item_1, result_1, [search_item_2, result_2, ..., default_result])

This function operates very much like the SWITCH statement where you have an expression to compare against and every capture is defined in the search_item_n with its result to return in the subsequent parameter result_n . The final result is the default returning result.

Therefore, while the DECODE function can be more than an IF formula, it can be used as an IF formula if it is used as follows:

DECODE(expression, item_if_true, result_true, result_false)

This means you can insert into the DECODE function’s first parameter the value to check, and what the corresponding value of that field will be if true and then what to return if true and what to return if false.

For example, if I had a Saved Search being performed on Customer records and I wanted to display if a Customer was overdue by a certain amount then I could perform the following IF condition using a Formula (Text) field with the DECODE function:

DECODE(INSTR({entityid}, 'WD')
    0, 'Active',
    'Withdrawn'
)

What this simple DECODE function I use the INSTR() function on the Customer’s ID to check if it contains a certain string 'WD' , if it does not ( INSTR() returns 0 when it cannot find the string) it returns Active otherwise it returns Withdrawn .

IF Formula In Saved Search: Summary

As there is no IF function in a saved search with Netsuite you can use two other popular alternatives CASE and DECODE .

CASE statements allow for nesting, conditional ranges, and complex IF condition structures, whereas the DECODE function is limited but can allow for multiple precise criteria, it doesn’t allow for nesting though.