ANS-0012 · SAVED SEARCHES & FORMULAS
How to Retrieve the First Line Item from Multiple NetSuite Transactions
Utilize the DENSE_RANK analytic function in NetSuite saved searches to accurately extract specific line items from grouped transaction data.
Short answer
To return the first line from multiple transactions in NetSuite, configure a saved search with mainline = F and group by internal ID. Add formula fields using MAX({field}) KEEP(DENSE_RANK FIRST ORDER BY {line}) for each desired result, ensuring the summary type is set to MAX. This method effectively isolates the initial line item.
Scenario
Users often need to extract specific line items from transactions, particularly the very first line, when analyzing data across multiple transactions in NetSuite. Standard saved search results typically display all lines, making it challenging to isolate a single, primary line item for reporting or analysis.
Solution
The DENSE_RANK analytic function can be employed to achieve this. Filters: Begin by adding the necessary filters to the saved search. Typically, this will include setting mainline = F. Results: In the Results subtab, it will be necessary to group the search by internal id. For each desired result field, add a formula field and set its summary type to MAX. An example of such a formula is: Max({job}) KEEP(DENSE_RANK FIRST ORDER BY {line}) This approach allows for selecting which line to keep based on various criteria. For instance, a field different from {line} could be used for ordering. To retrieve the last line instead of the first, simply change FIRST to LAST in the formula.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support