+ Start a Discussion
Padmini S 4Padmini S 4 

SOQL Query Issue

We have 4 objects (ORDER, ORDERITEMS,PARTNERFUNCTIONS__C,SCHEDULES__C)
Relationships is: 
PARTNERFUNCTIONS__C is child to ORDER
ORDERITEMS is child to ORDER
SCHEDULES__C is child to ORDERITEMS

we want to generate a custom report in excel sheet.
We are able to fetch all the values except form SCHEDULES__C
Since We are quring from ORDER (Grand Parent Object)

Our code VF PAGE

<apex:page showHeader="fALSE" Sidebar="FALSE"  standardController="Order" extensions="CustomReportController" >


    <table  Id="header" style="width: 100%; " border ="1px" cellpadding="0" cellspacing="0" >  
       

       <tr>
       <td> <b> SFDC SO No </b> </td>
       <td> <b> Order Type  </b> </td>
       <td> <b> Sales Org  </b> </td>
       <td> <b> Distribution Channel  </b> </td>
       <td> <b> Division  </b> </td>
       <td> <b> Sold to Party  </b></td>
       <td> <b> Ship to party  </b> </td>
       <td> <b> PO Number  </b> </td>
       <td> <b> PO Date  </b> </td>
       <td><b> Opp Description  </b></td>
       <td> <b> Item No  </b></td>
       <td> <b> Material  </b></td>
       <td><b>Quantity </b></td>
       <td><b>Price </b></td>
       <td><b>WBS </b></td>
       <td><b>Delivery Priority </b></td>
       <td><b>Plant </b></td>
       <td><b>Partner Functions </b></td>
       <td><b>Partner No </b></td>
       <td><b>Sch Item No </b></td>
       <td><b>Sch Date </b></td>
       <td><b>Sch Qty </b></td>
        </tr>
       
    
        <apex:repeat value="{!wrap}" var="w">
         <apex:repeat value="{!w.OrderItems}" var="w1">
                 <apex:repeat value="{!w.Partner_Functions__r}" var="w2">

         
       <!-- <tr>
        <td><apex:outputText value="{!w.OrderNumber}"/></td>
        <td>empty_1</td>
        <td>empty_2</td>
        </tr>
        --->
         <tr>
                 <td>  <apex:outputText value="{!w.OrderNumber}"/> </td>
                 <td> <apex:outputText value="{!w.SO_Type__c}"/> </td>
                 <td><apex:outputText value="{!w.Sales_Org__c}"/> </td>
       <td> <apex:outputText value="{!w.Distribution_Channel__c}"/> </td>
       <td> <apex:outputText value="{!w.Division__c}"/> </td>
       <td> <apex:outputText value="{!w.Sold_To_Party__r.Name}"/> </td>
       <td> <apex:outputText value="{!w.Ship_To_Party__r.Name}"/> </td>
       <td> <apex:outputText value="{!w.PoNumber}"/> </td>
       <td> <apex:outputText value="{!w.PoDate}"/> </td>
       <td> <apex:outputText value="{!w.SO_Opportunity_Description__c}"/> </td>
       
       
       <td> <apex:outputText value="{!w1.Item_Number__c}"/> </td>
       <td> <apex:outputText value="{!w1.Material_Code__r.Name}"/> </td>
       <td> <apex:outputText value="{!w1.Quantity}"/> </td>
       <td><apex:outputText value="{!w1.ListPrice}"/></td>
       <td><apex:outputText value="{!w1.PID_WBS__c}"/></td>
       <td><apex:outputText value="{!w1.Delivery_Priority__c}"/></td>
       <td><apex:outputText value="{!w1.Plant__c}"/></td>
                         
        
 
<td><apex:outputText value="{!w2.Partner_Function_Type__c}"/></td>
      
       <td><apex:outputText value="{!w2.Partner_Function_Name__c}"/></td>
       <td><apex:outputText value="{!w.Division__c}"/></td>
       <td><apex:outputText value="{!w.Division__c}"/></td>
       <td><apex:outputText value="{!w.Division__c}"/></td>

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


Apex CODE:

public with sharing class CustomReportController {

      
 //public List<OrderItems> ol{get;set;}
public List<Order> wrap {get;set;}
public List<Order> wrap1 {get;set;}


    public CustomReportController (ApexPages.StandardController stdController) {
      /*  wrap = [SELECT OrderNumber , SO_Type__c,Sales_Org__c ,Distribution_Channel__c,Division__c,Sold_To_Party__r.Name,Ship_To_Party__r.Name,PoNumber,PoDate,
        SO_Opportunity_Description__c,Partner_Number__c, (SELECT  Item_Number__c,Material_Code__r.Name,Quantity,ListPrice,PID_WBS__c,Delivery_Priority__c,Plant__c,
        (select Schedule_Date__c,Schedule_Quantity__c from Sales_Order_Line_Item_Schedule__c) FROM OrderItems)
       */ 
         wrap = [SELECT OrderNumber , SO_Type__c,Sales_Org__c ,Distribution_Channel__c,Division__c,Sold_To_Party__r.Name,Ship_To_Party__r.Name,PoNumber,PoDate,
        SO_Opportunity_Description__c,Partner_Number__c,ZD__c,ZM__c,ZS__c, (SELECT  Item_Number__c,Material_Code__r.Name,Quantity,ListPrice,PID_WBS__c,
        Delivery_Priority__c,Plant__c FROM OrderItems) , 
        (Select Partner_Function_Type__c,Partner_Function_Name__c from Order.Partner_Functions__r)
        
        
         FROM Order  ];
       // system.debug ('**********' + wrap.OrderNumber );
    }
}


And the result output is:

desired result

BalajiRanganathanBalajiRanganathan
Looks like you are generating custom report. you can add readonly="true" to the apex:page element and you can use @readonly annotation to the
getter methods. so you have to refactor you code to use the @readonly annotation.
http://www.salesforce.com/us/developer/docs/pages/Content/pages_controller_readonly_context_methodlevel.htm

You can refer this template code below. you have to follow the similar approach to get the grandchild.
List<Account> accList = [SELECT id,(SELECT id from Opportunities),(SELECT id from contact) FROM Account];

Map<Id,Opportuniy> oppMap = new Map<Id,Opportuniy> ([SELECT id, (SELECT id from OpportunityLineItems) FROM Opportunity]);

for (Account acc : accList) {
  for (Opportunity opp : acc.Opportunities) {
     opp.OpportunityLineItems = oppMap.get(opp.id).OpportunityLineItems;
  }   
}
Padmini S 4Padmini S 4
Hi Balaji,

I refered the template code. But while executing it is giving error like this.

Error: CustomReportController Compile Error: Field is not writeable: OpportunityLineItems at line 31 column 6

It is showing OpportunityLineItems  Field is not writeable. Please give some suggestion to overcom this.

 
BalajiRanganathanBalajiRanganathan
can you put your updated code? i am not sure why are using OpportunityLineItems. i just gave it as example.