function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
codewordcodeword 

Email Template (Cross Object Functionality)

Hello everyone,

I am seeking some help and advice regarding email templates.

Our ORG currently is configured with Accounts >> Opportunities >> Opportunity Line Items (multiple "items" per opp is possible).

What I'm trying to accomplish is:

Pull Account Information, Pull Opportunity Information, BUT ALSO pull Opportunity Line Items for an email template.

Currently, if I send an email template from the opportunity object, it only pulls account and opportunity information.

Anyone have the same problem?  I've tried using VF/APEX templates but I'm pretty new to that.

Please help.

Thanks
Best Answer chosen by Admin (Salesforce Developers) 
JimRaeJimRae
Looks like for your requirements, a custom controller might be needed.  Since a controller can't be attached directly to the template, I understand the best option would be to create a component, and have the controller attached to that instead.

Here is a sample that  I found that seems to do what you are looking for.

Create this controller code for your component:

Code:
public class SortedLineItemsController {
    public Opportunity opportunity { get; set; }
    
    public OpportunityLineItem[] getSorted() {
        if (opportunity == null || opportunity.opportunityLineItems== null) {
            return null;
        }
        
        // TODO: Add your sorting logic here (demo just reverses the list)
        OpportunityLineItem[] result = new OpportunityLineItem[1];
        for (OpportunityLineItem item : opportunity.opportunityLineItems) {
            result.add(0, item);
        }
        
        return result;
   }
}

 your component would look like this:
Code:
<apex:component access="global" controller="SortedLineItemsController">
<apex:attribute name="value" type="Opportunity" assignTo="{!opportunity}" description="TODO: Describe me"/>

<apex:repeat var="li" value="{!sorted}">
<tr>
<td>{!li.PricebookEntry.Product2.Name}</td>
<td>{!li.Quantity}</td>
<td>${!li.TotalPrice}</td>
</tr>
</apex:repeat>

<!-- Needed to work around limitation that the standard controller cannot "see"
the linkage between the value attribute and the opportunity property -->
<apex:variable var="oli" value="{!value.OpportunityLineItems}" rendered="false">
{!oli.PricebookEntry.Product2.Name}
{!oli.Quantity}
{!oli.TotalPrice}
</apex:variable>
</apex:component>

 
And your template would look like this:

Code:
<messaging:emailTemplate subject="sample" recipientType="User" relatedToType="Opportunity">
<messaging:htmlEmailBody >
Congratulations!
This is your new Visualforce Email Template.
{!relatedTo.Account.Name}
<apex:datatable value="{!relatedto}" var="o">
<apex:column value="{!o.name}" ></apex:column>
<apex:column >
<apex:repeat value="{!o.OpportunityLineItems}" var="oli">  <!--limited info available this way-->
<apex:outputText value="{!oli.Description}">
</apex:outputText><br/>
</apex:repeat>
</apex:column>
</apex:datatable>
<table border="0" >
   <tr> 
       <th>Product Name</th><th>Quantity</th><th>Total Price</th><th>Monthly Fee</th>
   </tr>
   <c:sortedlineitems value="{!relatedTo}"/>            
</table>
</messaging:htmlEmailBody>
</messaging:emailTemplate>

 





All Answers

ForcecodeForcecode

There is no other way than doing this as a Visualforce template.

This sample should help:

http://wiki.apexdevnet.com/index.php/VisualForceEmailTemplates_sample

JimRaeJimRae
Try making your Opportunity the primary relatedTo object type, then you should get the parent Account information and the child OpportunityLineItem info.
Here is a real simple example.

Code:
<messaging:emailTemplate subject="sample" recipientType="User" relatedToType="Opportunity">
     <messaging:htmlEmailBody >
     Account Data for: {!relatedTo.Account.Name}
     <apex:datatable value="{!relatedto}" var="o">
          <apex:column value="{!o.name}" ></apex:column>
          <apex:column >
               <apex:repeat value="{!o.OpportunityLineItems}" var="oli">
                    <apex:outputText value="{!oli.Description}"></apex:outputText><br/>
               </apex:repeat>
          </apex:column>
     </apex:datatable>
     </messaging:htmlEmailBody>
</messaging:emailTemplate>

 

codewordcodeword
Thanks for your feedback!

I got a part of it to work (where I put the Opp as the primary object) and cascaded down to retrieve Account and Opportunity Line Item info.  Now comes the problem.  The OppLineItem name (which is the exact thing I'm trying to pull) is a lookup to the Product object.  Our product object is renamed "Services" and to pull the name, I have to use {!Product2.Name}.  I'm not sure how to address this object and insert the product name. 

I've tried making a new template where the primary object is 'Product2' :

<messaging:emailTemplate subject="New Win Announcement!" recipientType="User" relatedToType="Product2">
     <messaging:htmlEmailBody >
         <apex:datatable value="{!relatedto}" var="p">
         Solution: <apex:column value="{!p.Solution__c}" ></apex:column>
         Solution Segment: <apex:column value="{!p.Solution_Segment__c}" ></apex:column>
         Service: <apex:column value="{!p.Name}" ></apex:column>
           
This seems to work fine, but how can I get account/opportunity info in this template?  Can I cascade upwards from product?

Thanks in advance
JimRaeJimRae
Looks like for your requirements, a custom controller might be needed.  Since a controller can't be attached directly to the template, I understand the best option would be to create a component, and have the controller attached to that instead.

Here is a sample that  I found that seems to do what you are looking for.

Create this controller code for your component:

Code:
public class SortedLineItemsController {
    public Opportunity opportunity { get; set; }
    
    public OpportunityLineItem[] getSorted() {
        if (opportunity == null || opportunity.opportunityLineItems== null) {
            return null;
        }
        
        // TODO: Add your sorting logic here (demo just reverses the list)
        OpportunityLineItem[] result = new OpportunityLineItem[1];
        for (OpportunityLineItem item : opportunity.opportunityLineItems) {
            result.add(0, item);
        }
        
        return result;
   }
}

 your component would look like this:
Code:
<apex:component access="global" controller="SortedLineItemsController">
<apex:attribute name="value" type="Opportunity" assignTo="{!opportunity}" description="TODO: Describe me"/>

<apex:repeat var="li" value="{!sorted}">
<tr>
<td>{!li.PricebookEntry.Product2.Name}</td>
<td>{!li.Quantity}</td>
<td>${!li.TotalPrice}</td>
</tr>
</apex:repeat>

<!-- Needed to work around limitation that the standard controller cannot "see"
the linkage between the value attribute and the opportunity property -->
<apex:variable var="oli" value="{!value.OpportunityLineItems}" rendered="false">
{!oli.PricebookEntry.Product2.Name}
{!oli.Quantity}
{!oli.TotalPrice}
</apex:variable>
</apex:component>

 
And your template would look like this:

Code:
<messaging:emailTemplate subject="sample" recipientType="User" relatedToType="Opportunity">
<messaging:htmlEmailBody >
Congratulations!
This is your new Visualforce Email Template.
{!relatedTo.Account.Name}
<apex:datatable value="{!relatedto}" var="o">
<apex:column value="{!o.name}" ></apex:column>
<apex:column >
<apex:repeat value="{!o.OpportunityLineItems}" var="oli">  <!--limited info available this way-->
<apex:outputText value="{!oli.Description}">
</apex:outputText><br/>
</apex:repeat>
</apex:column>
</apex:datatable>
<table border="0" >
   <tr> 
       <th>Product Name</th><th>Quantity</th><th>Total Price</th><th>Monthly Fee</th>
   </tr>
   <c:sortedlineitems value="{!relatedTo}"/>            
</table>
</messaging:htmlEmailBody>
</messaging:emailTemplate>

 





This was selected as the best answer
codewordcodeword
Thank you for the quick response!  I tried what you recommended and it worked!  Thanks a lot!!!

Cheers
codewordcodeword

Thanks again for your help.  One thing I've noticed on my alerts is that the total opp amount shown is rendered as $50.0 instead of $50.00.  (shows $50.1 instead of $50.10, but it will display $50.25 if it is $50.25)

 

Since we only use whole numbers, is there anyway to get rid of the decimal point?  or make it show 2 decimals places over?  Fields and their setup look fine, it's just pulling the data in a strange way.

 

 

codewordcodeword

I fixed the amount option by creating a roll up summary field (which pulls two decimal points over, instead of one). 

 

But now, I'm running into a bigger problem with our end users:

 

I have setup a workflow that triggers an email alert (described above).  The workflow kicks off when a stage of an opportunity is set to "Closed Won" and a picklist to send alert equals "Yes".

 

As an administrator, I can select both options and save the opportunity record (and workflow, email triggers).

 

BUT when an end users tries to do the same, he's prompted with an error:

 

" Error: Invalid Data.
Review all error messages below to correct your data.
You do not have sufficient privileges to access the controller: SortedLineItemsController"

 

Do you know why this is happening?  

 

Here is my controller:

 

public class SortedLineItemsController {
public Opportunity opportunity { get; set; }

public OpportunityLineItem[] getSorted() {
if (opportunity == null || opportunity.opportunityLineItems== null) {
return null;
}

// TODO: Add your sorting logic here (demo just reverses the list)
OpportunityLineItem[] result = new OpportunityLineItem[1];
for (OpportunityLineItem item : opportunity.opportunityLineItems) {
result.add(0, item);
}

return result;
}
}

 

 

 Any help is appreciated.  I'm under a deadline and can't seem to figure it out.  Is it in the code or something I have to change under the salesforce setup?

 

JimRaeJimRae
When you deploy the controller, you need to go into the production instance and set the security on it to allow users to access it.  Go into Setup, under App Setup go to Develop, under Develop, select Apex Classes.  You will see a Security selection next to your class name, make sure that you have the profiles that need to access it on the selected side.
codewordcodeword

Nice! Thanks.... I figured it out right when you were probably writing this :)

 

Cheers!

elpaso750elpaso750

Hi guys,

 

thanks, this was pretty useful.

 

I just customized the controller quering the DB using SELECT and ORDER BY.

 

I'm now stuck with the TEST class.

Should I test only the controller or also the component ?

 

any help/suggestion is appreciated.

 

Thanks

Alex

saintrjsaintrj

I have a simialr, if different issue, and it is complicated by my lack of VF/APEx capability.

 

We have a quote process that uses custom fields on the quote line item object to define allowed multipliers.

 

I want to pull the info from these line items into the approval email so that the approver does not have to open the quote to see the line item requirements.

 

If there is a way to do that using VF/APEX, I would be very appreciative.

 

 

Admin1112Admin1112

Hi I followed the same way you have mentioned but still Opportunity Line Items is coming in Random order and not the Opportunity line item sorted order.

 

 

elpaso750elpaso750

Hi,

 

all OK it worked for me and been working for a while now.

 

thanks,

Alex

Admin1112Admin1112

Here is the Class

 

public class SortedLineItemsController {
    public Opportunity opportunity { get; set; }
    
    public OpportunityLineItem[] getSorted() {
        if (opportunity == null || opportunity.opportunityLineItems== null) {
            return null;
        }
        
      
        OpportunityLineItem[] result = new OpportunityLineItem[1];
        for (OpportunityLineItem item : opportunity.opportunityLineItems) {
            result.add(0, item);
        }
        
        return result;
   }
}

 

---------------------------

 

Here is the component

 

 

<apex:component access="global" controller="SortedLineItemsController">
<apex:attribute name="value" type="Opportunity" assignTo="{!opportunity}" description="TODO: Describe me"/>

<apex:repeat var="li" value="{!sorted}">
<tr>
<td>{!li.PriceBookEntry.name}</td>
<td>{!li.Quantity}</td>
<td>${!li.TotalPrice}</td>
</tr>
</apex:repeat>

<!-- Needed to work around limitation that the standard controller cannot "see"
the linkage between the value attribute and the opportunity property -->
<apex:variable var="oli" value="{!value.OpportunityLineItems}" rendered="false">
{!oli.PriceBookEntry.name}
{!oli.Quantity}
{!oli.TotalPrice}
</apex:variable>
</apex:component>

---------------------





 

Admin1112Admin1112
I have posted my code, component and email template can you tell me where am i going wrong.
Admin1112Admin1112
<table border="1" >
<tr >
<th>Product Name</th><th>Quantity</th><th>Unit Price</th><th>Total Price</th>
</tr>
<SortedLineItemsController value="{!relatedto}"/>
<apex:repeat var="opp" value="{!relatedTo.OpportunityLineItems}">
<tr>
<td> {!opp.PriceBookEntry.name} </td>
<td align="right">{!ROUND(opp.Quantity,0)}</td>
<td align="right">$ {!ROUND(opp.UnitPrice,0)}</td>
<td align="right">$ {!ROUND(opp.TotalPrice,0)}</td>

</tr>
</apex:repeat>
</table>
elpaso750elpaso750

try this

 

<td>{!li.PriceBookEntry.Product2.name}</td>
<td>{!li.Product2.Quantity}</td>
<td>${!li.Product2.TotalPrice}</td>
elpaso750elpaso750

And :

 

{!oli.PriceBookEntry.Product2.name}
{!oli.Product2.Quantity}
{!oli.Product2.TotalPrice}
Admin1112Admin1112
Alex,

I tried to change that in the component and getting following error while saving

"Invalid field Product2 for SObject OpportunityLineItem
"
Admin1112Admin1112
line Items are displaying in Email template but it is in random order it should consider the order that is there in the Opportunity.
elpaso750elpaso750

And for the class

 

instead of :

 

 

 OpportunityLineItem[] result = new OpportunityLineItem[1];
        for (OpportunityLineItem item : opportunity.opportunityLineItems) {
            result.add(0, item);

 

 

try querying the DB :

 

OpportunityLineItem[] result = [select Id, PricebookEntry.Product2.Name, Description, ........ from opportunityLineItem where (OpportunityId=:Opportunity.Id) ORDER BY PricebookEntry.Product2.Name];

 

rgds,
Alex

elpaso750elpaso750
Sorry :
<td>{!li.PriceBookEntry.Product2.Quantity}</td>
<td>${!li.PriceBookEntry.Product2.TotalPrice}</td>
elpaso750elpaso750
And :

{!oli.PriceBookEntry.Product2.name}
{!oli.PriceBookEntry.Product2.Quantity}
{!oli.PriceBookEntry.Product2.TotalPrice}
elpaso750elpaso750
go for

<td>{!li.PricebookEntry.Product2.Name}</td>
<td>{!li.Quantity}</td>
<td>${!li.TotalPrice}</td>
</tr>
</apex:repeat>

<!-- Needed to work around limitation that the standard controller cannot "see"
the linkage between the value attribute and the opportunity property -->
<apex:variable var="oli" value="{!value.OpportunityLineItems}" rendered="false">
{!oli.PricebookEntry.Product2.Name}
{!oli.Quantity}
{!oli.TotalPrice}
spielsspiels
FWIW you can now do this with a 3rd party addin. Cross object merge fields in Salesforce email templates. You can even merge the template into Outlook. It's pretty easy. You can learn more here (http://www.contactmonkey.com/blog/cross-object-fields-in-salesforce-email-templates-fixed).
 
MRDJMRDJ
Hi,
I need help. How to refer the child object field names in the subject of email template. In this case Opportunity Line item field, can we add in the subject?