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.

HTH

-EG

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

January 7, 2021

Anything I wrote today about business would both feel and be inadequate. I simply can’t arrive at the words to express how I feel in this moment. So instead, here are a few helpful ...

Read More

December 20, 2020

2020 was a hundred swift kicks to the shorts and one giant kick in the ass. If you’ve lost people you love, if you’ve gotten sick or hurt, if you’ve lost your job, if ...

Read More

December 16, 2020

“I’ve been meaning to learn SuiteScript, but it’s hard to find the time …”“We’re slammed, and I just don’t have the time …”“I haven’t made the time …” All are variations of a theme ...

Read More

December 14, 2020

Last week we looked at mimicking the functionality of a REST endpoint with its own module (e.g. retrieving a User, or a list of Users, refreshing a token, etc). For complex integrations that rely on a large ...

Read More