+ Start a Discussion
omateomate 

Setup Conditional Logic to Update or Insert a Record

This is my first attempt at writing a Salesfroce trigger so please bear with me.
 
The company I work for has three different brands that all use the same Salesfoce account.  Each brand has several sales reps that cross-sell different products within their specific brand but DO NOT cross-sell between brands.  It is important that each sales rep within each brand maintains ownership of their lead whenever a new form submission comes in and the Principle Interest matches their brand.   I am trying to write an Apex trigger that will do the following when a new form submission comes in.

1.) Check for a duplicate email address in Salesforce.
2.) If a duplicate email address is found it will then compare the Principle Interest of the record currently in the database with the Priniciple Interest of the incoming form.
3.) If the Principle Interests match then the current record is updated in the database and the lead owner is left unchanged.  If they don't match then a new lead record is created using the information from the form submission and a new lead owner is assigned.

I think I have # 1 and #2 taken care of but #3 is givining me a lot fo difficulty.

This is the trigger I have written so far.

trigger SetLeadOwner on Lead (before insert) {
   for (Lead myLead : Trigger.new) {
       
            List<Lead> dupesLead = [
                SELECT Id, Principle_Interest__c
                FROM Lead
                WHERE Email = :myLead.Email
            ];
       
         Set<String> BrandOneSet = new Set<String>();
                List<String> BrandOneList = new List<String>();
                BrandOneList.add('PrincipleInterest1');
                BrandOneList.add('PrincipleInterest2');
                BrandOneList.add('PrincipleInterest3');
            BrandOneSet.addAll(BrandOneList);
           
            Set<String> BrandTwoSet = new Set<String>();
                List<String> BrandTwoList = new List<String>();
                BrandTwoList.add('PrincipleInterest4');
                BrandTwoList.add('PrincipleInterest5');
                BrandTwoList.add('PrincipleInterest6');
                BrandTwoList.add('PrincipleInterest7');
                BrandTwoList.add('PrincipleInterest8');
                BrandTwoList.add('PrincipleInterest9');
            BrandTwoSet.addAll(BrandTwoList);
           
            Set<String> BrandThreeSet = new Set<String>();
                List<String> BrandThreeList = new List<String>();
                BrandThreeList.add('PrincipleInterest10');
                BrandThreeList.add('PrincipleInterest11');
                BrandThreeList.add('PrincipleInterest12');
            BrandThreeSet.addAll(BrandThreeList);
        
        if(myLead.email != null){
            if (dupesLead.size() == 0) { // check for duplicate emails.  if none found, create new lead.
                // insert myLead;
            } else if (dupesLead[0].Principle_Interest__c == myLead.Principle_Interest__c) {
                update dupesLead[0];
            } else if (BrandOneSet.contains(myLead.Principle_Interest__c) && BrandOneSet.contains(dupesLead[0].Principle_Interest__c)) {
                update dupesLead[0];
            } else if (BrandTwoSet.contains(myLead.Principle_Interest__c) && BrandTwoSet.contains(dupesLead[0].Principle_Interest__c)) {
                update dupesLead[0];
            } else if (BrandThreeSet.contains(myLead.Principle_Interest__c) && BrandThreeSet.contains(dupesLead[0].Principle_Interest__c)) {
                update dupesLead[0];
            } else { // What happens if the Principle Interests don't match up
                // insert myLead;
            }
        } // End (myLead.email != null)
} // End (Lead myLead : Trigger.new)
}

Any help would be greatly appreciated.

Thanks,
Eric

Best Answer chosen by omate
Blake TanonBlake Tanon
  • If the email address for A and B do not match, then create a new record
    • This is met - you don't need to code a DML statement here
  • If the email address for A and B match but the principle interests do not, then create a new record
    • This is met - you don't need to code a DML statement here
  • If the email address and principle interests for A and B match, then update B
    • see below, this is where you need DML statements
For 3, the reason I added it to a list for deletion is because you wanted to not insert the record - but it's already been created so we have to remove it.  So the next question is, before we delete it what do we want to copy from B to A?  Keep in mind you probablt don't want to overwrite some fields, so try something like this...

list<lead> deleteList = new list<lead>();//these are only new records (b)
list<lead> updateList = new list<lead>();//these are only existing records (a)

//loop through leads that have emails in your set
for(lead a: [SELECT id, Principle_Interest__c FROM lead WHERE email in: emailSet.keySet()]){
  if(emailSet.get(l.email) != null){
   lead b = new lead();
   b = emailSet.get(l.email);
   if(a.Principle_Interest__c == b.Principle_Interest__c){

    //lets update A with some items from B
    //Always update the phone number from b --> a if it is not null on b
    if(b.phone != null)a.phone  = b.phone;

    //update the email from b --> a ONLY if a is null and b is not, so not overwrite a
    if(b.email != null && a.email == null)a.email = b.email;

    //now, add a to the list to be updated with new values.
    updatelist.add(a);

    //now, lets get rid of B
    deleteList.add(b);
   }else{

    //do something when email is the same but Principle_Interest__c is not
  
   }
  }
}

if(deleteList.size() > 0){
    delete deletelist;
}
if(updatelist.size() > 0){
  update updateList;
}


All Answers

Blake TanonBlake Tanon
I think the below will work, 

trigger SetLeadOwner on Lead (after insert) {

//first problem, you don't want to put a query inside of a loop it'll hit limits quick.

//create a map to hold emails/lead, maps can't hold dup values so you won't run the same search twice
map<string,lead>emailSet = new map<string,lead>()

//put emails into set
for(lead l:trigger.new){
  if(l.email != null)emailset.put(l.email,l);
}

list<lead> deleteList = new list<lead>();

//loop through leads that have emails in your set
for(lead L: [SELECT id, Principle_Interest__c FROM lead WHERE email in: emailSet.keySet()]){
  if(emailSet.get(l.email) != null){
   lead compLead = new lead();
   compLead = emailSet.get(l.email);
   if(l.Principle_Interest__c == compLead.Principle_Interest__c){

    //delete new lead if Principle_Interest__c is same???
    deleteList.add(compLead); // put new lead into list to insert later

   }else{

    //do something when email is the same but Principle_Interest__c is not
    
   }
  }
}

if(deleteList.size() > 0){
    delete deletelist;
}

}
omateomate

Thanks for the quick response Blake.

I am not sure the code you provided is doing what I am looking for.  If i understand everything correctly when a new form submission comes through a new lead record, will be created.  That new record is then deduped against the current database and if there is a match it wil be deleted. Is that correct?

I am trying to avoid deleting records if possible and would really prefer to only update or create a record as needed.  The problem I see with creating and deleting records is that the Lead Owner has to remain the same if the email address & principle interest combination already exists in the database..Currently when a new lead is created it randomly gets assigned out to the next available Lead Owner in the queue.

Example process I am trying to implement:
A = incoming form submission
B = record currently in the database

If the email address for A and B do not match, then create a new record
If the email address for A and B match but the principle interests do not, then create a new record
If the email address and principle interests for A and B match, then update B

 

Blake TanonBlake Tanon
  • If the email address for A and B do not match, then create a new record
    • This is met - you don't need to code a DML statement here
  • If the email address for A and B match but the principle interests do not, then create a new record
    • This is met - you don't need to code a DML statement here
  • If the email address and principle interests for A and B match, then update B
    • see below, this is where you need DML statements
For 3, the reason I added it to a list for deletion is because you wanted to not insert the record - but it's already been created so we have to remove it.  So the next question is, before we delete it what do we want to copy from B to A?  Keep in mind you probablt don't want to overwrite some fields, so try something like this...

list<lead> deleteList = new list<lead>();//these are only new records (b)
list<lead> updateList = new list<lead>();//these are only existing records (a)

//loop through leads that have emails in your set
for(lead a: [SELECT id, Principle_Interest__c FROM lead WHERE email in: emailSet.keySet()]){
  if(emailSet.get(l.email) != null){
   lead b = new lead();
   b = emailSet.get(l.email);
   if(a.Principle_Interest__c == b.Principle_Interest__c){

    //lets update A with some items from B
    //Always update the phone number from b --> a if it is not null on b
    if(b.phone != null)a.phone  = b.phone;

    //update the email from b --> a ONLY if a is null and b is not, so not overwrite a
    if(b.email != null && a.email == null)a.email = b.email;

    //now, add a to the list to be updated with new values.
    updatelist.add(a);

    //now, lets get rid of B
    deleteList.add(b);
   }else{

    //do something when email is the same but Principle_Interest__c is not
  
   }
  }
}

if(deleteList.size() > 0){
    delete deletelist;
}
if(updatelist.size() > 0){
  update updateList;
}


This was selected as the best answer
Blake TanonBlake Tanon
Note that I changed the variables in the loop to A and B, form L and compLead to match the scenerios.
Blake TanonBlake Tanon
There isn't really a perfect way to stop a record insertion, so if you don't want to delete a record you could try this.  Change the trigger to Before Insert instead of After, and try this (not sure if it will work) after the loop to find dups instead of using the delete piece.

for(lead l:trigger.new){
    for(lead dlist:deletelist){
        if(l == dlist){
            trigger.new.addError('This is a dupe, so it was stopped');
            system.debug('# # # # This record was a dup based on it's email and product interest.  '+dlist);
        }
    }
}


omateomate
Thank you for all your help so far Blake.  I really appreciate it.

I finally had some time over the weekend.to play around with the code you provided and have come to the conclusion that you are probably correct that the best way to proceed is to let the new record be created and then delete it after insert if a duplicate exists.  My problem now is that I am getting an error on line 20.  

if(emailSet.get(l.email) != null){
"Variable does not exist: l.email"  

Does the variable (l.email) need to be declared within the loop to remain in scope?  If so, won't that cause the same problem you initially pointed out where if I put a query within a loop I'll start hitting limits?

/*********************************************************/

trigger SetLeadOwner on Lead (after insert) {

//create a map to hold emails/lead
map<string,lead>emailSet = new map<string,lead>();

//put emails into set
for(lead l:trigger.new){
  if(l.email != null)emailset.put(l.email,l);
}

list<lead> deleteList = new list<lead>();//these are only new records (b)
list<lead> updateList = new list<lead>();//these are only existing records (a)

//loop through leads that have emails in your set
for(lead a: [SELECT id, Principle_Interest__c FROM lead WHERE email in: emailSet.keySet()]){
  if(emailSet.get(l.email) != null){   // <---- Variable l.email does not exist
   lead b = new lead();
   b = emailSet.get(l.email);
   if(a.Principle_Interest__c == b.Principle_Interest__c){

    //lets update A with some items from B
    if(b.phone != null)a.phone  = b.phone;

    //update the email from b --> a ONLY if a is null and b is not, so not overwrite a
    if(b.email != null && a.email == null)a.email = b.email;

    //now, add a to the list to be updated with new values.
    updatelist.add(a);

    //now, lets get rid of B
    deleteList.add(b);
   }else{

    //do something when email is the same but Principle_Interest__c is not

   }
  }
}

if(deleteList.size() > 0){
    delete deletelist;
}
if(updatelist.size() > 0){
  update updateList;
}
}
Blake TanonBlake Tanon
Change that to a.email, since the varible for the Leads in the loop is 'a'.
omateomate
That worked!  Thanks Blake for all your help.
Blake TanonBlake Tanon
No problem, please mark the answer as best answer when you have a moment!