CASE WHEN Formula in NetSuite Explained
The CASE WHEN Formula in NetSuite is definitely one of the most useful formulas that can be applied to Saved Searches.
If you ever come across it, you would know that One of the notable features of NetSuite is its ability to provide custom formula fields, which can be tailored for specific business needs.
There are plenty of Formulas that can be applied to different scopes but the CASE WHEN Formula in NetSuite is amongst my favorites.
In this article, we’ll learn the syntax and how to use the formula and I will share 3 different ways you can use it for your needs. Let’s dive in!
What is the CASE WHEN Formula
At its core, the CASE WHEN formula is a conditional statement, quite similar to the “if-then-else” logic found in traditional programming. In NetSuite, it allows for the evaluation of one or more conditions and returns a value when the first condition is met.
Such functionality is quite a powerful resource to cope with specific needs of returning different results based on the data analyzed.
Syntax of "CASE WHEN" Formula in NetSuite
Let’s now analyze what the syntax of the formula looks like:
CASE WHEN [condition] THEN [result_when_true]
WHEN [another_condition] THEN [result_when_another_true]
... ELSE [result_when_all_conditions_are_false]
END
Key Points
We can summarize the key points of the formula this way:
- General Structure: The Formula usually respects this structure:
- Starts with a
CASE WHENeTHENfor the first condition - For the following conditions, many
WHEN–THENwill be listed - An
ELSEstatement is included to cope with the eventuality of no conditions being met ENDclause is required at the very end to “close” the formula.
- Starts with a
- Multiple Conditions: The
CASE WHENformula can test multiple conditions in sequence. - First True Condition: The formula returns the value for the first true condition it encounters. If no conditions are true, it resorts to the
ELSEclause (if provided). - No ELSE: If there’s no
ELSEpart and no conditions are true, the formula returnsNULL.
1. Case When Formula (Text) in Saved Search - Displaying the logo of the carrier in your Sales Order
When you are processing Sales Orders, your dispatch team might be happy to realize who is the carrier associated with the shipment quickly.
Supposing that you have two files saved in your cabinet with logos called DHL.png and FedEx.png, you can work out something visual by adding the logo of the careers straight away next to the results of a Saved Search.
Important note here: make sure to use a Formula Text Field to have the desired results.
CASE
WHEN {shipmethod} = ‘DHL’ THEN ‘<div><img src="”DHL.png”" width="”75px”" /></div>’
WHEN {shipmethod} = ‘FedEx’ THEN ‘<div><img src="”FedEx.png”" width="”75px”" /></div>’
ELSE ‘<div> </div>’
END
In this way as you can see from the result you will have a super quick insight on which carrier is associated with the Sales Order.
2. Using Case When with Formula(Numeric)Field in the Filter Criteria of a Saved Search
Another fantastic opportunity is to use the CASE WHEN statement in the filter criteria. Once again, NetSuite comes up with brilliant opportunities to support your business needs.
Here is the Scenario: Let’s suppose that you want to filter out from a saved search containing all the transactions, only the ones that are sales orders and with status = “Billed”.
Head over to the criteria subtab and insert a Formula (Numeric) Field. In the Formula Field Add the following:
CASE WHEN {type}='Sales Order' AND {status}='Billed' THEN 1 ELSE 0 END
Then make sure to set the formula numeric value “equal to” 1
This will return a list of only billed sales orders amongst all the other transactions.
Note that this can be also achieved with by inserting all the filters in the criteria SubTab one by one.
3. Create Groups and Aggregation for filtering option
Let’s consider this example:
You have several locations distributed in the biggest cities in the world and you want to group them into their respective region (EMEA, APAC, NAOM, LATAM) without introducing a custom field in the location record.
You can eventually proceed this way:
CASE
WHEN {location} IN (’London', 'Amsterdam’,’Paris’,’Milan’,’Barcelona’) THEN ‘EMEA’
WHEN {location} IN (’Melbourne', 'Sydney’), THEN ‘APAC’
WHEN {location} IN (’San Diego’,’Miami’,’Chicago’,’Washington’) THEN ‘NOAM’
WHEN {location} IN (’Rosario', 'San Paulo’,’Buenos Aires’,) THEN ‘LATAM’
ELSE 'Not Assigned'
END
4. Classifying Sales Orders based on their Gross Margin
Scenario:
You have a list of open sales orders for which you have a custom field that calculates the gross margin as:
Gross Margin (GM) = ((Rate – Est.Cost)/Rate)
for each and every single item listed in the sales orders. Now, the idea is to evaluate the GM at the line level and flag them as low, normal, or high GM items with a CASE WHEN formula in NetSuite Saved Search.
Given the Brackets:
- GM<20% “LOW GM”
- GM between 21% and 50% “Normal GM”
- GM > 51% “High GM”
the formula will look like this:
CASE
WHEN {grossmargin} < 20 THEN 'LOW GM'
WHEN {grossmargin} BETWEEN 21 AND 50 THEN 'Normal GM'
ELSE 'High GM'
END
Tips that you cannot miss
Case When Statement is definitely one of the most versatile formulas that you can use in your daily operations however, it is also one of the trickiest ones. Here are some tips to help you debug your formula in case you encounter errors:
- Make sure to have the “Show Internal ID” preferences turned on. You can do that by hovering the home button in your dashboard and clicking on Set Preferences. The checkbox is located on the right side of the screen, under the default section.
This will ensure that you can see the IDs of the standard NetSuite fields and the custom ones you have created.
- the CASE WHEN Statement will reference the internal ID of the fields. If you are using custom fields, make sure to reference them correctly to avoid encountering errors like “the field is not found”.
- Make sure to check and follow the correct spelling and spacing. A good practice is to create the formula in the field editor in one line at first. Then once you have validated the spelling and the spacing, you can slit it in more lines.
- Make Sure to consider whether or not you want to include the “main line” to the results. In case you don’t want it, just go ahead and include a filter in the criteria with “Main Line” = False.
- Don’t forget to add the END line
Conclusioni
The CASE WHEN Formula in NetSuite is a resourceful tool to get complex requirements done.
This SQL Statement is one of the most useful conditional expressions widely used in database systems like Oracle, SQL Server, MySQL, PostgreSQL, and many others.
However, when using it, make sure to check the syntax used in your environment as it might slightly differ from the one reported in this post, which specifically reflects NetSuite syntax.
Now it’s your turn, what is the most successful application you managed to use the CASE WHEN Statement? Have you found any other interesting applications for the CASE WHEN formula in NetSuite yet? Please make sure to share it in the comments.
