Setting When Ordered By in SuiteScripts

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’

search.create({
        type: 'file',
        filters: [
          { name: 'internalid', operator: 'anyof', values: fileArr.map(el => el.id) },
          { name: 'field', join: 'systemnotes', operator: 'anyof', values: 'MEDIAITEM.SFILECONTENTHASH' }
        ],
        columns: [
          search.createColumn({
            name: 'internalid',
            summary: 'GROUP',
            label: 'Internal ID'
          }),
          search.createColumn({
            name: 'date',
            join: 'systemNotes',
            summary: 'MAX',
            label: 'Date',
            sort: search.Sort.DESC
          }),
          search.createColumn({
            name: 'newvalue',
            join: 'systemNotes',
            summary: 'MAX',
            label: 'Content Hash'
          }).setWhenOrderedBy({ name: 'date', join: 'systemNotes' })
        ]
      })
        .run().each(function(result) {
          let fileJson = fileArr.find(el => el.id === result.getValue({ name: 'internalid', summary: 'GROUP' }));
          if (!fileJson) return true;
          fileJson.contenthash = result.getValue({ name: 'newvalue', join: 'systemNotes', summary: 'MAX' });
          return true;
        });

.setWhenOrderedBy can be buggy in SuiteScript.Instead we can use the fomula

e.g: MAX({systemNotes.newvalue}) KEEP (DENSE_RANK LAST ORDER BY {systemNotes.date}