function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Andrew Aldis 6Andrew Aldis 6 

Creating Sub-Tables

I am trying to create a VF page to display on the account page I have an installation object account__r which is a child of account, I also have a Hardware Information object that is a child object of the Installation object.  I would like to create a VF page that displays a pageblock table of the installations and under each installation an expandable sub-table that displays the hardware information records.  I wrote the VF page below and get the following error.  Anyone want to help out a newby on getting this done?

My 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.. Original queryString was: 'SELECT (SELECT (SELECT Carrier_for_Shipment__c, Id, Item_Master__c, Quantity_Ordered__c, Quantity_Shipped__c, Sales_Order__c, Sales_Order_Line_Item__c, Serial_Number_Shipped_from_Denver_Whs__c, Ship_Date__c, Ship_to_Address__c, Shipment_Type__c, Tracking_Number__c FROM Hardware_Information__r), Id, Name, SFDC_Project_Manager__c, SFDC_Project_Name__c, SFDC_Project_Status__c FROM account__r), Id FROM Account WHERE id = '000000000000000''


My Code:
<apex:page standardController="Account">
    <apex:pageBlock >
       <apex:pageBlockTable value="{!Account.account__r}" var="install">
           <apex:column value="{!install.Name}"/>
           <apex:column value="{!install.SFDC_Project_Manager__c}"/>
           <apex:column value="{!install.SFDC_Project_Name__c}"/>
           <apex:column value="{!install.SFDC_Project_Status__c}"/>
           <apex:column breakBefore="True" colspan="2"/>
                   <apex:pageBlockSection title="Hardware Shipment Information" showHeader="true" collapsible="True">
                    <apex:pageBlockTable value="{!install.Hardware_Information__r}" var="hardware">
                      <apex:column value="{!hardware.Shipment_Type__c}"/>
                      <apex:column value="{!hardware.Ship_Date__c}"/>
                      <apex:column value="{!hardware.Item_Master__c}"/>
                      <apex:column value="{!hardware.Sales_Order__c}"/>
                      <apex:column value="{!hardware.Sales_Order_Line_Item__c}"/>
                      <apex:column value="{!hardware.Serial_Number_Shipped_from_Denver_Whs__c}" />
                      <apex:column value="{!hardware.Quantity_Ordered__c}"/>
                      <apex:column value="{!hardware.Quantity_Shipped__c}"/>
                      <apex:column value="{!hardware.Carrier_for_Shipment__c}"/>
                      <apex:column value="{!hardware.Tracking_Number__c}"/>
                      <apex:column value="{!hardware.Ship_to_Address__c}"/>
                   </apex:pageBlockTable>
               </apex:pageBlockSection>
   </apex:pageBlockTable>
</apex:pageBlock>
</apex:page>
Best Answer chosen by Andrew Aldis 6
Neetu_BansalNeetu_Bansal
Any luck Andrew?

Thanks,
Neetu

All Answers

Neetu_BansalNeetu_Bansal
Hi Andrew,

You cannot query more than 1 level as it is salesforce force limitation, well I have one solution for you:

List<Account__c> accounts = [ Select Id, Name, SFDC_Project_Manager__c, SFDC_Project_Name__c, SFDC_Project_Status__c, (SELECT Carrier_for_Shipment__c, Id, Item_Master__c, Quantity_Ordered__c, Quantity_Shipped__c, Sales_Order__c, Sales_Order_Line_Item__c, Serial_Number_Shipped_from_Denver_Whs__c, Ship_Date__c, Ship_to_Address__c, Shipment_Type__c, Tracking_Number__c FROM Hardware_Information__r) FROM Account__c where Account = '000000000000000']; //specify the account Id

This list must be getter properties:

Now on the page use these lists:
<apex:page standardController="Account">
    <apex:pageBlock >
       <apex:pageBlockTable value="{!accounts}" var="install">
           <apex:column value="{!install.Name}"/>
           <apex:column value="{!install.SFDC_Project_Manager__c}"/>
           <apex:column value="{!install.SFDC_Project_Name__c}"/>
           <apex:column value="{!install.SFDC_Project_Status__c}"/>
           <apex:column breakBefore="True" colspan="2"/>
                   <apex:pageBlockSection title="Hardware Shipment Information" showHeader="true" collapsible="True">
                    <apex:pageBlockTable value="{!install.Hardware_Information__r}" var="hardware">
                      <apex:column value="{!hardware.Shipment_Type__c}"/>
                      <apex:column value="{!hardware.Ship_Date__c}"/>
                      <apex:column value="{!hardware.Item_Master__c}"/>
                      <apex:column value="{!hardware.Sales_Order__c}"/>
                      <apex:column value="{!hardware.Sales_Order_Line_Item__c}"/>
                      <apex:column value="{!hardware.Serial_Number_Shipped_from_Denver_Whs__c}" />
                      <apex:column value="{!hardware.Quantity_Ordered__c}"/>
                      <apex:column value="{!hardware.Quantity_Shipped__c}"/>
                      <apex:column value="{!hardware.Carrier_for_Shipment__c}"/>
                      <apex:column value="{!hardware.Tracking_Number__c}"/>
                      <apex:column value="{!hardware.Ship_to_Address__c}"/>
                   </apex:pageBlockTable>
               </apex:pageBlockSection>
   </apex:pageBlockTable>
</apex:pageBlock>
</apex:page>

Thanks,
Neetu
Andrew Aldis 6Andrew Aldis 6
This probably a stupid question but does the list go on the VF page or does it go somewhere else?
 
Neetu_BansalNeetu_Bansal
Hi Andrew,

yes the list will go to page but you have to specify the page extenstion like:

<apex:page standardController="Account" extensions="Your Class Name" >

Thanks,
Neetu
Andrew Aldis 6Andrew Aldis 6
It is not working, I am very new to VF so I am sure I am doing something wrong.  Thanks for your help but this may be a little over my head.
Neetu_BansalNeetu_Bansal
Hi Andrew,

Try this:

public with sharing class AccountController
{
    public List<Account__c> accounts { get; set; }
    
    /**
     * Name: Parameterised Constructor
     * Desc: To initialize values
    **/
    public AccountController(ApexPages.StandardController stdcontroller)
    {
        // To get the account record
        Account acc = (Account)stdController.getRecord();
        
        if( acc != null )
        {
            accounts = [ Select Id, Name, SFDC_Project_Manager__c, SFDC_Project_Name__c, SFDC_Project_Status__c,
                            (SELECT Carrier_for_Shipment__c, Id, Item_Master__c, Quantity_Ordered__c, Quantity_Shipped__c, Sales_Order__c,
                            Sales_Order_Line_Item__c, Serial_Number_Shipped_from_Denver_Whs__c, Ship_Date__c, Ship_to_Address__c,
                            Shipment_Type__c, Tracking_Number__c FROM Hardware_Information__r)
                            FROM Account__c where Account =: acc.Id ];
        }
}

<apex:page standardController="Account" extensions="AccountController">
    <apex:pageBlock >
        <apex:pageBlockTable value="{!accounts}" var="install">
            <apex:column value="{!install.Name}"/>
            <apex:column value="{!install.SFDC_Project_Manager__c}"/>
            <apex:column value="{!install.SFDC_Project_Name__c}"/>
            <apex:column value="{!install.SFDC_Project_Status__c}"/>
            <apex:column breakBefore="True" colspan="2"/>
            <apex:pageBlockSection title="Hardware Shipment Information" showHeader="true" collapsible="True">
                <apex:pageBlockTable value="{!install.Hardware_Information__r}" var="hardware">
                    <apex:column value="{!hardware.Shipment_Type__c}"/>
                    <apex:column value="{!hardware.Ship_Date__c}"/>
                    <apex:column value="{!hardware.Item_Master__c}"/>
                    <apex:column value="{!hardware.Sales_Order__c}"/>
                    <apex:column value="{!hardware.Sales_Order_Line_Item__c}"/>
                    <apex:column value="{!hardware.Serial_Number_Shipped_from_Denver_Whs__c}" />
                    <apex:column value="{!hardware.Quantity_Ordered__c}"/>
                    <apex:column value="{!hardware.Quantity_Shipped__c}"/>
                    <apex:column value="{!hardware.Carrier_for_Shipment__c}"/>
                    <apex:column value="{!hardware.Tracking_Number__c}"/>
                    <apex:column value="{!hardware.Ship_to_Address__c}"/>
                </apex:pageBlockTable>
            </apex:pageBlockSection>
        </apex:pageBlockTable>
    </apex:pageBlock>
</apex:page>

Thanks,
Neetu
Neetu_BansalNeetu_Bansal
Any luck Andrew?

Thanks,
Neetu
This was selected as the best answer
Andrew Aldis 6Andrew Aldis 6
That is a big help still having some trouble but thank you.
Neetu_BansalNeetu_Bansal
Hi Andrew,

What issue you are facing now.

Thanks,
Neetu
Andrew Aldis 6Andrew Aldis 6
Well the requirements have changed and I am trying to do something simular on a different set of objects.  I have the parent object Installations and the Child Object serial numbers which I am showing in a page block table, I only need to show serial numbers that have shipped.  Right now I am using a rendered if function in the page block table which works fine but can be kind of sloppy.  I am trying to modify your controller to work for this now purpose.  SFDC_Project__c is the parent installation object and Serial_Number__r is the child object serial number . But I keep getting errors.  I am sure this is a simple thing but I am not a developer and have not formal training I just really want to learn.

This is what I have

public with sharing class hardwareShippingSerialNumbers {

    public List<SFDC_Project__c> installs { get; set; }
    
    /**
     * Name: Parameterised Constructor
     * Desc: To initialize values
    **/
    public AccountController(ApexPages.StandardController stdcontroller)
    {
        // To get the install record
        install ins = (install)stdController.getRecord();
        
        if( ins != null )
        {
           installs = [ Select Id,
                            (SELECT  Id, Installation__c, SCMC__Serial_Number__c, Shipment_Date_Time__c, Warranty_Level__c,
                            Warranty_Expiration_Date1__c FROM Serial_Number__r  )
                            FROM SFDC_Project__c where install=ins.Id];
        }
    }}