+ Start a Discussion
saas_ydusaas_ydu 

How to insert Last Case Comment in a Visualforce Mail Template ?

I would like to improve the Case Comment Template defined in our Cases / Support Settings.

It's a Visualforce Email Template, working well (including translations) but missing the "Last Case Comment" information.

 

I have tried a bunch of solution with no success until now. Can someone pinpoint what I am doing wrong ?

 

Here is my Apex Class:

public class findSaaSlastCaseComment {
    private final List<CaseComment> cComment;
    public Id caseId {get; set;}

    public findSaaSlastCaseComment() {
        cComment = [SELECT CommentBody FROM CaseComment WHERE ParentId = :caseId AND IsPublished = True ORDER BY LastModifiedDate DESC LIMIT 1];
    }

    public List<CaseComment> getSaaSlastCaseComment() {
        return cComment;
    }
}

 Here is my Visualforce Component:

<apex:component controller="findSaaSlastCaseComment" access="global">
    <apex:attribute name="caseId" description="Salesforce Id of the Case" type="Id" assignTo="{!caseId}" />
    <apex:dataTable value="{!SaaSlastCaseComment}" var="lastCaseComment">
        <apex:column >
            {!lastCaseComment.CommentBody}
        </apex:column>
    </apex:dataTable>
</apex:component>

 Here is an extract of my Email Template:

<messaging:emailTemplate recipientType="Contact"
    relatedToType="Case"
    subject="{!relatedTo.Subject}">
    <messaging:htmlEmailBody >
        <html><body>
	Dear customer...
        Description: {!relatedTo.Description}

        Last comment: <c:findSaaSlastCaseComment />

        </body></html>
    </messaging:htmlEmailBody>
<messaging:plainTextEmailBody >
	...
</messaging:plainTextEmailBody>
</messaging:emailTemplate>

 As I understand CaseId is empty because the mail template references the Id as "RelatedTo.CaseId" not "CaseId", but I don't know (I am not a developer) how to reference CaseId the right way.

 

Is there a better solution (unfortunately {!Case.Last_Case_Comment} is not available in Visualforce Email Template)?

 

Many thanks for your answers,

Yves

Best Answer chosen by Admin (Salesforce Developers) 
TheSwamiTheSwami

It is not good practice to assign the result of a query directly to an SObject:  

 

 
String lcc = [SELECT CommentBody FROM CaseComment WHERE ParentId = :caseId AND IsPublished = True ORDER BY LastModifiedDate DESC LIMIT 1].CommentBody;


 

If there are no results, you will get the "no rows for assignment" error.

This is a common problem, and can be solved farily easily:http://boards.developerforce.com/t5/Apex-Code-Development/APEX-List-has-no-rows-for-assignement-to-SObject-problem/td-p/66796

You should fix the error because I just saw a case where this caused an approval process to fail.

 

All Answers

saas_ydusaas_ydu

Finally I got a result, but stil have a warning when saving the email template: "Error occurred trying to load the template for preview: List has no rows for assignment to SObject. Please try editing your markup to correct the problem."

 

However, it works!

 

Apex Class

public class SaaSlastCaseComment {
    public Id caseId {get; set;}
    public String lastCaseComment { 
        get {
            String lcc = [SELECT CommentBody FROM CaseComment WHERE ParentId = :caseId AND IsPublished = True ORDER BY LastModifiedDate DESC LIMIT 1].CommentBody;
            return lcc;
        }
        set;
    }
}

 Component

<apex:component controller="SaaSlastCaseComment" access="global">
    <apex:attribute name="caseId" description="Salesforce Id of the Case" type="Id" assignTo="{!caseId}" />
    <apex:outputtext value="{!lastCaseComment}"/>
</apex:component>

 Email Template

<c:SaaSlastCaseComment caseId="{!relatedTo.Id}" />

 If you have any idea on what this warning is about, do not hesitate to comment my code.

TheSwamiTheSwami

It is not good practice to assign the result of a query directly to an SObject:  

 

 
String lcc = [SELECT CommentBody FROM CaseComment WHERE ParentId = :caseId AND IsPublished = True ORDER BY LastModifiedDate DESC LIMIT 1].CommentBody;


 

If there are no results, you will get the "no rows for assignment" error.

This is a common problem, and can be solved farily easily:http://boards.developerforce.com/t5/Apex-Code-Development/APEX-List-has-no-rows-for-assignement-to-SObject-problem/td-p/66796

You should fix the error because I just saw a case where this caused an approval process to fail.

 

This was selected as the best answer