+ Start a Discussion
Will LylesWill Lyles 

How to create a visualforce page that shows all records and related child records

I have the following SOQL query in my custom controller.  I would like to build a page that shows all the parent (case) records and related child (RMA) records in one single table.  I've been able to do this with a single case, but can't seem to figure out how to do it when I want to display all cases.

How would one go about doing this?
 
string searchquery = 'SELECT ID, '
                   + 'casenumber, '
                   + 'subject, '
                   + 'Severity__c, '
                   + 'Case_Type__c, '
                   + 'CreatedDate, '
                   + 'ClosedDate, '
            	   + 'Status, '
                   + 'Status_Comment__c, '
                    + '(select  '
                         + 'Customer_Asset_Type__c, '
                         + ' RMA_Customer_Reported_PO__c, '
                         + ' Date_Received__c, '
                         + 'PO_Contract_Date__c, '
                         + 'RMA_Actual_SN_Returned__c, '
                         + 'RMA_Customer_Reported_Failure_Inform__c, '
                         + 'RMA_Customer_Reported_Part_Number__c, '
                         + 'RMA_Customer_Reported_Qty__c, '
                         + 'RMA_Customer_Reported_SN__c, '
                         + 'Warranty_Status__c '
                         + 'from RMA_Products__r) '
                   + 'FROM Case  '
                   + 'WHERE OwnerId= \'' + UserInfo.getUserID() + '\''
                   + 'ORDER BY casenumber DESC '
                   ;

        caseResults = Database.query(searchquery);

 
Best Answer chosen by Will Lyles
$hwet@$hwet@
Hi William,

Please see the code below. where Account is the parent record and Test__c is child record(related record) .I hope it helps:

VF page:
<apex:page controller="RelatedListInVFPageController">
<apex:pageBlock>
    <apex:repeat value="{!AccountList}" var="t">
        <apex:pageBlockSection title="{!t.name}">
            <apex:repeat value="{!t.Test__r}" var="TestObj">
                <apex:outputText value="{!TestObj.name}" />
            </apex:repeat>
        </apex:pageBlockSection>
    </apex:repeat> 
</apex:pageBlock>
</apex:page>

Controller:
public class RelatedListInVFPageController{
    public list<Account> AccountList{get;set;}       
    public RelatedListInVFPageController(){
        AccountList= [select id,name, (select id,name from Test__r) from Account];
    }
}

All Answers

JayantJayant
How are you planning to display them on VF anyway ? A table with parent and children together would be really odd, especially with as many columns as you have included in your query.

First you should come up with a wireframe on how exactly it would look like on the page. The structure can always be created. You will have to use regular HTML though, rather than PageBlockTable for creating the table. So some <apex:repeat>s with <tr>s and <td>s can do the job. 
Will LylesWill Lyles
I've basically been asked to create an export button for our customers to send all their cases to Excel.  I actually have a ton more columns to add to this, but stripped most out to make it easier to read.  I know how to render the page in Excel, I just need to get the table created. 

Looking for something like this with repeating data. Don't know if I should render this in my controller or use the apex elements to render the data.
<table>
	<thead>
		<th>casenumber</th>
		<th>subject</th>
		<th>Customer_Asset_Type__c</th>
		<th>RMA_Customer_Reported_PO__c</th>

	</thead>
	<tbody>
		<tr>
			<td>casenumber</td>
			<td>subject</td>
			<td>Customer_Asset_Type__c</td>
			<td>RMA_Customer_Reported_PO__c</td>
		</tr>
		<tr>
			<td>casenumber</td>
			<td>subject</td>
			<td>Customer_Asset_Type__c</td>
			<td>RMA_Customer_Reported_PO__c</td>
		</tr>
		etc..
	</tbody>
</table>


 
JayantJayant
If you're going to do an Excel, I would suggest the structure to be similar to a Report export.
That usually has all the columns for parent repeated as many times as there are Children records in the report.

Easiest solution would be to have a wrapper class with all columns (superset of Parent and Children) and simply bind a list of these wrapper objects to a PageBlockTable. The page itself would be really simple.
JayantJayant
Can't you just provide a custom link to a report and let them download the report ?
Where would this button be hosted ? Salesforce, Community, Force.com Site ???
Will LylesWill Lyles
Hi Jayant.  The button will be in our community portal.  We are developing it to become a support site.  Our customers will login and then they will only see their cases. 

I've only been developing in Salesforce for about 3 weeks now. If you have any links to examples of setting up the reports and having them accessible on a portal that would be greatly appreciated. Just need a basic idea of how to get started.

Thanks!
$hwet@$hwet@
Hi William,

Please see the code below. where Account is the parent record and Test__c is child record(related record) .I hope it helps:

VF page:
<apex:page controller="RelatedListInVFPageController">
<apex:pageBlock>
    <apex:repeat value="{!AccountList}" var="t">
        <apex:pageBlockSection title="{!t.name}">
            <apex:repeat value="{!t.Test__r}" var="TestObj">
                <apex:outputText value="{!TestObj.name}" />
            </apex:repeat>
        </apex:pageBlockSection>
    </apex:repeat> 
</apex:pageBlock>
</apex:page>

Controller:
public class RelatedListInVFPageController{
    public list<Account> AccountList{get;set;}       
    public RelatedListInVFPageController(){
        AccountList= [select id,name, (select id,name from Test__r) from Account];
    }
}
This was selected as the best answer
JayantJayant
Just create a regular report with the criteria you need. Save it in a folder that can be read by External users. And expose the Reports tab on your Community. You should be done, no customization.

https://help.salesforce.com/articleView?id=networks_analytics_limitations.htm&type=5
https://www.youtube.com/watch?v=FprCnEnz07U
https://www.youtube.com/watch?v=uJhq2iQ7DSQ
Will LylesWill Lyles
Hi $hwet@,

I think the code you provided got me on the right track.  I still have to work on the formatting a bit, but I am seeing the data from my related list which is what I was struggling the most with. 

This is what I have so for.  The format is a bit strange, but I think I am getting closer now.
 
<apex:pageBlock>
            <apex:pageBlockTable value="{!caseResults}" var="c">
                <apex:column value="{!c.casenumber}"/>
                <apex:column value="{!c.subject}"/>
                <apex:column >
                    <apex:pageBlockTable value="{!c.RMA_Products__r}" var="r">
                        <apex:column value="{!r.RMA_Customer_Reported_SN__c}" />
                        <apex:column value="{!r.RMA_Customer_Reported_Qty__c}" />
                    </apex:pageBlockTable>
                </apex:column>
            </apex:pageBlockTable> 
        </apex:pageBlock>

Thank you and Jayant for your replies.  So far the people on this community have been very helpful!