I don’t like duplicate records; nor should you. Yet it is a common challenge in all ERP and CRM systems. CRM systems, including NetSuite, are notoriously plagued with duplicate records because lead data is often imported from third party sources.
Here is an example of how you may inadvertently create duplicate records. Suppose you are an exhibitor at a trade show and you pickup names electronically as show participants visit your booth. It is convenient for you to gather those names electronically so you don’t have to enter them into your marketing database manually one-by-one. To prepare for post-show follow up calls, you upload your visitor list to NetSuite using CSV Import. Sure enough, some of those participants were previously in NetSuite — you now have a new concern: duplicates!
This brief article will help you learn how to use NetSuite’s Saved Search technology to find which records are duplicates. A future article will discuss how to get rid of them.
Saved Search to Find Duplicates
The key to using saved search to find any duplicates is the Saved Search Summary capacities. Here is the general approach:
- Start a new Saved Search on the record type in question.
- Determine which Result columns you want to organize on to distinguish elements that make you have duplicated records. Select those columns in the result set and set the Summary Type to “Group”. For example, you may have a record where the date and location of a record should be unique; but you have two records that have both the same date and location — one record is wrong and it represents a duplicate.
- Select the InternalID column (which is always unique) and set the Summary Type to “Count”.
- Go to the Criteria tab and select the Summary Type. Use the type “Count” on the field “InternalID”. Look for any records where the result is greater than 1.
- Optionally, go to the Criteria Standard filter and remove any records that you know are not a concern.
- Run the result. The list of records you see represent duplicates that need to be cleaned up. You can drill down on the list to inspect further.
Leave me a comment if you have specific questions on how to find your duplicates.
I am looking for a formula similar to this but with a twist.
We have what are called billing elements. The billing element contains a requisition #, Date Open and Date Closed. I am trying to find all requisitions with the same requisition number but where the Date Open and Date Closed are different. So, if there are two requisitions with the same # 1234 but one has a Date Open of 1/1/14 and the other has a Date Open of 1/31/14, then I want it in the saved search. Otherwise leave it off the report. I was doing a count of the Date Closed but my issue is sometimes the Date Closed is Null because one of the records has not been updated with the date. Any assistance would be appreciated.
Hi Janine,
Here is some thinking:
1. First find all records that are possible duplicates: As this article points out, Group on requisition # and use the internal ID technique where count is greater than 1.
2. Consider using NetSuite’s NS_CONCAT function. Check out the article I wrote about how to show detail in a summary search. This column wil then illustrate some detail that makes up those duplicate records.
I am curious if you are able to get any meaningful information out of the first step and then bridge to the second step to reveal information. Finally, is this a one time “clean up” concern or an ongoing operational search?
Marty
Marty
[…] https://blog.prolecto.com/2012/10/18/how-to-find-duplicates-in-netsuite/ […]
I have the Netsuite Search that looks for leads coming in and groups them by email. It then counts “ID” and “Date Created”.
This tells me if I have a duplicate in the system if either number is greater than 1.
My problem is I can’t seem to find a way to limit my results to only those who have a “Count of ID/Date” greater than 1.”
My end goal is to have the search email when a new record is created where the count is at least 2. Thus alerting me that duplicates are being created in the system.
Hi William,
Have you tried to define the count > 1 filter under the Criteria Tab, Summary Sub Tab and then reference a Formula field of type Numeric? You may need to use a SUM function in the formula to get it right. If this doesn’t work for you, I will have to recreate situation to get the exact syntax.
Marty
I’ve been going through our dups, and the “Not a duplicate” box doesn’t seem effective. When I merge a group of duplicates, the ones marked not a duplicate, continue to appear on the report. Is there a way to make clicking that box effective so that those customer records don’t continue to appear on the report?
Interesting. The underlying field name is “hasduplicates”. It is called “Duplicates” when you do a Saved Search. Are you not able to use that field to produce your filter?
Marty
Hi
I have actually found the duplicate records by grouping it and making count >1.But I want to show all the duplicates of the group in one report .I don’t want to drill down .Is this possible ?.Thanks
Priyabrata
Have you tried working with different elements of your group by to show the duplicates?
Marty
Hi Marty
While working on Netsuite I wanted to remove duplicate columns .Is there any idea to do that .Thanks
This seems straight forward. I am not sure I understand your situation.
Marty
Is there way to find duplicate values between two columns?
Yes, use a formula field that returns true in this condition: INSTR( {field a}, {field b} ) > 0
Hi Marty
Very interesting article thanks!
I’m having an issue with duplicate record matching. We recently moved from Infusionsoft and transferred all leads through a csv import.
Now when an “old” lead completes an enquiry form on our website, Netsuite adds ONLINE 3 to the company name. (this number increases after each enquiry – currently on ONLINE 37).
I realise this isn’t a help desk but I’ve exhausted every avenue I can find, and came across your post which I thought was the solution!
I have seen something like this before. But I don’t have the specific fix. I usually resort to some simple scripts to help me get control over the situation.
I have a saved search but problem is it is returning duplicate record, I want only unique records and also I can’t apply summary on it because I am fetching it using third party integration.
If I apply summary on search in that case third party connector did not able to fetch the saved search. Please Help!!!
Thanks.
Hello Mayur,
Duplicate record searches can be challenging especially returning one row, not all the duplicates. Consider using an aggregate or analytical function to get the maximum value of the internal ID to retrieve only one row. Still, your challenge sounds like the tools you are using do not work well with summary searches. NetSuite saved search and script technoloy when using aggregates do present challenges. Consider using our Content Renderer Engine for system integration as an alternative.
Hello Marty,
I’m trying to build a search that will identify duplicates in Item Name Records, The system don’t allow us to create actual duplicates but there are many item records in my system that say
Brightway Vs. Brighway, or Item 24DS Vs Item 24 DS
So I need something that would identify similar characters in a row? I’m not sure what script or algorithm to build in.
Hello Amy,
These are challenging problems. The Like Operator is your friend here with plenty of trial and error. Once you can get matches that will find these, you can refine the syntax to help you identify these issues.
Note, our Record State Manager pattern helps evaluate and look for duplicates in the normal day-to-day business record management. This can be bolted on top of any record that is having trouble. We basically build rules that look for patterns that are similar and warn users in a way that can direct the flow.
Marty
10 years later- this post finds its way in Google and saved me hours of work. At least the third time I’ve stumbled on your great blog in the last month.
Thank you. You rock.
Thank you sir!
Marty
Hi Marty, I too came across this and it saved me hours. However, I have one use case that I can’t seem to get this to work for.
I need to identify potential duplicate vendor payments based on vendor name and payment amount. You can’t group the amount field and neither sum nor maximum seem to work. Any ideas?
Many thanks,
Simon
Hello Simon,
Consider using the to_char({amount}) plsql function to a formula text field as a way to give you the text representation. You should then be able to group by it and count by internalID to help you find the duplicates.
Marty
Hi Marty,
Thanks for your response. I failed to mention that to_char also returns the error regarding grouping (The result field Formula (Text) cannot be grouped)- it’s almost as though NetSuite knows I’m trying to bluff it. Thanks anyway and if you happen to come across anything else that might assist, please let me know. Many thanks, Simon
In case this helps anyone else, I was having trouble with getting any results to return. Then I realized that Step #2 is very important and you can only GROUP by fields that may be duplicated.
So don’t add other fields like Document Number that will make the result set unique even if the value you are trying to find duplicates on is unique.