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
wedaftwedaft 

How to display all grandchild records in a single table in visualforce email template

So here's the background:

 

I have a master-detail hierarchy that goes: School Implementations-->Teacher Implementations-->Class Implementations. There is a lot of data that we store at the Class Implementation level (How many hours has the class used our program, what is the average class score on our program, etc) that we want to email out to a contact, specifically the school principal. I would like to create a visualforce email template that can capture all of the data from the class implementation records that are grandchildren of a school implementation and list them as rows in a single table, something like this:

 

Class Name

Hours online

Problem accuracy

Class A

3.2

78%

Class B

3.8

74%

Class C

2.5

70%

 

My main question, then, is if I have a school (Parent object) with 5 teachers(child object) and each teacher has 3 classes (grandchild object), how do I get all 15 of those classes displayed as rows on a single table in an email template?

 

Is this possible? If so, I would be very grateful for any direction that I can get. Thanks!

mast0rmast0r

I think it is possible. So you have a DB design like following:

 

School --< Teacher --< Class

 

List<Teacher__c> theWholeList = [
SELECT Id, Name, School__r.Name, (SELECT Id, Name, Hours__c, ProblemAccuracy__c FROM Class__r)
FROM Teacher__c
Where School__c = 'XXXXXXX' ];

Where the Class__r is a child relationship name of a Class object in Teacher object.

Then you can iterate through that list like this:

 

<apex:repeat value="{!theWholeList}" var="list">

    Teacher: <apex:outputText value="{!list.Name}"/>

    <apex:pageBlockTable value="{!list.Class__r}" var="classItem">
        <apex:column value="{!classItem.Name}"/>
        <apex:column value="{!classItem.Hours__c}"/>
        <apex:column value="{!classItem.ProblemAccuracy__c}"/>
    </apex:pageBlockTable>

<apex:pageBlockTable>

 

 
wedaftwedaft

Thanks mast0r. So that SOQL query you wrote goes in an apex class that I then reference in my visualforce template correct? Don't I need to reference the class using something like in the code paster below? Or can I make that query in some way directly from the visualforce template?

 

<apex:page controller="myController"
mast0rmast0r

That's right:

 

public with sharing class MyClass
{
    List<Teacher__c> theWholeList { get; set; }

    public MyClass(){
        theWholeList = new List<Teacher__c>();
    }

    public PageReference getTheWholeList(){
        List<Teacher__c> theWholeList = [ SELECT Id, Name, School__r.Name, (SELECT Id, Name, Hours__c,
                                                 ProblemAccuracy__c FROM Class__r)
                                          FROM Teacher__c
                                          Where School__c = 'XXXXXXX' ];
        return null;
    }
}

<apex:page controller="myController">
    ...
</apex:Page>

 
wedaftwedaft

It doesn't seem to like the query from classes: this is the error I get:

 

Error: Compile Error: Didn't understand relationship 'Class__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 10 column 56
wedaftwedaft

Thanks for your help mast0r. I've got it almost all working now, and just have one question remaining. In that SOQL query in the custom controller, I don't want to filter my list by specifying a school name or ID.

 

We have a contact look-up field called "Principal Name" on the school object that stores the contact for the principal of the school. I would like for the controller to be able to filter the list results to only include classes for the school where the "Principal Name" field matches the Contact Id of the contact that the email is being sent to. So, if an email is being sent to John Doe, the principal of Rogers Elementary, the SOQL query will return a list of class from Rogers Elementary because John Doe is the listed principal at that school.

 

Any ideas?