[SalesForce] How to traverse multiple SObjects in a Visualforce email template

I am having trouble creating an email template that requires the following field path: Contact–>Opportunity–>Account–>Factor Data (custom object on account).

We are trying to put together a commission report for our brokers. The brokers (contacts) are linked to the opportunities. However, the fees earned, purchases, etc. that the opportunity incurs for any given month is found on Factor Data (on the accounts page).

I have little to no experience with coding or Salesforce language. I'm just trying to find out if this is even possible.

Would anyone know a workaround given the following Visualforce template?

<messaging:emailTemplate subject="Broker Report" recipientType="Contact"   relatedToType="Opportunity">
    <messaging:plainTextEmailBody >
        <html>
            <body>
                <STYLE type="text/css">
                    TH {font-size: 11px; font-face: arial; background: #CCCCCC;
                    border-width: 1; text-align: center}
                    TD {font-size: 11px; font-face: verdana}
                    TABLE {border: solid #CCCCCC; border-width: 1}
                    TR {border: solid #CCCCCC; border-width: 1}
                </STYLE>
                <font face = "arial" size="2">
                    <p>Dear , </p>
                    <p>Below is a detail of your commission report for this month. </p>
                    <table border="0">
                        <tr >
                            <th>Client</th>
                            <th>Purchases</th>
                            <th>Collections</th>
                            <th>Fees Earned</th>
                        </tr>
                        <apex:repeat var="ox" value = "{!relatedTo.Opportunity}">
                        <tr>
                            <td>{!ox.Account}</td>
                            <td>{!ox.Purchase_MTD__c}</td>
                            <td>{!ox.Collections__c}</td>
                            <td>{!ox.Earned_Fees__c}</td>
                        </tr>
                        </apex:repeat>
                    </table>
                    <p />
                </font>
            </body>
        </html>
    </messaging:plainTextEmailBody>
</messaging:emailTemplate>

Best Answer

You can reference it, and you don't need to use the <apex:repeat> tag. Let's assume, for example, that your relationship from Account to Factor Data has an API Name of Factor_Data__c. The following table shows you how you would reference the object and pull fields from it.

SObject        Lookup Field      Relationship Name
Contact        OpportunityId     Opportunity
Opportunity    AccountId         Account
Account        Factor_Data__c    Factor_Data__r

So, to reference fields on the related Factor Data object, you would use:

{!relatedTo.Opportunity.Account.Factor_Data__r.Some_Field__c}
Related Topic