This article is relevant if you have duplicates in NetSuite and you need a way to discover them.
Background
A reader of the blog sent me a request to understand how to find NetSuite transaction duplicates that are based on the amount. Duplicates are generally troublesome. The most common place we find duplicates is in lead and customer record management. We see duplicates less so in our transaction work. Yet, in general, our work to effectively use the NetSuite database is thwarted by duplicates so it’s a good topic to cover.
There are two ways to think about duplicate management:
- Preventive: develop practices that avoid them in the first place. Practices to avoid duplicates demand care and careful consideration. Generally, it’s best to start with a centralized individual within a department function to be responsible for the care of duplicates. People are first — then technology can be used to help promote the desired practice.
- Detection: look for duplicates in the environment so you can act on them. That is the focus of this article.
Basic Pattern for NetSuite Saved Search Duplicate Detection
Each duplicate situation is unique. The reader asked a relatively simple question because looking for a duplicate based on transaction amount is effectively an easy lookup match. However, if we were looking for duplicates in contact names, this is more demanding because the duplicates are likely caused by misspellings. In any case, we use this as an opportunity to learn the basic Saved Search duplicate pattern in NetSuite:
Prepare Saved Search Summary Results for Grouping and Counting
The first thing to do is to prepare a search where your data is located. In our example, I performed a transaction search that narrowed in on “Check” types as I knew our Prolecto Resources Systems Integration NetSuite database had natural duplicates. Two key fields are needed here:
- Grouping: group on the column that produces the duplicate. The amount field is easy. But a field like contact names with misspellings may require you to produce formulas to get the values to be the same.
- Count: count on something unique. Generally, the internal ID, representing the unique key in the database, is almost entirely reliable.
Click the image to see an example.
Prepare Saved Search Summary Criteria for Counting Greater than 1
Once you see the results are aggregating (summarizing) right, refine your saved search via the Criteria section. Use the Summary section and select the same attribute you used for the Count. Generally, this is the internal ID. Make the criteria be where the count is greater than one. More technical readers will understand that this is the “Having” clause in a SQL Aggregate search.
Click the image to see an example.
Format Duplicate Results and Drill Through to Detail
Once you have the results looking right, it may be helpful to sort and bring forth additional information. In the example I produced (click on the image above), I brought forth the date using both minimum and maximum as it has no effect on the duplicate search algorithm. NetSuite’s native drill through will take you to the records that suggest the duplicates. At that point, you have the opportunity to act on the records.
Work with Expert NetSuite Professionals
If you are finding that you simply need more intellectual capacity to help you with your NetSuite driven endeavors, then perhaps it makes sense to consider developing a relationship with a set of professionals who hold high standards for care while possessing best practices and knowledge on how to get the most out of your NetSuite configuration.
If you found this article meaningful, feel free to get notifications of new articles as I publish them. If you would like more help with your NetSuite Saved Search challenge, 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
