+ Start a Discussion
abinayaabinaya 

Scheduled Email alert to record owner

Hi,

 

Whenever a candidate is approved and not submitted to client by the requirement owner on same date, an e-mail has to be sent to the requirement owner by end of day. I have selected the candidates which are appoved and not submitted to client using SOQL. 

 

My query is "SELECT Candidate__c,Requirement__c, Requirement_Code__c, Requirement_Owner__c,Req_Owner_Email__c,status1__c FROM Candidate_Mapping__c where  Submitted_to_Client__c =''and status1__c = 'Approved' ". 

 

No.of rows retrieved is 55 in my sandbox environment. 

 

I have used text template to send this email to the owners. I am facing a issue like "No of Email Invocation is more than 10" Governor limits.

 

 

Code using text template:

 

public class assigncandidatestatusemailalert
{

public void SendEmail()
{

List<Candidate_Mapping__c> candidatemappinglist = new List<Candidate_Mapping__c>( [SELECT Candidate__c, Id, Req_Owner_Email__c, Requirement__c, Requirement_Owner__c, Requirement_Owner_Email__c, Status1__c,LastModifiedDate FROM Candidate_Mapping__c where Submitted_to_Client__c = '' limit 30]);

system.debug('candidatemappinglist :' + candidatemappinglist );

String CandID, ReqmtID;

if(candidatemappinglist != null)
{
Messaging.SingleEmailMessage mail;

for (Candidate_Mapping__c cm :candidatemappinglist )
{
CandID = cm .Candidate__c;
system.debug('candID:' + CandID);

ReqmtID = cm.Requirement__c;
system.debug('ReqmtID:' + ReqmtID);

Candidate__c Candt = [Select Id, Candidate_Full_Name__c from Candidate__c where Id = :CandID];
Requirement__c Reqmt = [Select Id,Name,Opportunity_Code__c,OwnerId from Requirement__c where id = :ReqmtID];
System.debug('Reqmt:' + Reqmt);
User u = [select Id, Email, Name from User where Id = :Reqmt.OwnerId];
System.debug('user:' + u);

EmailTemplate et = [Select Id, Subject, HtmlValue, Body from EmailTemplate where Name = 'Submitted to Client Alert'];
System.Debug('EmailTemplate Id: ' + et.id);
System.Debug('et : ' + et);
// mail.setSenderDisplayName('Abinaya');

// String htmlBody = et.HtmlValue;
String plainBody = et.Body;


if(u.Name != null)
{
plainBody = plainBody.replace('{!Requirement__c.OwnerFullName}', u.Name);
}

else
{
plainBody = plainBody.replace('{!Requirement__c.OwnerFullName}', ' ');
}

if(Candt.Candidate_Full_Name__c != null)
{
// htmlBody = htmlBody.replace('{!Candidate__c.Candidate_Full_Name__c}', Candt.Candidate_Full_Name__c);
plainBody = plainBody.replace('{!Candidate__c.Candidate_Full_Name__c}', Candt.Candidate_Full_Name__c);
System.Debug('Candt Name:' + Candt.Candidate_Full_Name__c);
}
else
{
//htmlBody = htmlBody.replace('{!Candidate__c.Candidate_Full_Name__c}', ' ');
plainBody = plainBody.replace('{!Candidate__c.Candidate_Full_Name__c}', ' ');
}

if(Candt.Id != null)
{
// htmlBody = htmlBody.replace('{!Candidate__c.Id}', Candt.Id);
plainBody = plainBody.replace('{!Candidate__c.Id}', Candt.Id);
System.Debug('Candt Id:' + Candt.Id);
}
else
{
// htmlBody = htmlBody.replace('{!Candidate__c.Id}', ' ');
plainBody = plainBody.replace('{!Candidate__c.Id}', ' ');
}

if(Reqmt.Name != null )
{
// htmlBody = htmlBody.replace(' {!Requirement__c.Name}', Reqmt.Name);
plainBody = plainBody.replace('{!Requirement__c.Name}', Reqmt.Name);
System.Debug('Reqmt Name:' + Reqmt.Name);
}
else
{
// htmlBody = htmlBody.replace('{!Requirement__c.Name}', ' ');
plainBody = plainBody.replace('{!Requirement__c.Name}', ' ');
}

if(Reqmt.Opportunity_Code__c != null)
{
// htmlBody = htmlBody.replace('{!Requirement__c.Opportunity_Code__c}', Reqmt.Opportunity_Code__c );
plainBody = plainBody.replace('{!Requirement__c.Opportunity_Code__c}', Reqmt.Opportunity_Code__c );
System.Debug('Candt Name:' + Reqmt.Opportunity_Code__c );
}
else
{
// htmlBody = htmlBody.replace('{!Requirement__c.Opportunity_Code__c}', ' ');
plainBody = plainBody.replace('{!Requirement__c.Opportunity_Code__c}', ' ');
}

mail = new Messaging.SingleEmailMessage();

String[] address = new String[]{u.email};
mail.setToAddresses(address);


//System.Debug('HtmlBody:' + htmlBody);

System.Debug('Plain Body:' + plainBody);

// List<User>user = new List<User>([Select Id from User where IsActive = true and ProfileId = '00e40000000wnXc' ]);
// System.Debug('UserID: ' + user.id);

//String subject = et.Subject;
//mail.setTemplateId(et.id);
mail.setPlainTextBody(plainBody);
// mail.setHtmlBody(htmlBody );


String[] bccaddress = new String[]{'abinaya_s@preludesys.com'};

mail.setBccAddresses(bccaddress);

mail.saveAsActivity = false;
// mail.setTargetObjectId(user.Id);


Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

}
}

}

}

 

 

So I am trying to send email through visualforce email template. 

 

For this i have created a class, component and visualforce email template.

 

Class:

 

public class SubmittedtoClientalert
{
public final List<Candidate_Mapping__c> candidatemappinglist ;
public final test[ ] candiateassign= new List<test>();
public final List<Candidate_Mapping__c> one = new List<Candidate_Mapping__c>();

public SubmittedtoClientalert()
{
candidatemappinglist = [SELECT Candidate__c,Requirement__c, Requirement_Code__c, Requirement_Owner__c,Req_Owner_Email__c,status1__c FROM Candidate_Mapping__c where Submitted_to_Client__c =''and status1__c = 'Approved' limit 30];

system.debug('candidatemappinglist :' + candidatemappinglist );

String CandID, ReqmtID;

if(candidatemappinglist != null)
{
Messaging.SingleEmailMessage mail;

for (Candidate_Mapping__c cm :candidatemappinglist )
{
test t= new test();
CandID = cm .Candidate__c;
system.debug('candID:' + CandID);
ReqmtID = cm.Requirement__c;
system.debug('ReqmtID:' + ReqmtID);

Candidate__c Candt = [Select Id, Candidate_Full_Name__c from Candidate__c where Id = :CandID];
Requirement__c Reqmt = [Select Id,Name,Opportunity_Code__c,OwnerId from Requirement__c where id = :ReqmtID];
System.debug('Reqmt:' + Reqmt);
User u = [select Id, Email, Name from User where Id = :Reqmt.OwnerId];
System.debug('user:' + u);
one.add(new Candidate_Mapping__c( LR_Comments__c= Candt.Candidate_Full_Name__c ,R_Comments__c= Reqmt.Name,MR_Comments__c= Reqmt.Opportunity_Code__c,status1__c= cm.status1__c));

}
}

}

public List<Candidate_Mapping__c> getcandidatemappinglist()
{
return candidatemappinglist ;
}

public List<Candidate_Mapping__c> getone()
{
return one;
}

public class test
{
public String candidatename { get; set; }
public String requirementname{ get; set; }
public String reqcode { get; set; }
public String status { get; set; }
}
}

 

Component:

 

<apex:component controller="SubmittedtoClientalert" access="global">
<apex:dataTable value="{!one}" var="proj" border="2">
<apex:column width="30%" style="align:left">
<apex:facet name="header">Candidate Name</apex:facet>
{!proj.LR_Comments__c}
</apex:column>
<apex:column width="30%" style="align:left">
<apex:facet name="header">Requirement Name</apex:facet>
{!proj.R_Comments__c}
</apex:column>
<apex:column width="40%" style="halign:left">
<apex:facet name="header">Requirement Code</apex:facet>
{!proj.MR_Comments__c}
</apex:column>
</apex:dataTable>
</apex:component>

 

 

I am retrieving the output in visualforce template if the query limit is 30. Otherwise too many queries 101 error is throwing.

 

How to resolve this error?. And also i need to seperate the candidate list based on the requirement owner and send email to the requirement owner with their candidate list.

 

Kindly advice.

hitesh90hitesh90

Hi abinaya,

 

You have used SOQL queries in For loop it hits salesforce governer limits. because of that you are getting error.

you have to optimize your code. use Set and Map methods of salesforce to resolve this.

 

Important :
Hit Kudos if this provides you with useful information and if this is what you where looking for then please mark it as a solution for other benefits.
 
Thank You,
Hitesh Patel
SFDC Certified Developer & Administrator

Avidev9Avidev9

The error you are not because of the value/query rows return but because of the number of SOQL fired

 

public class SubmittedtoClientalert {
    public final List < Candidate_Mapping__c > candidatemappinglist;
    public final test[] candiateassign = new List < test > ();
    public final List < Candidate_Mapping__c > one = new List < Candidate_Mapping__c > ();
    public SubmittedtoClientalert() {
        candidatemappinglist = [SELECT Candidate__c, Requirement__c, Requirement_Code__c, Requirement_Owner__c, Req_Owner_Email__c, status1__c FROM Candidate_Mapping__c where Submitted_to_Client__c = ''
            and status1__c = 'Approved'
            limit 30
        ];

        system.debug('candidatemappinglist :' + candidatemappinglist);
        String CandID, ReqmtID;
        if (candidatemappinglist != null) {
            Messaging.SingleEmailMessage mail;
            for (Candidate_Mapping__c cm: candidatemappinglist) {
                test t = new test();
                CandID = cm.Candidate__c;
                system.debug('candID:' + CandID);
                ReqmtID = cm.Requirement__c;
                system.debug('ReqmtID:' + ReqmtID);
                Candidate__c Candt = [Select Id, Candidate_Full_Name__c from Candidate__c where Id = : CandID];
                Requirement__c Reqmt = [Select Id, Name, Opportunity_Code__c, OwnerId from Requirement__c where id = : ReqmtID];
                System.debug('Reqmt:' + Reqmt);
                User u = [select Id, Email, Name from User where Id = : Reqmt.OwnerId];
                System.debug('user:' + u);
                one.add(new Candidate_Mapping__c(LR_Comments__c = Candt.Candidate_Full_Name__c, R_Comments__c = Reqmt.Name, MR_Comments__c = Reqmt.Opportunity_Code__c, status1__c = cm.status1__c));

            }
        }

    }
    public List < Candidate_Mapping__c > getcandidatemappinglist() {
        return candidatemappinglist;
    }
    public List < Candidate_Mapping__c > getone() {
        return one;
    }
    public class test {
        public String candidatename {
            get;
            set;
        }
        public String requirementname {
            get;
            set;
        }
        public String reqcode {
            get;
            set;
        }
        public String status {
            get;
            set;
        }
    }
}

 

 

  • Problem 1 : You have SOQL inside for loop which is not a good practice. Have a look here how to avoid SOQL inside for loop http://wiki.developerforce.com/page/Best_Practice:_Bulkify_Your_Code
  • No of Email Invocation is more than 10 : You can call the sendEmail method only 10 times in a transaction.  sendEmail takes in list of mail message so Again you have to bulkify the code so that it calls the sendEmail only once
abinayaabinaya

Thanks Avi and Hitesh Patel.

 

I have resolved the too many soql queries 101 using relationship queries. Now i have to separate the email template based on the requirement owner.

 

How to accomplish this or else is there any other way to do the filtering in apex class?  

 

Kindly advice.