Overview: The FIRST_VALUE function in NetSuite’s Saved Search feature allows you to retrieve the first non-NULL value from a specified set of values. This article explains how to use the FIRST_VALUE function in a Saved Search.
FIRST_VALUE(CASE WHEN {appliedtotransaction.type}='Quote'
THEN {appliedtotransaction} END IGNORE NULLS )
OVER (PARTITION BY {internalID} ORDER BY {linesequencenumber}
ASC ROWS UNBOUNDED PRECEDING)FIRST_VALUE: This is an analytical function in NetSuite that returns the first value in a specified set of values.
CASE WHEN {appliedtotransaction.type}='Quote' THEN {appliedtotransaction} END: This is a conditional statement that checks if the type of the applied to transaction is ‘Quote’. If it is, it returns the value of the applied to transaction field; otherwise, it returns NULL.
IGNORE NULLS: This clause is used with the FIRST_VALUE function to ignore NULL values when determining the first value. It ensures that the first non-NULL value is returned.
OVER (PARTITION BY {internalID} ORDER BY {linesequencenumber} ASC ROWS UNBOUNDED PRECEDING): This part defines the window for the analytical function. It specifies that the function should be calculated for each unique value of {internalID} and within each partition, the rows should be ordered by {linesequencenumber} in ascending order. The ROWS UNBOUNDED PRECEDING clause means that the window includes all preceding rows from the partition’s first row up to the current row.
In summary, the formula is used in a NetSuite Saved Search to find the first non-NULL value of the {appliedtotransaction} field, but only when the {appliedtotransaction.type} is ‘Quote’. It is applied within a specific partition defined by {internalID} and ordered by {linesequencenumber}.
Result:
