This article is relevant if you need to create a NetSuite saved search based on internal IDs or if you want to populate a NetSuite static group list.
Background
In a recent effort to reach out to our existing contacts in the NetSuite database, we identified the contacts using a combination of saved searches and some spreadsheet work.
While popular third-party services are available, using NetSuite’s campaign capabilities allows for targeted and trackable email communications. To execute this, a “Group” list is required, which can be accessed via the Lists > Relationship > Groups menu.
NetSuite offers two types of groups: Dynamic and Static. While I generally prefer Dynamic groups, Static groups allow you to specifically target records for any reason.
In our case, we needed to create a NetSuite Static Group List of type Contact.
An Approach to Building a Static NetSuite Group List
When dealing with large numbers, adding contacts to a Group list one by one through the user interface is impractical. In our case, we needed to target over 500 contacts from our NetSuite database.
Although NetSuite offers a CSV import option, I chose to use the Mass Update technique instead. This approach allows you to update the list in one go—each time you run the mass update, it removes the existing records and replaces them with new ones based on your target criteria.
How to Build a Saved Search Using a Large Number of NetSuite Record IDs
Since the Mass Update tool relies on the Saved Search criteria builder, the technique described here can be applied to any Saved Search operation where you need to target records by their internal ID.
The key is to use a formula numeric lookup. While a CASE statement could work, the DECODE Oracle PLSQL formula offers the same functionality. In this approach, if the internal ID is found, the formula returns a 1 (indicating true).
It’s important to note that NetSuite’s formula fields have a limit on the amount of string content they can handle. To work around this, we use saved search expressions to create an OR clause, breaking the search into smaller chunks. With the DECODE statement, you can generally handle about 250 rows before risking an overload of string data.
Click on the images for a clearer understanding of this technique.
Similar Techniques to Build Saved Searches Based on NetSuite Record IDs
While continuing to be relevant today, I previously wrote articles on this topic to help you expand your thinking:
2013: How To: NetSuite Saved Search: Look Up Records from Another List
2017: How to: NetSuite Saved Search Lookup By List of InternalIDs
Here is a link to an Excel spreadsheet to help you build your string generator.
Drive NetSuite by Understanding Oracle Database Constructs
While our firm now extensively uses SuiteQL (SQL) for many tasks—leveraging our client-based, license-free tools (see my article Render NetSuite SQL Queries Like Saved Searches for an example)—the key to mastering advanced search capabilities in NetSuite is recognizing that its underlying database is Oracle.
Additionally, it’s crucial to understand that the NetSuite User Interface and the SuiteScript platform add a logic layer around the database to protect the system from critical errors. By understanding these constructs, we can creatively push closer to the database, fine-tuning our queries to get the precise desired results.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. Perhaps you are that NetSuite individual who desires to work within a best-in-class NetSuite Systems Integrator, and you want to be recognized for your hard-earned talents. If that resonates, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
