0

I have a custom object "Proposal Line Item" which has a lookup to another object "Quote". "Quote" object has a lookup to "Account". This "Account Id" is present in "Proposal Line Item" object as a formula field. Now I need to send email to all the "Account Team Members" of above mentioned account whenever the batch class updates the "Proposal Line Item" record. I have a V.F Email template which is using merge fields of the associated "Quote" object. I want to remove the SOQL query inside FOR loop whenever I an sending email to "Account Team Members". Below is my code:

 Global void execute(Database.BatchableContext BC, List<sObject> scope) 
{
List<Apttus_Config2__PriceListItem__c> UpdatePriceList = (List<Apttus_Config2__PriceListItem__c>)scope;     
Set<ID> PriceListItemIDs = new Set<ID>(); // Set of "Price List Item" Ids
Set<ID> PriceListIDs = new Set<ID>(); // Set of "Price List" Ids
Set<ID> ProductIDs = new Set<ID>(); // Set of "Product" Ids
String x= System.Label.Proposal_LineItem_End_Date; //Retrives custom label value
Integer y = Integer.valueOf(x); //Converts custom label value from string to Integer

for(Apttus_Config2__PriceListItem__c getPriceListrecords : UpdatePriceList) 
  {
    PriceListItemIDs.add(getPriceListrecords.id);  //Adding PriceListItem Id's to the set 
    PriceListIDs.add(getPriceListrecords.Apttus_Config2__PriceListId__c);  //Adding Price List Id's to the set 
    ProductIDs.add(getPriceListrecords.Apttus_Config2__ProductId__c);  //Adding the Product Id's to the set 
  }

    //Query to get all ProposalLineItem Id's whose PriceList/Product Ids are same as in PriceLineItem.
    List<Apttus_Proposal__Proposal_Line_Item__c> proposal = [Select ID,Apttus_Proposal__Proposal__c,APTS_Expired__c,Apttus_QPConfig__EndDate__c ,Publishing_Status__c,AccountId__c from Apttus_Proposal__Proposal_Line_Item__c where Apttus_QPConfig__PriceListId__c In :PriceListIDs AND Apttus_Proposal__Product__c In :ProductIDs]; // Listing Related proposals                                

    //Getting Proposal Line Items that has to be updated.              
    List<Apttus_Proposal__Proposal_Line_Item__c> PrsToUpdate = new List<Apttus_Proposal__Proposal_Line_Item__c>();
    for(Apttus_Proposal__Proposal_Line_Item__c prpsl : proposal) 
    {          
      if (prpsl.APTS_Expired__c==false)//Filtering the records where Expired=false
      {
          prpsl.Apttus_QPConfig__EndDate__c=system.today().adddays(y); 
          prpsl.Publishing_Status__c='To Be Published';
          PrsToUpdate.add(prpsl);
      }
     }
     update PrsToUpdate;          

     List<Messaging.SingleEmailMessage> emails=new List<Messaging.SingleEmailMessage>();
     EmailTemplate templateId = [Select id from EmailTemplate where name = 'Update_Expiry_Date'];                 
     for(Apttus_Proposal__Proposal_Line_Item__c Prs1 : PrsToUpdate)
     {            
          List<String> EmailString = new List<String>();
          List<User> Users1 = [Select id,Email from User where Id in(Select UserId from AccountTeamMember where AccountId=:Prs1.AccountId__c)];
          for(User U12 : Users1)
          {
           EmailString.add(U12.Email);
          }

          User AccountOwner = [Select Id from User where ID in(Select OwnerId from Account where Id=:Prs1.AccountId__c)];

          Messaging.SingleEmailMessage email= new Messaging.SingleEmailMessage();              
          email.setTargetObjectId(AccountOwner.Id);
          email.setwhatID(Prs1.Apttus_Proposal__Proposal__c);
          email.setSaveAsActivity(false);
          email.setToAddresses(EmailString);
          email.setTemplateID(templateId.Id);
          emails.add(email); 
      }
      Messaging.sendEmail(emails);  }
  • I have 2 SOQL query inside my last FOR loop. One to get "Account Team Members" and second to get "Account Owner" . I want to remove both queries outside loop. I have tries 2-3 ways to do it but none of them gives me proper result. – Ruchi Jul 29 '15 at 12:05
0

Some point that I did:

  1. When you get the line items, create a set of AccountiDs to query
  2. Query AccountTeamMember using relationships to get the data instead of the other queries and store them in a map
  3. Use the AccountID__r.OwnerID instead of querying for the Account again

Important Point: Use the ability to traverse relationships whenever you can to get all the data you need

There may be other issues that I did not review and it could be refined further but this is a start

I have update your code below:

Global void execute(Database.BatchableContext BC, List<sObject> scope) 
{
List<Apttus_Config2__PriceListItem__c> UpdatePriceList = (List<Apttus_Config2__PriceListItem__c>)scope;     
Set<ID> PriceListItemIDs = new Set<ID>(); // Set of "Price List Item" Ids
Set<ID> PriceListIDs = new Set<ID>(); // Set of "Price List" Ids
Set<ID> ProductIDs = new Set<ID>(); // Set of "Product" Ids
String x= System.Label.Proposal_LineItem_End_Date; //Retrives custom label value
Integer y = Integer.valueOf(x); //Converts custom label value from string to Integer

for(Apttus_Config2__PriceListItem__c getPriceListrecords : UpdatePriceList) 
  {
    PriceListItemIDs.add(getPriceListrecords.id);  //Adding PriceListItem Id's to the set 
    PriceListIDs.add(getPriceListrecords.Apttus_Config2__PriceListId__c);  //Adding Price List Id's to the set 
    ProductIDs.add(getPriceListrecords.Apttus_Config2__ProductId__c);  //Adding the Product Id's to the set 
  }

    //Query to get all ProposalLineItem Id's whose PriceList/Product Ids are same as in PriceLineItem.
    List<Apttus_Proposal__Proposal_Line_Item__c> proposal = [Select ID,Apttus_Proposal__Proposal__c,APTS_Expired__c,Apttus_QPConfig__EndDate__c ,Publishing_Status__c,AccountId__c from Apttus_Proposal__Proposal_Line_Item__c where Apttus_QPConfig__PriceListId__c In :PriceListIDs AND Apttus_Proposal__Product__c In :ProductIDs]; // Listing Related proposals                                
    //Get account IDs
    Set<ID> acctIDs = New Set<ID>();

    //Getting Proposal Line Items that has to be updated.              
    List<Apttus_Proposal__Proposal_Line_Item__c> PrsToUpdate = new List<Apttus_Proposal__Proposal_Line_Item__c>();
    for(Apttus_Proposal__Proposal_Line_Item__c prpsl : proposal) 
    {          
      if (prpsl.APTS_Expired__c==false)//Filtering the records where Expired=false
      {
          prpsl.Apttus_QPConfig__EndDate__c=system.today().adddays(y); 
          prpsl.Publishing_Status__c='To Be Published';
          PrsToUpdate.add(prpsl);
          acctIDs.add(prpsl.AccountID__c);
      }
     }
     update PrsToUpdate;          

     List<Messaging.SingleEmailMessage> emails=new List<Messaging.SingleEmailMessage>();
     EmailTemplate templateId = [Select id from EmailTemplate where name = 'Update_Expiry_Date'];                 
     Map<ID,AccountTeamMember[]> acctTeam = New Map<ID,AccountTeamMember>();
     Map<ID,Account> accts = New Map<ID,Account>([Select Owner.Name From Account Where ID IN :acctIDs]);

     //Get records related to account
     for(AccountTeamMember tm : [Select id, AccountID, user.Email, Account.OwnerID from AccountTeamMember where AccountId in :acctIDs]){
        if(!acctTeam.containsKey(tm.AccountID))
            acctTeam.put(tm.AccountId, New AccountTeamMember[]{});

        acctTeam.get(tm.AccountID).add(tm);
     }


     for(Apttus_Proposal__Proposal_Line_Item__c Prs1 : PrsToUpdate)
     {            
          List<String> EmailString = new List<String>();
          //List<User> Users1 = [Select id,Email from User where Id in(Select UserId from AccountTeamMember where AccountId=:Prs1.AccountId__c)];
          if(acctTeam.containsKey(prs1.AccountID__c)){
              for(AccountTeam U12 : acctTeam.get(prs1.AccountID__c))
              {
               EmailString.add(U12.User.Email);
              }

          }

          //User AccountOwner = [Select Id from User where ID in(Select OwnerId from Account where Id=:Prs1.AccountId__c)];

          Messaging.SingleEmailMessage email= new Messaging.SingleEmailMessage();              
          //email.setTargetObjectId(AccountOwner.Id);

          //You will need to handle the possibility that no Account was returned

          if(acts.containsKey(prs1.AccountID__c))
                email.setTargetObjectId(prs1.AccountID__r.OwnerID);

          email.setwhatID(Prs1.Apttus_Proposal__Proposal__c);
          email.setSaveAsActivity(false);
          email.setToAddresses(EmailString);
          email.setTemplateID(templateId.Id);
          emails.add(email); 
      }
      Messaging.sendEmail(emails);  
}
| improve this answer | |
  • Hello Eric, Thank you for the answer . Everything is working awesome with your provided code except for the part email.setTargetObjectId(prs1.AccountID__r.OwnerID); I get the error saying Compile Error: Invalid foreign key relationship: Apttus_Proposal__Proposal_Line_Item__c.AccountID__r at line 85 column 35 Can you please help here too? Thanks! – Ruchi Jul 30 '15 at 6:16
  • I had tried the same thing before also to get the account owner id but then also it gave me the same error. – Ruchi Jul 30 '15 at 6:20
  • @RuchikaSaxena - What is the API Name of the field on the Proposal Line Item that looks up to the account? Ot is the AccountID__c only a text field storing the Account ID? – Eric Jul 30 '15 at 6:43
  • Yes it is Formula (Text) type of field only storing the 15digit account id. – Ruchi Jul 30 '15 at 6:50
  • I have provided my comments above. Can you please help me with this ? – Ruchi Jul 30 '15 at 7:26
1

Make list of Users1 & AccountOwner outside both loops and use SOQL to store the result their associated SOQL results by mention all ids of PrsToUpdate in in selector of SOQL quires.

Then use their list of result in loops to get their value & perform operation accordingly.

Please let me know if you understand the logic or need more clarification on it.

| improve this answer | |

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.