Access child object's fields in visualforce email template

Question

Design problem - Implement an approval process on case, which sends email to approver showing some additional info, which should not be visible to case owner/agent (who clicked on "Submit for approval" button).

Possible solution - All that additional info will not be stored on Case (otherwise agent will also be able to see it). I am creating a child object to case, where all that additional info be stored. We can make sure agent does not have access to that child object (lets call it ChildObj).

In the email template used, we can include info about that child object. From what i understand, standard email templates dont allow cross object reference. We probably need to use visualforce email templates.

Questions -

1) Can anyone see/point out any issues with that design? 2) I am very new to salesforce. Can anyone give me any example of how i will access child record's field? I mean there won't be any lookup or anything to child object, right? How do i access a field on the child object? Any clue would be helpful

Answer 1

I think you need to use visualforce email template with controller.

Controller "

    ppublic class GetChild {
    private final List<Account> accounts;


    public GetChild() {
        accounts = [SELECT Name,(SELECT CreatedBy.Name FROM Notes) FROM Account];
    }

    public List<Account> getchildNotes() {

       Return accounts;
    }
}

Create a component and name it SmithNotes and access the child values something like this

<apex:component controller="GetChild" access="global">
    <apex:variable var="count" value="{!0}"/> //declare
        <apex:dataTable value="{!childNotes}" var="s_account">
            <apex:column >
                <apex:facet name="header">Notes Title</apex:facet>
                {!s_account.Notes[count].title}
            </apex:column>
        </apex:dataTable>
        <apex:variable var="count" value="{!count + 1}"/> //increment
   </apex:component>

In your visualforce email template include the component.

<messaging:emailTemplate subject="Embedding Apex Code" recipientType="Contact" relatedToType="Opportunity">
    <messaging:htmlEmailBody>
        <p>As you requested here are the notes</p>
        <c:SmithNotes/>
        <p>Hope this helps with the {!relatedToType}.</p>
    </messaging:htmlEmailBody>
</messaging:emailTemplate>