ANS-0075 · SAVED SEARCHES & FORMULAS
How to Display a Previous Row’s Result in a NetSuite Saved Search?
Leverage the LAG analytic function within a formula field to display data from a preceding record in your NetSuite saved search results.
Short answer
To reference a previous row's value in a NetSuite saved search, utilize the LAG analytic function in a formula field. This function allows you to retrieve data from a preceding record based on a specified order. Ensure your search results are ordered correctly and no summary types are applied to the columns for accurate results.
Scenario
Users often require a saved search to display a value from a preceding row within the current row's results. This is useful for comparing sequential data or tracking changes over time. For instance, a common requirement is to show the 'Letter' value from the previous record in a new 'Letter 2' column, as demonstrated in the example: Number 1, Letter A, Letter 2 (empty); Number 2, Letter B, Letter 2 A.
Solution
To achieve this functionality, the LAG analytic function can be employed within a formula field in the NetSuite saved search. This function retrieves a value from a row at a given physical offset before the current row. The formula should be structured as follows, assuming {letter} is the internal ID of the Letter field and {number} is the internal ID of the Number field:LAG({letter}) OVER(ORDER BY {number})It is crucial that the saved search rows are ordered by the field specified in the ORDER BY clause (e.g., 'Number'). Additionally, this solution assumes that no Summary Type is applied to any of the columns in the saved search. This represents the simplest form of the formula; further modifications may be necessary depending on specific display requirements.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support