🧠 Overview:
This article explains how to use a formula in a NetSuite Saved Search to flag when the total quantity sold of a specific item exceeds a set threshold. The formula returns 1 if the threshold is crossed, otherwise 0.
🧮 Formula:
CASE WHEN SUM(
CASE WHEN {item.name} = ‘Premium Laptop’
THEN {quantity}
ELSE 0
END
🔍 What This Formula Does:
Filters transaction lines for the item ‘Premium Laptop’. Sums the total quantity sold using {quantity}. Compares the total quantity to a threshold of 100.
Returns 1 if the threshold is exceeded, 0 otherwise.
⚠️ Important Limitation: Line-Level vs Summary-Level Fields
In NetSuite Saved Searches, it’s important to understand that you cannot mix line-level fields with summary-level logic (like SUM()) inside the same CASE clause improperly.
❌ Not Allowed:
CASEWHEN {quantity} > 100 THEN SUM(…)
ELSE …
END
✅ Allowed:
You must apply SUM() outside of the inner CASE, as shown in our main formula:
SUM(
CASE WHEN {item.name} = ‘Premium Laptop’ THEN {quantity} ELSE 0 END
)
This ensures:
- {quantity} is evaluated per line first,
- and SUM() is applied afterward to summarize at the saved search result level.
✅ Example Scenario:
You want to monitor sales of “Premium Laptop”. If more than 100 units are sold during the selected time period, this formula will return 1. You can use this to:
- Trigger alerts
- Filter results
- Highlight records in reporting dashboards
🛠️ Notes:
- Replace “Premium Laptop” with your actual item name.
- Adjust the threshold (100) based on your business criteria.
- Use this in a Formula (Numeric) field.
- Best used in a Transaction Saved Search grouped by criteria such as customer, item, or period.