You need to sign in to do that
Don't have an account?
Group record fields in a Visualforce Table
Sometimes we need to build a table for showing grouped records by a particular field. Here I show one way to accomplish this.
For example, suppose we want to show all contacts grouped by company as shown in the following image.
In order to group cells in a html table we could use the attribute rowspan but we need to know how many records will be grouped in each Account. For this we could use a controller that allow us order and manage the records that will be shown in the Visualforce page.
Here is an example that I made for the visualforce and Apex Code.
<apex:page standardController="Contact" extensions="contactsByAccount_extension" > <table border="1" cellspacing="0" width="60%"> <thead> <th> Account </th> <th> Title </th> <th> Name </th> <th> Email </th> </thead> <apex:repeat value="{!contactsByAccount}" var="key" > <apex:repeat value="{!contactsByAccount[key].contactList}" var="keyvalue" > <tr> <td rowspan="{!contactsByAccount[key].numOfContacts}" style="display:{!IF(CASESAFEID(keyvalue.id)==CASESAFEID(contactsByAccount[key].firstOfList), 'table-data','none' )};"> {!keyvalue.Account.name} </td> <td> {!keyvalue.Title} </td> <td> {!keyvalue.Name} </td> <td> {!keyvalue.Email} </td> </tr> </apex:repeat> </apex:repeat> </table> </apex:page>
In the Visualforce page we iterate through a Map that comes from the extension controller. This Map group the records by Account. So for each Account we also iterate through the contacts of that Account.
The first cell, that show the name of the Account ONLY should be displayed in the first iteration, and it's not recomended to use apex variables in an apex:repeat, so a possible solution for this is making a method in the controller that let us know if the record is the first of the list. Thus we use in the style attribute of the <td> tag a conditional for display the cell. If the record ID of the current record is equal to the first of the list then display the account name. The rest of the cells are displayed without condition.
Let's see the extension controller now.
public class ContactsByAccount_extension { public ContactsByAccount_extension(ApexPages.StandardController controller) { } public Map<String,contactosListWrapper> getContactsByAccount(){ List<Contact> result = [SELECT Account.name, Title, Name, Email FROM Contact ]; // Group Contacts by Account Map<String,contactosListWrapper> contactsByAccount = new Map<String,contactosListWrapper>(); for(Contact cont: result){ if(null == cont.Account.name) continue; contactosListWrapper empresa = contactsByAccount.get(cont.Account.name); if(null == empresa){ contactsByAccount.put(cont.Account.name, new contactosListWrapper(new List<contact>()) ); } contactsByAccount.get(cont.Account.name).contactList.add(cont); } return contactsByAccount; } // List of contacts and details class contactosListWrapper { public List<Contact> contactList {get; set;} public Integer numOfContacts { get{ return contactList.size(); } set; } public Id firstOfList{ get{ return contactList[0].Id; } set; } public contactosListWrapper(List<contact> listContacts){ contactList = listContacts; }
The inner class contactosListWrapper is a container of a contact list and give us information about wich is the first and how many of them are.
The extension controller method getContactsByAccount() makes a query of all the contacts and then they are group in the map by Account. For each Account we make a new entry in the Map with the name of the Account (String) and the list of contacts (contactosListWrapper).
I used it to display a list in a VF page and group by Contact. Here is the code.
Several of our attempts and their resulting errors :
Any suggestions? We're new to SalesForce and not java wizzes...
Another issue may be material for separate research or post - in our visualforce page, the results render exactly as desired in HTML and PDF, but when we switch to contentType="application/msWord", the table structure gets weird. Blank cells get added - the more the further you go down the table, making the table resemble a set of stairs.
// code to insert test data...
// ...
// instantiate the controller object first
armNotesByAccount arm = new armNotesByAccount();
// declare a map variable using the specific wrapper type from the controller class
map<string, armNotesByAccount.armNoteListWrapper> mapVar = new map<string, armNotesByAccount.armNoteListWrapper>();
// assign the object to the variable
mapVar = arm.getArmNotesByAccount();
// evaluate results, i.e. match the number of test records
System.assert(mapVar.size() = n );
Controller:
public class GroupingExampleController
{
private List<Account> allAccs {get; set;}
public List<GroupWrapper> groups {get; set;}
public String groupFieldName {get; set;}
public List<SelectOption> groupOptions {get; set;}
public GroupingExampleController()
{
allAccs=[select id, Name, BillingStreet, BillingCity, BillingCountry, Type,
(select id, Name, Email, Phone from Contacts limit 5)
from Account
where Type != null
limit 10];
groupFieldName='Type';
setupGrouping();
groupOptions=new List<SelectOption>();
groupOptions.add(new SelectOption('Name', 'Name'));
groupOptions.add(new SelectOption('BillingCity', 'BillingCity'));
groupOptions.add(new SelectOption('BillingCountry', 'BillingCountry'));
groupOptions.add(new SelectOption('Type', 'Type'));
}
public PageReference regroup()
{
setupGrouping();
return null;
}
private void setupGrouping()
{
Map<String, List<Account>> groupedMap=new Map<String, List<Account>>();
for (Account acc : allAccs)
{
String key=String.valueof(acc.get(groupFieldName));
if ( (null==key) || (0==key.length()) )
{
key='Undefined';
}
List<Account> groupedAccs=groupedMap.get(key);
if (null==groupedAccs)
{
groupedAccs=new List<Account>();
groupedMap.put(key, groupedAccs);
}
groupedAccs.add(acc);
}
groups=new List<GroupWrapper>();
for (String key : groupedMap.keySet())
{
GroupWrapper gr=new GroupWrapper();
groups.add(gr);
gr.accs=groupedMap.get(key);
gr.groupedVal=key;
}
}
public class GroupWrapper
{
public List<Account> accs {get; set;}
public String groupedVal {get; set;}
public Integer count {get {return accs.size(); } set;}
}
}
Visualforce page:-
<apex:page controller="GroupingExampleController" tabstyle="Account">
<apex:form >
<apex:pageBlock >
Group By: <apex:selectList value="{!groupFieldName}" size="1">
<apex:selectOptions value="{!groupOptions}" />
</apex:selectList> <apex:commandButton value="Go" action="{!regroup}"/>
<table border="0">
<apex:repeat value="{!Groups}" var="group">
<tr>
<td colspan="3"><b>{!groupFieldName}:{!group.GroupedVal}</b> - {!group.count} records</td>
</tr>
<apex:repeat value="{!group.accs}" var="acc">
<tr>
<td width="30px"></td>
<td colspan="2"><b>Account:</b>{!acc.Name}</td>
</tr>
<apex:repeat value="{!acc.Contacts}" var="cont">
<tr>
<td width="30px"></td>
<td width="30px"></td>
<td><b>Contact:</b>{!cont.Name}</td>
</tr>
</apex:repeat>
</apex:repeat>
</apex:repeat>
</table>
</apex:pageBlock>
</apex:form>
</apex:page>
Thanks
VisualForce Page:
Controller:
I'm thinking the standard controller needs to be Opportunity instead of OpportunityLineItem and something like this needs to be used. I'm just not sure exactly where.
Any help would be greatly appreciated!