[SalesForce] Return SOQL for current record on Visualforce Email

I'm going to have to write a custom controller for a Visualforce email to pull back details for the contract where my "send email" button will be hosted. My relatedtotype is the Account object (has to be this way because of the amount of related information to Account that I need to pull) if that at all helps.

I was hoping it would be a simple SOQL of:

SELECT <fields 1 - 20> FROM Contracts WHERE id = get.id

or something similar.

I'm afraid my syntax knowledge is lacking and that is why I'm coming to you!

Fingers crossed my life will be:

  1. Apex to host this SOQL
  2. VisualforceComponent to bring in the result
  3. Tie into Visualforce email

Feel free to let me know if my logic is lacking!

UPDATE 1: (now deleted to reduce length, superseded by next update)

UPDATE 2:

I am working on the custom controller. So far I have the following Apex Controller:

public class findContract {

public Id conID {get; set;}

    Public List<Contract> currentcontract = new List<Contract>();

    public List<Contract> getcurrentcontract() {
        currentcontract = [SELECT Id, StartDate, Business_Type__c, Affinity__c, ContractTerm,  CompanySigned.name, CompanySignedDate, CustomerSigned.name, CustomerSignedDate, CustomerSignedTitle, 
        EndDate, Services_Taken_AI_Only__c, Services_Taken_AI_Only_HS__c, Services_Taken_Advice_Only__c, Services_Taken_Advice_Only_HS__c, Services_Taken_Franchise_Comp_EL__c, 
        Services_Taken_Franchise_Comp_HS__c, Services_Taken_Consultancy__c, Services_Taken_Franchise_Entry_EL__c, Services_Taken_Env__c, 
        Services_Taken_eRAMS__c, Services_Taken_FRA__c, Services_Taken_HS__c, Services_Taken_SBP__c, Services_Taken_Training__c, Services_Taken_JIT__c, H_S_Notes__c, 
        Finance_Notes__c, PEL_Notes__c, SpecialTerms FROM Contract WHERE Id = '800D00000044RXCIA2'];    //:conID
        return currentcontract ;
        }
}

I've hard coded the ID because I just want to get something to display to start with, I'm getting nothing right now.

In the Component, I have this:

<apex:component controller="findContract" access="global">
    <apex:attribute name="ContractId" description="This is the Contract Id." type="Id" assignTo="{!conID}"/>     
    <table border="1">
        <tr>
            <td><apex:outputText value="Contract Term"/></td>
            <td><apex:outputText value="Start Date"/></td>
            <td><apex:outputText value="Business Type"/></td>
            <td><apex:outputText value="Affinity"/></td>
        </tr>  
    <apex:repeat value="{!currentcontract}" var="con" id="theRepeat">
        <tr>
            <td><apex:outputField value="{!con.ContractTerm}"/></td>
            <td><apex:outputField value="{!con.StartDate}"/></td>
            <td><apex:outputField value="{!con.Business_Type__c}"/></td>
            <td><apex:outputField value="{!con.Affinity__c}"/></td>
        </tr>                
    </apex:repeat>
    </table>
</apex:component>

It is as if nothing is coming through from the Apex Controller.

And the VF email:

<c:DisplayContractDetails ContractID="800D00000044RXCIA2"/>

Again, something basic with hardcoded values that aren't even being used yet. I am just not seeing anything returned on the email.

I know the SOQL is fine because I have run it through Force Explorer – I just cannot see where to go next right now. I've tried system.debug but nothing shows up.

Best Answer

You can't actually use a controler (directly) from a visualforce email template. Instead, you'll need to write a visualforce component, and include the component in your template. (You can write a controller for the component, but not for the email template.)

crop1645's question about having multiple contracts associated with an account is important to consider too.

Here's how I would approach this:

  • Create a very basic email template containing a custom component. (Plan on putting most of the dynamic content in the component, not the template.)
  • Make the relatedToType of your email template "Contract" (instead of "Account"). That way you'll know exactly which Contract you're dealing with.
  • Pass the Contract ID into the component so you'll be able to reference it from the component's controller.
  • In the controller, you can use the Contract ID to query for any info you need from the Contract and it's associated Account. Use that info to design your email inside the component.

Something like this:

Email template:

<messaging:emailTemplate relatedToType="Contract" subject="...">
  <messaging:htmlBody>
      Lorem Ipsum dolor bla bla bla...
      <c:EmailBodyContent theContractID="{!relatedTo.ID}">
  </messaging:htmlBody>
</messaging:emailTemplate>

Visualforce component: (EmailBodyContent.component)

<apex:component access="global" controller="myController">
  <apex:attribute name="theContractID" description="..." type="string" assignTo="{!conID}"/>
  <apex:outputText>
    Regarding the contract starting on {!theContract.StartDate} for {!theAccount.Name}...
  </apex:outputText>
</apex:component>

Component controller

Public class myController{
  public String conID {get; set;}
  public Account theAccount {get; set;}
  public Contract theContract {get; set;}

  theContract = [SELECT Id, StartDate, AccountID FROM Contract WHERE Id = :conID LIMIT 1];
  theAccount = [SELECT Id, Name FROM Account WHERE Id = :theContract.AccountID LIMIT 1];
}
Related Topic