ANS-0030 · SAVED SEARCHES & FORMULAS
How to Retrieve a Field Based on Another Maximal Field in NetSuite Saved Searches?
Leverage the keep...dense rank syntax in NetSuite summary saved searches to extract specific field values tied to maximal criteria within grouped results.
Short answer
To retrieve a field's value based on another maximal field within a group in a NetSuite summary saved search, use a formula field. Enter max({b}) keep(dense_rank last order by {a}) and set the field's summary type to 'Max'. This ranks records by field 'a', keeps the highest rank, and returns the maximum 'b' value.
Scenario
Users often need to extract a specific field's value from a record that meets a maximal condition within a grouped set of results in a NetSuite saved search. For instance, a common requirement is to identify the title of the most recent task associated with each opportunity. Standard summary functions may not directly support this complex filtering and retrieval.
Solution
To achieve this in a NetSuite summary saved search, a formula field utilizing the keep...dense rank syntax can be employed. Suppose the objective is to find field b where field a is maximal within each group. The following formula should be entered into a formula field:
max({b}) keep(dense_rank last order by {a})
The summary type of this field must be set to 'Max'.
This syntax instructs the search to, within each group, rank all records by a, retain only the record(s) with the highest rank, and then determine the maximum value within that filtered group. It is important to note that if there is a tie, this will return an arbitrary value, so using fields that are unique is recommended.
For example, to find the title of the most recent task for each opportunity, the formula would be:
max({task.title}) keep(dense_rank last order by {task.startdate})
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support