1

We have a visualforce email template which has to be sent via work flow.We need to populate Case object information, related object of the Case called Big_W_Case_PO's fields and the Big_W_Case_PO's related list called BIG_W_Case_PO_PO_Line_Items's fields. When I trying to implement this operation I am getting an error like this "Error: Object type not accessible. Please check permissions and make sure the object is not in development mode: SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object"

I have given the code which I tried. Can anyone please help me out in this issue? enter image description here

public class Email_DC_Receiving
{
public Id caseId {get;set;}
public List<Big_W_Case_PO__c> Details = new List<Big_W_Case_PO__c>();
public List<Big_W_Case_PO__c>getDetails() {
       Details= [select id, name,Case__c,(select id,name,PO_Line_Item__r.Name,Keycode__c from BIG_W_Case_PO_PO_Line_Items__r)from Big_W_Case_PO__c where Case__c=:caseId];
   return Details; 
    }
}

This Component for Keycode 
<apex:component controller="Email_DC_Receiving" access="global">
<apex:attribute name="thiscaseId" type="Id" assignTo="{!caseId}" description="This is CaseId for DC Receiving Email Notification"/> 
<apex:repeat value="{!Details}" var="cas" id="theRepeat">
<apex:repeat var="c" value="{!cas.BIG_W_Case_PO_PO_Line_Items__r}">         
            <apex:outputField value="{!c.Keycode__c}"/>,
             </apex:repeat>  
</apex:repeat> 
</apex:component>

This Component for PO Line Item 
<apex:component controller="Email_DC_Receiving" access="global">
<apex:attribute name="thiscaseId" type="Id" assignTo="{!caseId}" description="This is CaseId for DC Receiving Email Notification"/>
<apex:repeat value="{!Details}" var="cas" id="theRepeat">
<apex:repeat var="c" value="{!cas.BIG_W_Case_PO_PO_Line_Items__r}">
            <apex:outputField value="{!c.PO_Line_Item__r.Name}"/>,            
</apex:repeat> 
</apex:component>

Visualforce Email Template
<messaging:emailTemplate subject="DC Receiving Query Notification – DC: {!relatedTo.Distribution_Centre__r.DC_Number__c} - Vendor - {!relatedto.Vendor_Number__r.Vendor_Number__c} {!relatedto.Vendor_Number__r.Name} " recipientType="Contact" relatedToType="Case">

<messaging:htmlEmailBody >
<html>
      <body>
        <STYLE type="text/css">
          TH {font-size: 11px; font-face: arial;background: #CCCCCC;
               border-width: 0;  text-align: center } 
          TD  {font-size: 11px; font-face: verdana } 
          TABLE {border: solid #CCCCCC; border-width: 0}
          TR {border: solid #CCCCCC; border-width: 5}
        </STYLE>
        <font face="arial" size="2">

          <p>Hi {!recipient.name},</p>
          <p>The below DC Receiving Query has been received and requires action:</p>
    <table cellspacing="0" cellpadding="0" border="0" >
                    <tr>
                        <td align="left"><p style="font-family:Helvetica Neue,arial;font-size:12px;color:#333333;line-height:17px;padding-left:2px;">
                            <b>DC:</b>&nbsp;&nbsp; {!relatedTo.Distribution_Centre__r.DC_Number__c}<br></br>
                            <b>Vendor Number:</b>&nbsp;&nbsp; {!relatedto.Vendor_Number__r.Vendor_Number__c}<br></br>
                             <b>Vendor Name:</b>&nbsp;&nbsp; {!relatedto.Vendor_Number__r.Name}<br></br>
                            <b>Query Type  :</b>&nbsp;&nbsp; <apex:outputField value="{!relatedto.Type}"/><br></br>
                            </p>
                        </td>
                    </tr>
          </table>
<table colunm = "6" border = "5">
            <tr > 

               <th>PO Number(s)</th>
               <th> Buyer Name</th>
               <th> Event Number </th>
               <th> Container Number </th>
               <th> Number Pallets / Cartons </th>
               <th> PO Line Item / Time</th>
               <th> Keycode / Time</th>
               <th> Booking Date / Time</th>


            </tr>
            <apex:repeat var="cx" value="{!relatedTo.Big_W_Case_PO__r}">
              <tr>                  
               <td>{!cx.PO__r.Name}</td>
                <td>{!cx.PO__r.Department_Team__r.Name}</td>
                <td>{!cx.PO__r.Event_Number__c}</td>
                <td>{!cx.PO__r.Container_Number__c}</td>                
                <td>{!cx.PO__r.Number_Pallets_Cartons__c}</td>               
               <td><c:Email_DC_Receiving_PoLineItem thiscaseId="{!relatedTo.id}" /> </td>     
              <td><c:Email_DC_Receiving_Keycode thiscaseId="{!relatedTo.id}" /> </td>        
                <td><c:BIG_W_Locale_FormatDateTime date_Timevalue="{!cx.PO__r.Booking_Date_Time__c}"/></td>
              </tr>              
            </apex:repeat>          
            </table>                    


            <br/>   
            <br/>   
                Please follow up and advise of the steps taken via the following link:<br/>
                <br/> 
                <br/>  

              <b> Kind Regards,</b><br/>
              {!$User.Email_Signature_Rich__c}  

    </font>
      </body>
    </html>
</messaging:htmlEmailBody>

<messaging:plainTextEmailBody >
 Hi {!recipient.name},
  The below DC Receiving Query has been received and requires action:
</messaging:plainTextEmailBody>
</messaging:emailTemplate>

1 Answer 1

2

The inner repeat won't work:

<apex:repeat var="cx" value="{!relatedTo.Big_W_Case_PO__r}">
...
    <apex:repeat var="c" value="{!cx.BIG_W_Case_PO_PO_Line_Items__r}">
        <td>{!c.Keycode__c}</td>
    </apex:repeat>
...
</apex:repeat>

It's similar to how when writing a SOQL query you can go only 1 level "down". For example 1 Account + it's Opportunities is fine but you can't add Opportunity Line Items to the mix.

Bear in mind your table will look weird - potentially with 2 extra <td> in first row, 7 cells in second row etc - depending how many line items are there!

For generic and most flexible solution you'll need a visualforce component with a controller that'll run that query for line items for you. Looks like you already use components for currency formatting.

To save on queries and boost performance probably it's better to have 1 giant component "render me all POs and their Line items for Case #123" rather than "render me PO header and Line items for one PO" component which you'd embed in main <apex:repeat>.

If this sounds like an overkill and you'll really need only this one field from the PO line items - why don't you aggregate them up to a helper text field on the PO header? You'd require a trigger but it'd keep the email template cleaner.

2
  • eyescream : Thanks for the reply. We have tried this plseae see my updated question. The BIGW PO Holds a record(10002) which holds the line PO Line item (0013,0015). Same way Another record(10004) hold a line Item of (0020). But while displaying all the PO Items for a particular case Each Line items should be displayed in their respective row. But it displays all the Line items In the same row. I want to display the Po Line items only on the respective row(record) aprt from dispaly all the Line items from the other BIGW PO.
    – Udaya
    Commented Apr 29, 2014 at 10:46
  • If I understand you correctly you need a 1 component that will render whole table (not like you have it now, small components embedded in <td>). And in that component's controller run query similar to SELECT PO_Header__r.Name, Name FROM PO_Line_Item__c WHERE PO_Header__r.Case__c = :passedCaseId ORDER BY PO_Header__r.Name, Name (of course add more fields as neccessary). Experiment with the query first, when you see the grid that's similar to the end result then start styling it in the VF component.
    – eyescream
    Commented Apr 29, 2014 at 12:16

You must log in to answer this question.

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