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
BrianWKBrianWK 

Overcoming SoQL Limits

 
Hi everyone. I'm still very new at writing triggers. I've had a few succesful ones. We have one trigger that is firing on a custom object and updates two fields pulled via SoQL from its related object. The code below works well but only updates 1 record when using the dataloader.
 
 
Code:
trigger Product_Contact_Roles_Phone_Email on Product_Contact_Role__c (before insert, before update) {
 
 //Set Contact_Email__c  to Email
 //Set Contact_Phone__c to Phone 
 
  //This should set a variable to an identifier 
 String ContactID = Trigger.new[0].Contact__c;
 
    
 Contact p = [SELECT Phone FROM Contact WHERE contact.id = :ContactID limit 1]; 
 Contact e = [SELECT Email FROM Contact WHERE contact.id = :ContactID limit 1]; 
  //For the Item that's update the field Phone__C value is set to the varible defined above
  
  Trigger.new[0].Contact_Phone__C = p.Phone;
  Trigger.new[0].Contact_Email__C = e.Email;
So I updated the code to loop through the Trigger.new. This works, but once I get more than 10 records being updated I reach the limit. I think I can use a map to grab all the Phone and E-mails from the related contact IDs of items in Trigger.new and then use a for to loop through all Ids from Trigger.new to update from the Map. But 1. I'm not sure if this would work and 2. if it would not quite sure how to build it. Here's the trigger that's breaking the limit:
Code:
for (Integer i = 0; i < Trigger.new.size(); i++) { 
 Contact p = [SELECT Phone FROM Contact WHERE contact.id = :ContactID limit 1]; 
 Contact E = [SELECT Email FROM Contact WHERE contact.id = :ContactID limit 1]; 
 Trigger.new[i].Contact_Phone__C = p.Phone;
 Trigger.new[i].Contact_Email__C = e.Email; 
}

 Help Suggestions greatly appreciative. If anyone has a good resources that gives me examples of similar triggers that would be fantastic learning tool for me. Thank you!

 
mikefmikef
Brian:

You need to make your code handle bulk actions.

Here is a post that might help, post.

The goal is to do as few queries as possible and call variables in memory to get the results when you need them.

Also and this is a rule of thumb with Apex development, never put a query in a for loop.

Hope this helps.
DaGunsterDaGunster
Even after he makes the fix, will this deploy?  How would a 'test' be created to get this out of development and into production?
mikefmikef
I would create a Apex Class and have a testMethod in the class that creates and updates a Product_Contact_Role__c record.

Then test to see if the values of his phone and email are correct.

There is a section on testMethods in the Apex docs that is very helpful.
DaGunsterDaGunster

I've been through it twenty times and maybe I'm stupid.

Can you create this test class that works with this example and post it. I'll try to figure it out.

But I'm out of time. My trigger works and I HAVE to deploy it.  I'm going to my superiors and telling them that SalesForce is not going to do what we need if I cannot deploy my trigger.

Thank you so much.

Please example the class.
BrianWKBrianWK
My test class was really simple. I have a test account, product contact role(s) and the related contact. Then the Class selected a single Prodcut Contact Role and updated it. This was enough for it to pass an get deployed.
BrianWKBrianWK

Mikef:

Thanks for the guidance. I've been pouring over that very same post and trying to make it applicable to my specific trigger.

Let me sum up what I think that trigger doing and what I would do for my example.

I would create a set of all Contact ids in the trigger.

Then I would create  a map<string,id> using a SQoL Query for all IDs in the Contact Set created above

Then I do a for loop for each Contact Id in the Trigger set and within the for loop is where the Product Contact Role contact_Phone will get updated.

Is this correct?
mikefmikef
Brian:

That is correct you have the logical steps down, and those are the steps for 80% of what you want to do in Apex.
Get a set of values to query
Query to populate a Map
Call the map to get your new vales.

So here is how I would write what you need todo.

Code:
trigger Product_Contact_Roles_Phone_Email on Product_Contact_Role__c (before insert, before update) {
 Set<Id> conId = new Set<Id>(); 
 
 for(Product_Contact_Role__c pcr : Trigger.new){
    conId.add(pcr.Contact__c);
 }
 Map<Id,Contact> contactMap = new Map<Id,Contact>([SELECT Id, Phone, Email FROM Contact WHERE Id in : conId]);
    
 for(Product_Contact_Role__c pcr : Trigger.new){
    pcr.Contact_Phone__c = contactMap.get(pcr.Contact__c).Phone;
    pcr.Contact_Email__c = contactMap.get(pcr.Contact__c).Email;
 }

 
mikefmikef
DaGunster:

I am sorry to hear that you have to tell your boss that salesforce can't do what you need todo.
I am sure you figured it out but this testMethod is the one thing keeping you back.

So let's try to solve that, I think we can do it.

One thing writing the test class would take me a long time so I can't do all the work for you but I can provide you with a frame work.
Will that help?

Code:
public class TestContactRole{
  static testMethod void testInsertOfRoles(){
    Contact con = new Contact(LastName='name', and other fields you need like phone and email);
    insert con;
    System.assertNotEquals(con.id,null);//this a test to see if the record was created

    Product_Contact_Role__c pcr = new Product_Contact_Role__c(Contact__c=con.id, and other fields you need);
    insert pcr;
    System.assertNotEquals(pcr.id,null);

    Contact conQuery = [select Email, Phone from Contact where Id = : con.id Limit 1];
    Product_Contact_Role__c pcrQuery = [select Contact_Email__c, Contact_Phone__c from Product_Contact_Role__c where id = : pcr.id Limit 1];

    System.assertEquals(conQuery.Email,pcrQuery.Conact_Email__c);
    System.assertEquals(conQuery.Phone,pcrQuery.Contact_Phone__c);
  }



}

Well I ended up writing the whole thing.

 That should give Brian's trigger 100% code coverage.
BrianWKBrianWK

Mikef - Thanks. Your first post was enough guidance that I figured it out and got it to work. The only thing I did different was I created two maps, one for the e-mail and one for the phone.

Is there any benefits to creating a single map other than it's 1 less away from the gov. limit?
mikefmikef
Brian:

The benefit for one map is cleaner code, and less resources used up.
Plus one could say it's "faster" but we are dealing in a few milliseconds so not sure a user would care.

What is your thinking for using two maps?
Does it make more sense to you?
BrianWKBrianWK

Mike,

The reason I used two maps was simply for my own Sanity. I wanted to make sure I didn't forget anything.

Plus, I originally used 1 map just for the phone. Once I got to work I just cloned the code to add the e-mail. By not changing the phone I was ensured that I didn't "Break" the code to complete it. A benefit for me is if it Errors it'll tell me if it Errored on the Phone or the e-mail. Matter of fact when I did my dataloader I did get an error on about 7% of the items due to Null References in the e-mail.

I agree doing it in 1 map is cleaner and will probably be how I will code it in the future.

Thank you again for your help!
mikefmikef
Brian:

To get rid of the null errors you have to check to see if the map was populated before you try and update the record.

Code:
trigger Product_Contact_Roles_Phone_Email on Product_Contact_Role__c (before insert, before update) {
 Set<Id> conId = new Set<Id>(); 
 
 for(Product_Contact_Role__c pcr : Trigger.new){
    conId.add(pcr.Contact__c);
 }
 Map<Id,Contact> contactMap = new Map<Id,Contact>([SELECT Id, Phone, Email FROM Contact WHERE Id in : conId]);
    
 for(Product_Contact_Role__c pcr : Trigger.new){
    if(contactMap != null && contactMap.containsKey(pcr.Contact__c)){
       pcr.Contact_Phone__c = contactMap.get(pcr.Contact__c).Phone;
       pcr.Contact_Email__c = contactMap.get(pcr.Contact__c).Email;
    }
 }

 
you will have to add two of the if statements and that way if there isn't an email, then the record will not be updated, and you won't get the null error.
The email will be blank which is fine cause there isn't an email on the contact record if the map is null.

Same for Phone.
jpwagnerjpwagner
Mike,
I am writing a very similar trigger (and had the same too many queries problem and I appreciate this forum for the help it gives) however, I am trying to populate the lookup Id once another field is already set as equal:
 

trigger MyTrigger on Account (before insert, before update) {

Set<Id> accId = new Set<Id>();

for (Account a : Trigger.new){ accId.add(a.Id);}

Map<Id,Account> accMap = new Map<Id,Account>([select Id, AField__c from Account where Id in : accId]);

Contact c = [select Id from Contact where CField__c = : accMap.get(accId).AField__c];

for(Account a : Trigger.new){a.Contact__c = c.id; }

}

 

Clearly the struckthrough reference is incorrect, but what does need to go there?

Thanks
BrianWKBrianWK
jpwagner,
 
This is what I ended up doing.
 
1. Create a set of IDs (in your case this is Set "a")
2. Create a map containing the information (This is your accMap)
3. Did a for loop that for every item in trigger.new set the field.
 
Here's my code:
 
Code:
  set<Id> CONTACTIDS = new Set<Id>();
  for(Product_Contact_Role__c PCR : Trigger.new){
   CONTACTIDS.add(PCR.Contact__C);
  }
  
    Map<string,Contact> vArray = new Map<String,Contact>([select Phone, Email from Contact where contact.id in :CONTACTIDS]);
  for(Product_Contact_Role__c PCR : Trigger.new){

   PCR.Contact_Phone__c = vArray.get(PCR.Contact__C).Phone;
   PCR.Contact_Email__c = vArray.get(PCR.Contact__C).Email;
  }

 
 
jpwagnerjpwagner
I appreciate the feedback, however I cannot compile a set of Id's from the "Contact__c" because that is the field I am trying to populate.
mikefmikef
jpwagner:

The logic that you are using in your trigger is not correct.
You are doing something like this:
  1. on an account trigger get all the account ids
  2. Query the account table for a list of accounts that match your Ids
  3. query the contact table for the same records that have matching custom fields
  4. update the account with a custom contact lookup with the contacts that come back
There are a few things wrong with this logic.
  1. you're querying on the account trigger for a list of accounts in the trigger. (you have the list you need cause you have the trigger)
  2. you're not using the standard relationship that accounts have with contacts.
So I have a question for you.
Can you explain, in plain english, what you are trying to do?
I think I know but I don't want to suggest a solution on a assumption.
jpwagnerjpwagner

Mike,

Sorry for the confusion.

I would like to design a bulk trigger to find which Contact record has CField__c = AField__c from Account and assign the Contact Id to Contact__c on the Account. 
jpwagnerjpwagner

Mike,

I took a stab.

Does this make more sense:

trigger MyTrigger2 on Account (before insert, before update) {
Set<Id> accId = new Set<Id>();
for (Account a : Trigger.new){accId.add(a.Id);}
Contact c = ([select Id, CField__c, Account.Id from Contact where Account.Id in : accId]);
for(Account a : Trigger.new){
if(a.AField__c == c.CField__c){
a.Contact__c = c.id;}
}}




mikefmikef
you're close.

I am not sure you want this to run on insert. I can't think of a use case where a someone or something can insert an account and contacts at the same time. You can insert an account get the Id back and insert related contacts. But not all in the same API transaction.

Code:
trigger NewTrigger on Account(before update){
  Set<Id> accId = new Set<Id>();

  for (Account a : Trigger.new){accId.add(a.Id);}

  Map<Id,Contact> cMap = new Map<Id,Contact>();
  for(Contact c : [select Id, CField__c, Account.Id from Contact where Account.Id in : accId]){
    cMap.put(c.Account.Id,c);
  }

  for(Account a : Trigger.new){
    if(cMap != null && cMap.containsKey(a.Id)){
      if(a.AField__c == cMap.get(a.Id).CField__c){
        a.Contact__c = cMap.get(a.Id).id;
      }
    }
  }

 That should do it, but the issue you're going to have is you may have many contacts for that account and you really want to check all contacts under the account for the match.

So you might want to you List the query and search the List for all the contacts with that AccountId.




jpwagnerjpwagner
Mike,


Thanks for the help.