If you know it in Excel, you know it (mostly) in NetSuite Saved Search

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.

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.

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).

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

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.

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

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.

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

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 erp@rsmus.com or call 855.437.7202.

By: Ryan Meyer – National NetSuite Solution Provider

Ryan Meyer