There are times where we only care about the values on a Record where a certain field is minimal or maximal. For instance, perhaps we want the Totals of Sales Orders by Customer, but we only care about the most recent orders – in other words, where the Date field is maximal.
For situations like this, NetSuite provides us with the When Ordered By feature of Search Columns.
According to NetSuite Help:
The When Ordered By Field option provides search results that return the value for a field when the value for another field is minimal or maximal.
If you happen to be familiar with Oracle SQL, When Ordered By is the same as keep_dense_rank.
Example: What is the Amount of the Most Recent Sales Order, by Customer?
You should be able to open your browser’s console within the NetSuite UI and
run/adapt this code sample.
Note the use of join: "x" within setWhenOrderedBy; at the time of this writing, join is a required value in setWhenOrderedBy, even if your search does not require a join there. I used “x” as a non-sensical join name so it would not be confused with an actual join.
It is my hope that this is found to be a bug in the setWhenOrderedBy API and is fixed by making join optional.
HTH
-EG
This example is an excerpt of a chapter in Transaction Searches in SuiteScript.