ANS-0103 · SAVED SEARCHES & FORMULAS
How to Filter NetSuite Saved Searches Using a CASE WHEN Summary Formula?
This guide explains how to configure a NetSuite saved search to identify records where the count of two distinct team member types differs, leveraging a summary filter.
Short answer
To filter a NetSuite saved search based on a discrepancy between two counted fields, apply a Summary filter. Configure this filter as 'COUNT - Greater than 0' and use a 'Formula (Numeric)' with the expression: CASE WHEN COUNT({salesteammember}) ! COUNT({partnerteammember}) THEN 1 ELSE 0 END. This identifies records where the counts are unequal.
Scenario
Users often need to identify records in NetSuite where the number of associated team members from different categories does not match. For instance, a scenario might require finding records where the count of sales team members is not equal to the count of partner team members. This necessitates a saved search capable of comparing aggregated values.
Solution
The solution involves configuring a Summary filter within the NetSuite saved search. This filter should be set to evaluate a formula that checks for inequality between the counts of the two relevant team member fields. Add a new filter under the 'Summary' tab. Set the filter criteria as 'COUNT - Greater than 0'. For the 'Formula (Numeric)' field, input the following exact expression: CASE WHEN COUNT({salesteammember}) ! COUNT({partnerteammember}) THEN 1 ELSE 0 END. This configuration ensures that only records where the count of sales team members is not equal to the count of partner team members are returned by the saved search.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support