ANS-0116 · SAVED SEARCHES & FORMULAS
How to Filter NetSuite Transaction Searches by Customer Name Range Using Formulas
Learn to implement advanced formula-based filters in NetSuite SuiteScript to precisely narrow down transaction search results by a specified alphabetical range of customer names.
Short answer
To filter NetSuite transaction searches by a range of customer names, utilize a formulatext search filter. This involves constructing a SQL CASE WHEN formula that evaluates UPPER(SUBSTR({customer.companyname},0,length)) against your defined start and end range strings. This method allows for flexible, character-length independent range filtering within SuiteScript.
Scenario
Users often need to refine NetSuite transaction searches to include only customers whose names fall within a specific alphabetical range. Standard search filters may not offer the flexibility required for dynamic, character-length independent range filtering, especially when the start and end points of the range can vary.
Solution
To achieve a flexible customer name range filter in a NetSuite transaction search, a formulatext filter can be implemented using SuiteScript. This approach leverages SQL functions within a formula to compare substrings of the customer's company name against specified start and end range values. First, define the custNameStartRange and custNameEndRange variables as string values representing the beginning and end of the desired name range. Then, construct the formula string as follows:
var formula = "CASE WHEN UPPER(SUBSTR({customer.companyname},0," +custNameStartRange.length + ")) > '" +custNameStartRange.toUpperCase() + "' AND " +"UPPER(SUBSTR({customer.companyname},0," + custNameEndRange.length +")) < '" + custNameEndRange.toUpperCase() + "' THEN '1' ELSE '0' END";Finally, add this formula as a formulatext filter to your search. In SuiteScript 2.x, this is done using search.createFilter:
filters.push(search.createFilter({name: 'formulatext',operator: 'is',values: ['1'],formula: formula}));This method allows for filtering based on any number of characters for each side of the range, providing a robust solution for dynamic customer name range searches.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support