How to Apply Advanced Filtering with Summary Formulas in NetSuite Saved Searches

To implement additional filtering on a result set, leverage the 'Summary' subtab within a saved search. This approach allows for advanced conditional logic to refine the data presented. Navigate to the 'Criteria' tab of the saved search and then select the 'Summary' subtab. Configure the following settings: Summary Type: Maximum, Field: Formula (Numeric), Description: is 1. The formula for this field, designed to compare specific values, is as follows. Note that while the general structure of using summary types with formulas is supported, direct use of window functions like DENSE_RANK() within 'Criteria' formulas may have limitations in current NetSuite versions, and alternative approaches might be necessary depending on the specific NetSuite environment and desired outcome. The formula to be used is:

DECODE(max({systemnotes.name}) keep(dense_rank last order by {systemnotes.date}), max({custbody_employecreatingje}), 1, 0)

This formula evaluates whether the maximum system note name, ordered by the last dense rank based on date, matches the maximum value of the custom body field custbody_employecreatingje. If they match, it returns 1; otherwise, it returns 0, effectively filtering the results based on this comparison.