ANS-0189 · SAVED SEARCHES & FORMULAS
How to Use DENSE_RANK for Transaction Line Item Data in NetSuite Searches
Leverage SuiteScript 2.x and SQL formulas to extract specific ranked information from transaction line items.
Short answer
To retrieve ranked transaction line item data, define custom search columns using SuiteScript 2.x's N/search module. Employ search.createColumn with aggregate formulas incorporating DENSE_RANK (an Oracle SQL extension) to group and rank items by criteria like transaction date.
Scenario
Users need to extract specific details from transaction line items, such as the latest transaction ID, date, quantity, or rate for each item, based on a ranking criterion like the transaction date. This requires advanced search capabilities beyond standard NetSuite search filters.
Solution
To achieve this, a SuiteScript 2.x search must be constructed with specific column definitions. The solution involves using search.createColumn to define each desired output column, incorporating aggregate functions with DENSE_RANK to rank and select the relevant data. The keep(dense_rank last order by {trandate}) syntax, an Oracle SQL extension, is employed within the formula to select the appropriate value based on its rank by transaction date.
The following SuiteScript 2.x code snippet illustrates the column definitions required:
var columns = [];
columns.push(search.createColumn({name: 'item', summary: search.Summary.GROUP}));
columns.push(search.createColumn({name: 'formulatext', summary: search.Summary.MAX, formula: "max({tranid}) keep(dense_rank last order by {trandate})"}));
columns.push(search.createColumn({name: 'formuladate', summary: search.Summary.MAX, formula: "max({trandate}) keep(dense_rank last order by {trandate})", sort: search.Sort.ASC}));
columns.push(search.createColumn({name: 'formulanumeric', summary: search.Summary.MAX, formula: "max({quantity}) keep(dense_rank last order by {trandate})"}));
columns.push(search.createColumn({name: 'formulacurrency', summary: search.Summary.MAX, formula: "max({rate}) keep(dense_rank last order by {trandate})"}));Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support