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 [email protected] or call 855.437.7202.

By: Ryan Meyer – National NetSuite Solution Provider

Is it possible to bring fields that are listed in the system notes section on a record into the expression? For example, when updating or adding more items to a purchase order, I want to have an expression that compares the current total of the purchase order compared to the previous totals. Perhaps putting in some logic that says if the increase less than *% then let’s not give it a “YES” but if it is more than * percentage that it needs to be flagged.

Chris:

The answer is yes. For the most part, as long as a field can be displayed in a Saved Search, it can be used in a formula on a Saved Search. Your specified use case should be possible.

Hi There, I am trying to have a date formula populate based on a text field name.

Example: If published field says PIC add 182 days to date created, ELSE add 364.

This is not in a saved search, it is on a custom record and I am trying to have this auto populate based on a custom list. I want it to view that field and if that field reads X, add 182 days to date created, if not, add 364 days to date created.

Let me know if you have any thoughts on this! I’m curious to know if this is possible.

Thanks,

Amber

You can use the CASE statement functionality the same way in a custom field like I’ve outlined above in searches. That said, you can only do that if you’re able to select the field you want to base your CASE statement off of. In other words, if the way you’ve set up the Custom Record and Custom List hasn’t created a link between the two, you’re not going to be able to do it. If I’m understanding correctly, your formula would look something like “case when {customlist.field}=’PIC’ then {datecreated} + 182 else {datecreated} + 364 end.”

Is it possible to filter saved search with few customer name/ID?

Yes there are a couple different ways you can do this. If you want to specify the customer names, you could do something like this: case when {entityid} in (‘customer1′,’customer2’) then ‘yay’ else ‘nay’ end where customer1 and customer2 would have to be the exact names (case specific) of the customers. If you’re just looking for customers that are similar, you could do something like: case when {entityid} like ‘cust%’ then ‘yay’ else ‘nay’ end where the % sign is your wildcard representing any group of characters. In that example, any customers that began with the characters “cust” would be brought in.

Thank you so much for your reply. i will try the solution provided by you.

Hi,

Is it possible to write CASE formulas with a CONTAINS or NOT CONTAINS condition?

Hi Kerry,

Thanks for your question. I don’t believe “contains” or “not contains” are functions that Saved Searches recognize. However, you can use “like” or “not like” which accomplishes basically the same thing. Example if you want to bring in all customers who have ABC in their name: case when {name} like ‘%ABC’ then ‘has ABC’ else ‘doesnt have ABC’ end. Or if you want to exclude those: case when {name} not like ‘%ABC’ then ‘doesnt have ABC’ else ‘has ABC’ end.