Wednesday, 15 June 2011

Grouping Records and Children in Visualforce

I've been working this week on producing a Visualforce page custom hierarchy style view of records and their children with additional grouping.  In this scenario I firstly want to display a record and its children in indented form, e.g

Account 1
   Contact 1
   Contact 2
Account 2
   Contact 1
   Contact 2

However, I also wanted to allow the user to group the parent records by particular attributes and display count information, e.g.

Attribute Value 1 - 2 records
   Account 1
      Contact 1
      Contact 2
   Account 2
      Contact 1
      Contact 2
Attribute Value 2 - 1 record
   Account 1
      Contact 1
      Contact 2

And finally, the user could regroup on demand by choosing a different attribute from a drop down list.

As I was looking to combine records and some additional information, I headed for my old friend the wrapper class.  In this case I wanted to wrap the group value, the list of records and the size of the records list.  As an aside, if I didn't need the record count I could probably have achieved this using a Map and Visualforce Dynamic Bindings using the techniques described in this post.

The wrapper class is shown below:

public class GroupWrapper
{
 public List<Account> accs {get; set;}
 public String groupedVal {get; set;}
 public Integer count {get {return accs.size(); } set;}
}

I've created this as an inner class of my controller. My page will be backed by a list of these classes, which I initially build from the constructor, but also rebuild if the user chose to regroup by a different attribute.

The first thing my controller constructor does is to retrieve the master list of records:

allAccs=[select id, Name, BillingStreet, BillingCity, BillingCountry, Type,
       (select id, Name, Email, Phone from Contacts limit 5)
    from Account
    where Type != null
    limit 10];

Note that I'm retrieving the child records through a relationship query. This means that I don't need to worry about managing these in my wrapper class, I can simply navigate to them via the parent record when traversing the list.

Next I define the initial grouping attribute as the Type field and invoke the method that groups the data. This initially traverses the list of accounts and stores them into a Map keyed by the group attribute value, whose value is a list of the accounts whose group attribute matches that value. Once the data is organized, the keyset of the map is traversed and an instance of the wrapper class created for each key/value pair:

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;
 }
}


The final job of the constructor is to create the select options that allow the user to choose a different grouping:


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'));

The full controller is shown below:

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;}
 }
}

The Visualforce markup to display the data is quite straightforward - its a simple matter of generating a table by iterating the list of wrapper classes, iterating the list of accounts contained by each wrapper class, and finally iterating the associated contacts. That, plus a select list that allows the user to choose a different grouping and a command button to action the user's choice, is all there is to it:


<apex:page controller="GroupingExampleController" tabstyle="Account">
 <apex:form >
  <apex:pageBlock >
  Group By: <apex:selectList value="{!groupFieldName}" size="1">
     <apex:selectOptions value="{!groupOptions}" />
  </apex:selectList>&nbsp; <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>

And here's a screen capture of the page in action:

9 comments:

  1. Good one Bob.
    Wondering how to show the products, grouped by Family Name without using Apex code.

    ReplyDelete
  2. I'm assuming you are using Professional Edition for Apex code to be an issue. This is tough, as there's nothing in the standard controller that allows you to group objects in this way.

    I think you'd have to go the Javascript route for that.

    ReplyDelete
  3. Bob, thanks so much for posting. Your blog really helps!

    I like this example, but wonder if you would recommend its use for something a bit more complex? I have a situation where I would like to sort records in groups which are also sorted within parent groups and have been having a bit of a time.

    It seems like it should be straightforward, but I don't know if I should use the wrapper class or something else.

    Anyway, thanks much for your blog and posts!

    Alex

    ReplyDelete
  4. I'd certainly go with the wrapper classes. You may need multiple types of wrapper class, one for the parent, one for the grouped children etc. Essentially what you need to do is come up with a wrapper class hierarchy that matches what you are looking to display on the page.

    ReplyDelete
  5. It looks similar what I want to develop but I am not sure about that. I need to develop a visualforce page that contains list of all users and underneath of each user i need to display total count of opportunity closed/won records that user owns, total count of opportunity closed/lost records that user owns. Its kind of user summary (or user role-up) in this company. we are calling it master user record. and also going to provide search on left or on top so any VP of sales can search any user. if they get user then they get its summary.

    I got search also page-block sections with name of all users. and now i am going to get records of each uses but not sure how am i going to get that.

    ReplyDelete
  6. Awesome, just what I was looking for

    ReplyDelete