function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
London Calling.ax1696London Calling.ax1696 

Bulkify Trigger , problems with SOQL syntax

Hi,

 

In essence the below trigger creates records in two junction objects, TrialContactAssociation__c & TrialAccountAssociation__c , when a Trial__c record is created that matches an email address in an existing Contact.  This creates a way for us to track Accounts and Contacts that are already in our system for incoming trials.

 

After a zillion hours of Googling I can't seem to figure out how to reference Email__c outside the for loop?

 

Any help on the bulkification syntax will be greatly appreciated!  All bulkification examples I've come across reference the ID as opposed to a 'column' of incoming records.

 

trigger TrialInsert on Trial__c (after insert) {
    
    List<TrialContactAssociation__c> ContactListItem = new List<TrialContactAssociation__c>();
    List<TrialAccountAssociation__c> AccountListItem = new List<TrialAccountAssociation__c>();
    
    for (Trial__c c : Trigger.new) {
        
        List<Contact> contactID = new List<Contact>([SELECT Id FROM Contact WHERE Email LIKE :c.Email__c]);
        List<Contact> ContactAccountRel = new List<Contact>([SELECT AccountId FROM Contact WHERE Email LIKE :c.Email__c]);

        for (Contact contactItem : contactID){
            TrialContactAssociation__c clt = new TrialContactAssociation__c(Trial__c = c.ID, Contact__c = contactItem.Id);
            ContactListItem.add(clt);        
        }
        for (Contact AccountItem : ContactAccountRel){
            TrialAccountAssociation__c clt2 = new TrialAccountAssociation__c(Trial_Account_Asso__c = c.ID, Account_Trial_Link__c = AccountItem.AccountId);
            AccountListItem.add(clt2);
        }    
    }
    insert ContactListItem;
    insert AccountListItem;
}

 Thanks

 

John

Naidu PothiniNaidu Pothini
trigger TrialInsert on Trial__c (after insert)
{
    List<TrialContactAssociation__c> ContactListItem = new List<TrialContactAssociation__c>();
    List<TrialAccountAssociation__c> AccountListItem = new List<TrialAccountAssociation__c>();

    List<String> emailList = new List<String>();

    for (Trial__c c : Trigger.new)
    {
        emailList.add(c.Email__c);
    }

    for (Trial__c c : Trigger.new)
    {
        for(Contact con : [SELECT Id, AccountId, Email FROM Contact WHERE Email IN :emailList])
        {
            if(c.Email__c == con.Email)
            {
                TrialContactAssociation__c clt = new TrialContactAssociation__c(Trial__c = c.Id, Contact__c = con.Id);
                TrialAccountAssociation__c clt2 = new TrialAccountAssociation__c(Trial_Account_Asso__c = c.Id, Account_Trial_Link__c = con.AccountId);
            
                ContactListItem.add(clt);
                AccountListItem.add(clt2);
            }
        }  
    }

    insert ContactListItem;
    insert AccountListItem;
}

 What if there is a new trail with an Email which belongs to two or more Contacts? The above code will create a TrailContactAssociation & TrailAccountAssociation records for every match contact email.

 

Let me know if it doesnt work.
Sean TanSean Tan

I'm not sure what you're matching logic for email is so you can try this one as well. It's a little more efficienct as it doesn't use any soql in loops at all. Though this was written on the fly so may need some tweaking:

 

trigger TrialInsert on Trial__c (after insert) {
    
    List<TrialContactAssociation__c> ContactListItem = new List<TrialContactAssociation__c>();
    List<TrialAccountAssociation__c> AccountListItem = new List<TrialAccountAssociation__c>();
    Set<String> contactEmails = new Set<String>{};
    String contactEmailClause = 'LIKE ';
    
    for (Trial__c c : Trigger.new) 
    {
        if (!contactEmails.contains(c.Email__c))
        {
            contactEmails.add(c.Email__c);           
            
            if (contactEmailClause != 'LIKE ')
            {
                contactEmailClause += 'OR LIKE ';
            }
            //Add % here if you need contains or begins with
            //e.g. '\'' + c.Email__c + '\%'' for begins
            //e.g. '\'%' + c.Email__c + '\%'' for contains
            contactEmailClause += '\'' + c.Email__c + '\'';
        }
    }
    
    Contact[] contactList = Database.query('SELECT Id, AccountId FROM Contact WHERE Email ' + contactEmailClause);
    
    for (Trial__c c : Trigger.new)
    {
        for (Contact contactItem : contactList)
        {
            if (contactItem.Email.indexOf(c.Email__c) != -1)
            {
                ContactListItem.add(new TrialContactAssociation__c(Trial__c = c.ID, Contact__c = contactItem.Id));
                AccountListItem.add(new TrialAccountAssociation__c(Trial_Account_Asso__c = c.ID, Account_Trial_Link__c = contactItem.AccountId));
            }
        }
    }
    
    insert ContactListItem;
    insert AccountListItem;
}

 
London Calling.ax1696London Calling.ax1696

Thanks very much for the help Naidu!!

 

Looks like the SOQL is still running within a for loop, am I correct?  Will this fall over if the number of incoming trials exceeds 20? 

 

"...

  for (Trial__c c : Trigger.new)
    {
        for(Contact con : [SELECT Id, AccountId, Email FROM Contact WHERE Email IN :emailList])
        {

..."

 

We fortunately don' have any duplicate email addresses in Salesforce.

 

thanks again for your help.

 

John

 

 

 
Naidu PothiniNaidu Pothini
trigger TrialInsert on Trial__c (after insert)
{
    List<TrialContactAssociation__c> ContactListItem = new List<TrialContactAssociation__c>();
    List<TrialAccountAssociation__c> AccountListItem = new List<TrialAccountAssociation__c>();

    List<String> emailList = new List<String>();
    Map<String, Contact> emailMap = new Map<String, Contact>();

    for (Trial__c c : Trigger.new)
    {
        emailList.add(c.Email__c);
    }

    for(Contact con : [SELECT Id, AccountId, Email FROM Contact WHERE Email IN :emailList])
    {
        emailMap.put(con.Email, con);
    }

    for (Trial__c c : Trigger.new)
    {
        if(emailMap.get(c.Email__c) <> null)
        {
            Contact con = emailMap.get(c.Email__c);

            TrialContactAssociation__c clt = new TrialContactAssociation__c(Trial__c = c.Id, Contact__c = con.Id);
            TrialAccountAssociation__c clt2 = new TrialAccountAssociation__c(Trial_Account_Asso__c = c.Id, Account_Trial_Link__c = con.AccountId);

            ContactListItem.add(clt);
            AccountListItem.add(clt2);
        }  
    }

    insert ContactListItem;
    insert AccountListItem;
}

 if you dont have any duplicate Emails, then go with the above code..