0

My requirement is to find the duplicate lead for the current lead by matching against all other leads in the database. something like this:

http://sqlfiddle.com/#!4/439dd/1

I am not sure how the same can be done using SOQL. Can somebody help me on this?

EDIT:

Just to clear the confusion. I am not looking for a way to prevent duplicates in the system. My goal is to find duplicate leads for a given lead in a batch job. I am going to use this query in the batch job execute method.

  • Dear downvoter, This is the second question you downvoted in two days, not sure what is your problem. – javanoob Sep 1 '15 at 10:47
2

The example you provided

INNER JOIN Leads SecondTable ON SecondTable.FirstName = firstTable.FirstName AND SecondTable.LastName = FirstTable.LastName AND SecondTable.Email = FirstTable.Email

is a match on all three fields so that is easy....

  1. Make an external ID field that concatonates the First and lastName fields along with the email and set it to a unique index. Name it say dupIdx

Then in the lead trigger it would be something like:

trigger findDuplicates on lead(after Insert){

   Map<String,Lead[]> duplicates = New Map<String[]>();

   for(trigger.new l : lead){
       if(dulicates.containsKey(l.dupIdx__c))
           duplicates.get(l.dupIdx__c).add(l); //catches dupes in the trigger
       else
           dulicates.put(l.dupIdx__c,New lead[]);
   }

   for(Lead l : [Select ID, dupIdx__c From Lead Where dupIdx__c IN :duplicates.keyset() AND ID NOT IN :trigger.new]){
      duplicates.get(l.dupIdx__c).add(l);
   }

   //now run through trigger again checking for the dupes and add errors as appropriate


} 

If you want to then prevent duplicates, you can make the field unique and case insensitive and then you do not need the trigger at all.

  • Can I do this without creating an extra field on the lead object? I want to do this for selected set of leads in a scheduled batch. – javanoob Aug 14 '15 at 20:38
  • Actually I want to use this query in this batch job. Here is my other question salesforce.stackexchange.com/questions/88105/… – javanoob Aug 14 '15 at 20:41
  • I take the time to answer your question only to find out that your question is not really the question you wanted to ask....???...And yes you could do this in a batch. Yes you could do it without the field but it introduces unnecessary complexity..... – Eric Aug 14 '15 at 20:53
  • No Eric..I am really sorry..I am still looking for query to get duplicate leads but I just don't want to create extra field on the lead object...My sincere apologies for any miscommunication – javanoob Aug 14 '15 at 20:54
  • Why would you have a problem creating the field? it takes A LOT of the work off of the code with the field present. Otherwise you will have to query the entire database and evaluate OR set a scope size of 1 in the batch and perform a query for each lead..... – Eric Aug 14 '15 at 20:57

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.