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.

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

1 Answer 1

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.

11
  • 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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 at 20:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .