SuiteScript Example - When Ordered By

Created: November 17, 2020

There are times when 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?

/**
 * Retrieves the Total on the most recent Sales Order for each Customer
 */
require(['N/search'], (s) => {
  s.create({
    type: s.Type.SALES_ORDER,
    filters: [
      ['mainline', s.Operator.IS, true]
    ],
    columns: [
      { name: 'entity', summary: s.Summary.GROUP },
      s.createColumn({
        name: 'totalamount',
        summary: s.Summary.MAX
      }).setWhenOrdereredBy({ name: 'trandate', join: 'x' })
    ]
  })
  
  function printOrder (result) {
    const name = result.getText({ name: 'entity', summary: s.Summary.GROUP })
    const total = result.getValue({ name: 'totalamount', summary: s.Summary.MAX })
    console.log(`${name}'s most recent order: ${total}`)
    return true
  }
})

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.