I have about 400K records in opportunity object which needs a cleanup of duplicate records using a batch class. I don't want to delete the duplicate records, I want to check them as 'prospective duplicate' maintained in a custom field. Could anyone please suggest me how should I do this. I see one solution here - How do i Find and Delete Duplicates in Custom Object using SOQL or APEX however I am not sure how to do it on such a large number of records. Can someone please suggest me a solution around this?

| improve this question | | | | |
  • Have you already evaluated Salesforce's native Duplicate Management feature for this objective? It supports custom objects and can run jobs across the database. – David Reed Oct 3 '18 at 15:51
  • Yes I checked that and it was giving options for just Leads, Contacts and Accounts cleanup, and not for other objects, which is why I decided to proceed with batch class. Also, I updated by question above - I have to cleanup Opportunities object. – abc Oct 3 '18 at 15:53
  • 1
    That makes a huge difference in your solution architecture because while custom objects are supported by Duplicate Management, Opportunities are not. – David Reed Oct 3 '18 at 15:56
  • Yeah that's right. Do you think this is achievable using batch class? – abc Oct 3 '18 at 15:57
  • Is this a one-time deduplication or an ongoing process? Have you looked in AppExchange at apps like DemandTools or Cloudingo? – David Cheng Oct 3 '18 at 16:41

Many duplicate management solutions work by calculating a unique key for each record, which incorporates the critical data points that are to be compared between records to find duplicates. They are then able to group the records by this key to find duplicate sets.

This works for many types of criteria, like "email address and last name and first initial", for Contacts. A Fred Jones (fred@gmail.com) might have a unique key of "fred@gmail.com/jones/f", for example - aggregating those data points into a key. That would then match on a Frederick Jones (fred@gmail.com), who would also have "fred@gmail.com/jones/f" as their unique key.

One way to approach this, and there are many, would be to write a batch class to generate and populate such a key across all Opportunities in your org into a new External Id custom field. (It's important for it to be External Id so that the field is indexed). Your Opportunities might, for example, have a unique key made up of the Account Id, the Amount, and the Close Month - "001000000000000/100.45/12", for example.

Then, you can export this data into a spreadsheet, where you'll have an easier time manipulating it to find the duplicates. Or you can run a query like this to find duplicate sets:

SELECT count(Id), Duplicate_Key__c FROM Opportunity GROUP BY Duplicate_Key__c HAVING count(Id) > 1

and then drill down on the individual duplicate groups to handle them in the way that your organization deems most appropriate.

This is one option. There's a lot of complexity in duplicate management, and the details are going to be fairly dependent upon your organization's specific requirements and duplicate criteria. You may also wish to evaluate a 3rd-party tool like DemandTools.

| improve this answer | | | | |
  • DemandTools isn't cheap, but it's worth every penny when it comes to stuff like this. They just changed their license model, so it's a little more affordable now. (And, no, I don't work for them!) – Shane Steinfeld Oct 3 '18 at 17:21
  • I used it when I worked for a nonprofit and got it for free (still bitter that Validity ended that grant). Very good product, somewhat steep learning curve. – David Reed Oct 3 '18 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.