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
Shawn Reichner 33Shawn Reichner 33 

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

Hello awesome Devs! 

I have hit a roadblock on an VF email template I am workign 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="support@criticalstart.com" >
    
<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="Critical Start" />
        </div>
        <br/>
        <font face="arial" size="2">
        <b>6100 Tennyson Pkwy #200</b><br/>
        <b>Plano, TX 75024 </b>
        <b>USA</b><br/>
        <b>Phone: (877) 648-2077</b><br/>
        <b>Fax: (214) 919-4050</b><br/> </font>
        <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>

 
Best Answer chosen by Shawn Reichner 33
Maharajan CMaharajan C
Hi Shawn,

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:

Visualforce Component: ( Name as :contractOppLineItems )

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.

 
<messaging:emailTemplate recipientType="Contact"
    relatedToType="Contract"
    subject="ORDER SUMMARY_Subscription Contract Number {!relatedTo.ContractNumber}_PO:{!relatedTo.SBQQ__Opportunity__r.Customer_PO__c}"
    replyTo="support@criticalstart.com" >
    
<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="Critical Start" />
        </div>
        <br/>
        <font face="arial" size="2">
        <b>6100 Tennyson Pkwy #200</b><br/>
        <b>Plano, TX 75024 </b>
        <b>USA</b><br/>
        <b>Phone: (877) 648-2077</b><br/>
        <b>Fax: (214) 919-4050</b><br/> </font>
        <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/>                  
		   <c:contractOppLineItems oppId="{!relatedTo.SBQQ__Opportunity__c}" show1="True" show2="False"/><br/><br/>
       <p/>
 </font>
       
        </body>
    </html>
    
Dear {!recipient.name},  <br/><br/>
 
Below contains a list of products ordered. <br/><br/>

Account: {!relatedTo.SBQQ__Opportunity__r.Account.name}  <br/><br/>
Opportunity Close Date: {!relatedTo.SBQQ__Opportunity__r.CloseDate}  <br/><br/>


[ Product Name ] - [ Description ] - [ Quantity ]   <br/>
-------------------------------------------------------------------------  <br/><br/>

<c:contractOppLineItems oppId="{!relatedTo.SBQQ__Opportunity__c}" show1="False" show2="True"/><br/><br/>

</messaging:htmlEmailBody>    
        
</messaging:emailTemplate>

Thanks,
Maharajan.C