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: This is an analytical function in NetSuite that returns the first value in a specified set of values.… Continue reading Using the FIRST_VALUE Function in NetSuite Saved Search
Tag: saved search formula
Calculating Cumulative Sum with Partitioning
This article explains how to calculate a cumulative sum with partitioning in Saved Search Formula. The provided formula demonstrates how to achieve this using the SUM function along with the OVER clause. By partitioning the data based on specific columns, such as the month of a date and a sales representative, the cumulative sum can… Continue reading Calculating Cumulative Sum with Partitioning
Saved Search Formula: Date/Time Difference in Days – Hours – Minutes – Seconds format
DAYS TRUNC({date1}-{date2}) HOURS MOD(TRUNC(({date1}-{date2})*24),24) MINUTES TRUNC(60*((({date1}-{date2})*24)-TRUNC(({date1}-{date2})*24))) SECONDS ROUND(60*((((({date1}-{date2})*24)-TRUNC(({date1}-{date2})*24))*60)-TRUNC(((({date1}-{date2})*24)-TRUNC(({date1}-{date2})*24))*60)),0) Create a formulae text field and then concatenate all the above formula using || as a result column.
How to distingush between normal item and Matrix child item in a saved search?
To distingush between normal item and matrix child item, we can use Formula(Numeric) field in the result tab of item saved search. Before applying this formula, we need to display the ‘parent’ field in the result tab. Then on saving the search we will get the results i.e 4 and 1 accordingly.
How to List Items with Name ends with (xxx)
Scenario: The User wants to get the list of items, the name of the items ends with (-MR). Create an Item saved search: Go to Lists > Search > Saved Searches > New. Select “Item” as the record type for the saved search. Give a name to the saved search and click on the “Criteria”… Continue reading How to List Items with Name ends with (xxx)
Commission calculation based on employee contribution
The user can calculate the commission payable to employees for every invoice based on the contribution percentage of employees. Create transactions saved search Go to Lists > Search > Saved Searches > New Select type as Transactions Set the title of your saved search (e.g. “Commission Calculation by Employee Contribution“). In the Criteria tab, add… Continue reading Commission calculation based on employee contribution
Show the required format for the date in the saved search result.
Under saved search results, select Formula Text. Type the formula as shown.
Saved Search Formula to Get the Decimal Part of a Number
Solution The formula to use to get the decimal places of any given number is: mod({number},1) Note: Change {number} to any field ID of any given number.
Handle commas in a saved search result from the NSCONCAT function
Recently, I had to fetch some URLs and file names stored in a custom record related to another record. The challenge was to handle the commas inserted using the NSCONCAT function.My solution was to use the string.replace() function to replace commas in the file names with ‘%2C’ and URLs before concatenating the search result and… Continue reading Handle commas in a saved search result from the NSCONCAT function
How to find Number of months between two given dates
If we want to set Yes if the Customer placed an order within last 24+ months and set No if the Customer placed an order within last 24 months in a Transaction saved search.