0

I have hit a roadblock on an VF email template I am working on, and would appreciate any help you all may be able to offer.

I have the following code where I need to set up an email to an outside party when a contract is created from an Won Opportunity for an order summary. Seems when I travel from the Contract to the Won Opportunity and then down to its child line items I get the following error:

Error: Aggregate Relationship is used in an unsupported complex expression containing 'SBQQ__Opportunity__r.opportunitylineitems'

Can anyone look at my below code and tell me what I am doing wrong, and point me in the right direction by manipulating my code below to try?

Thank you again, very much appreciate any help!

Email Template Code:

<messaging:emailTemplate recipientType="Contact"
    relatedToType="Contract"
    subject="ORDER SUMMARY_Subscription Contract Number {!relatedTo.ContractNumber}_PO:{!relatedTo.SBQQ__Opportunity__r.Customer_PO__c}"
    replyTo="[email protected]" >
    
<messaging:htmlEmailBody >        
    <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>
        <div style="text-align:left;">
        <apex:image id="theImage" value="https://xxx.my.salesforce.com/servlet/servlet.ImageServer?id=0153j00000AlYgC&oid=00D3j00000024KB"  width="465" height="84" alt="xxx" />
        </div>
        <br/>
        <br/>
        <br/>
        <font face="arial" size="5">
        <b>Order Summary</b><br/>
        <br/> </font>
        <font face="arial" size="2">
        <b><u>General Information</u></b><br/>
        Sales ID: {!relatedTo.SBQQ__Opportunity__r.Opp_Num_Only__c}<br/>
        Subscription Contract Number: {!relatedTo.ContractNumber}<br/>
        Subscription Contract Start Date: {!relatedTo.StartDate}<br/>
        Subscription Contract End Date: {relatedTo.EndDate}<br/>
        Subscription Contract Term Length: {!relatedTo.ContractTerm} months<br/>
        Document Date: {!month(relatedTo.Todays_Date__c)}/{!day(relatedTo.Todays_Date__c)}/{!year(relatedTo.Todays_Date__c)}<br/>
        PO #: {!relatedTo.SBQQ__Opportunity__r.Customer_PO__c}<br/>
        Account Manager: {!relatedTo.SBQQ__Opportunity__r.Owner_Full_Name__c}<br/>
        <br/>
        </font>
        <b><u>Sold-To-Party</u></b><br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Name__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Street__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_City__c}, {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_State__c} {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Zip__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Country__c}<br/> 
        E-Mail: {!relatedTo.SBQQ__Opportunity__r.Bill_To_Contact_Email__c}<br/>
        <br/>
        <b><u>End User</u></b><br/>
        {!relatedTo.SBQQ__Opportunity__r.Account_Name__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Account_Street__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Account_City__c}, {!relatedTo.SBQQ__Opportunity__r.Account_State__c} {!relatedTo.SBQQ__Opportunity__r.Account_Zip__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Account_Country__c}<br/>
        <br/>
        <b><u>Bill-To-Party</u></b><br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Name__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Street__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_City__c}, {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_State__c} {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Zip__c}<br/>
        {!relatedTo.SBQQ__Opportunity__r.Bill_To_Account_Country__c}<br/> 
        <br/>
        <font face="arial" size="2">
        <p><b>Below contains a list of products ordered:</b></p>  
        <p/>                  
       <table border="5" >
                 <tr > 
                     <th>Product Name</th><th>Description</th><th>Quantity</th>
                  </tr>
    <apex:repeat var="opp" value="{!relatedTo.SBQQ__Opportunity__r.OpportunityLineItems}">
       <tr>
           <td>{!opp.PriceBookEntry.name}</td>
           <td>{!opp.Description__c}</td>
           <td>{!ROUND(opp.Quantity,0)}</td>
       </tr>
    </apex:repeat>                
       </table>
       <p/>
 </font>
       
        </body>
    </html>
</messaging:htmlEmailBody> 


<messaging:plainTextEmailBody >
Dear {!recipient.name},
 
Below contains a list of products ordered.

Account: {!relatedTo.SBQQ__Opportunity__r.Account.name}
Opportunity Close Date: {!relatedTo.SBQQ__Opportunity__r.CloseDate}


[ Product Name ] - [ Description ] - [ Quantity ]
-------------------------------------------------------------------------

<apex:repeat var="opp" value="{!relatedTo.SBQQ__Opportunity__r.OpportunityLineItems}">
[ {!opp.PriceBookEntry.name} ] - [{!opp.Description__c} ] - [ {!ROUND(opp.Quantity,0)} ]
</apex:repeat>

</messaging:plainTextEmailBody>       
        
</messaging:emailTemplate>
Improve this question
1
  • As noted in the linked question, the email template does not automatically retrieve your quotelineitem records. You'll need to write a Visualforce component to retrieve the line item records and include the component in your template.
    – David Cheng
    Aug 30, 2020 at 19:07

1 Answer 1

Reset to default
-1

I think it's not possible travel from the Contract to the Won Opportunity and then down to its child line items. But Please confirm.

I have an alternate solution for this. Create Visualforce component(to show the Opp Line Items) and refer the component inside the vf template.

Please use the below code:

I have used the two output panel here. because in your template you are showing the Line Items at two places. Due to the reference of output panel you have to change the <messaging:plainTextEmailBody > to </messaging:htmlEmailBody> . I have attached the updated the template code also below. If you don't want this change(<messaging:plainTextEmailBody > to </messaging:htmlEmailBody>) you can go with one more additional visualforce component to display the bottom line items.

    <apex:component controller="contractOppLineItemsController" access="global">
<apex:attribute name="oppId" type="Id" description="Id of Opportunity" assignTo="{!optyId}"/>
<apex:attribute name="show1" type="boolean"  description="" assignTo="{!s1}"/>
<apex:attribute name="show2" type="boolean"  description="" assignTo="{!s2}"/>
<apex:outputPanel rendered="{!s1}">
    <table border="5" >
        <tr > 
            <th>Product Name</th><th>Description</th><th>Quantity</th>
        </tr>
        <apex:repeat var="opp" value="{!lineItems}">
            <tr>
                <td>{!opp.PriceBookEntry.name}</td>
                <td>{!opp.Description__c}</td>
                <td>{!ROUND(opp.Quantity,0)}</td>
            </tr>
        </apex:repeat>                
    </table>
</apex:outputPanel>
<apex:outputPanel rendered="{!s2}">
    <apex:repeat var="opp" value="{!lineItems}">
        [ {!opp.PriceBookEntry.name} ] - [{!opp.Description__c} ] - [ {!ROUND(opp.Quantity,0)} ]
    </apex:repeat>
</apex:outputPanel> </apex:component>

Visualforce Apex Controller:

public class contractOppLineItemsController {
public Id optyId {get;set;}
public boolean s1 {get;set;}
public boolean s2 {get;set;}
public List<OpportunityLineItem> getlineItems()
{
    List<OpportunityLineItem> lineItems;
    lineItems = [SELECT PriceBookEntry.name, Description__c, Quantity FROM OpportunityLineItem WHERE OpportunityId =: optyId];
    return lineItems;
}}

VF Email Template: Please handle if there is any ui changes is required for bottom table.

use the email template code from the below developer forum. I have updated your code there.

https://dfc-org-production.force.com/forums/ForumsMain?id=9062I000000IOwmQAG

Thanks, Maharajan.C

Improve this answer
0

Not the answer you're looking for? Browse other questions tagged .