November 17, 2020

SuiteScript Example – When Ordered By

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.



This example is an excerpt of a chapter in Transaction Searches in SuiteScript.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Related posts

March 3, 2021

Does the physical location of a competent employee change the amount of value they can deliver for your organization? If it does, I’d be keen to hear your story. If it does not, why ...

Read More

March 2, 2021

Today a friend and former colleague of mine – who happens to now be in a development leadership role for a NetSuite partner – was lamenting the extreme difficulty of finding and hiring NetSuite ...

Read More

March 1, 2021

Software is never finished; clients are always changing and re-prioritizing. NetSuite is ever a moving target – always updating and evolving. Market forces are always shifting and shoving, forcing new demands on your business. ...

Read More