Many who work in or with the accounting department have some knowledge in Excel and have probably become comfortable with various formulas, such as IF statements. Some of these Excel formulas can be easily duplicated in NetSuite Saved Searches.

Many Excel users know how to write a basic IF statement in Excel, similar to the example below. This one will give a different text message based on the dollar amount of the Amount (Debit) column.

Excel Saved Search NetSuite 1

IF statements in Saved Searches are known as CASE or CASE WHEN statements. They may look more difficult to do, but they really are not. The basic functionality is the same. The only difference is that the syntax (way the formula is written) is a little different. Below is a comparison of an Excel IF statement compared to a Saved Search CASE statement.

Excel Saved Search NetSuite 2

To add a CASE statement in a Saved Search, go to the Results tab and add a new line. Choose one of the formula options. The formula options depend on what the formula results are. So in this case, to build the formula above, choose Formula (Text) since the result is going to be one of two text messages. If the desired result is number, choose Formula (Currency) or Formula (Numeric).

Excel Saved Search NetSuite 3

Click on the Set Formula button in the Formula column to bring up the Formula Builder window:

Excel Saved Search NetSuite 4

Start the formula by typing “case when.” The CASE option could also be chosen from the Function drop-down list although sample data populated by the system will have to be deleted.

Excel Saved Search NetSuite 7

Click on the Field drop-down to find the field to use to compare.

Excel Saved Search NetSuite 8

The field will be added in the proper format with the squiggly parentheses ( {} ) around it. The field ( {debitamount} ) could also be manually keyed if known. Type the rest of the formula.

Excel Saved Search NetSuite 9

Click Set on the Formula Builder window and run the search.  The formula should return the desired result.

Excel Saved Search NetSuite 10

Just like IF statements in Excel, CASE statements can be built to be fairly complex. This is just a basic example. Play around and see what you can do!

For more information on this topic or others related to NetSuite, please contact us at [email protected] or call 855.437.7202.

By: Ryan Meyer – National NetSuite Solution Provider