+ Start a Discussion
Liz Gibbons 16Liz Gibbons 16 

Group records by picklist value in table

I am trying to create a table that displays Asset__c records grouped by a picklist field, Category__c. I've got the table displaying the Categories, but cannot get the Assets to display. Here's what is looks like now: User-added image
Here's the code I've got so far:
Class
public with sharing class ListViewController {
	
	public List<String> categories {get; set;}
	public List<Asset__c> tessa {get; set;}
	
	public ListViewController(){
		categories = new List<String>();
		Schema.DescribeFieldResult fieldResult = Asset__c.Category__c.getDescribe();
		System.debug(fieldResult);
		List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();
		System.debug('--ple-->> ' + ple);
		for(Schema.PicklistEntry f : ple){
			categories.add(f.getLabel());
		}
		tessa = [select Name, Category__c, LastModifiedDate from Asset__c];
	
	}
         
}

Table
<div class="view" id="list">
	<div class="panel panel-default">
		<div class="panel-body">
			<div class="row">
				<div class="col-lg-2">
				<div class="form-group">
					<input type="text" class="form-control input-sm form-control-flat" placeholder="{!$Label.LBL_Search_Asset}" onkeyup="checksearch(this);"/>
				</div>
				</div>
			</div>
			<apex:form id="tableAssets">
				<table id="basic-datatable" class="table table-bordered">
					<thead>
						<tr>
							<th id="first-th" class="click-header" onclick="toogleall(this);"><i class="fa fa-plus-square-o" title="{!$Label.LBL_Show_Hide_All}"></i></th>
							<th width="60%">{!$ObjectType.Asset__c.Fields.Name.Label}</th>
							<th>{!$ObjectType.Asset__c.Fields.LastModifiedDate.Label}</th>
						</tr>
					</thead>
					<tbody>
						<apex:variable var="index" value="{!0}" />
						<apex:repeat value="{!categories}" var="cat">
							<tr class="category-row" id="{!cat}" onclick="togglerows(this);">
								<td class="hidden"><label></label></td>
								<td colspan="3"><i class="fa fa-plus-square-o"></i>&nbsp;&nbsp;<span class="name">{!cat}</span></td>
								<td class="hidden"><span></span></td>
							</tr>
							<apex:repeat value="{!tessa}" var="a">
									<tr class="category-in" style="display:none;">
										<td class="asset-name"><span>{!a.Name}</span></td>
									</tr>
								<apex:variable var="index" value="{!index+1}" />
							</apex:repeat>
						</apex:repeat>
					</tbody>
				</table>
			</apex:form>
		</div>
	</div>
</div>


       
 
KC ShaferKC Shafer
You have all the categories displaying, and then it looks like you have some javascript toggling the items to expand where you will display all of the assets from your query inside each category. It looks like you have the asset name inside a <tr> tag with display:none applied, could that have something to do with it? 

--KC
Liz Gibbons 16Liz Gibbons 16
Hey KC,

That does get me part of the way there. But now I've got every Asset listed under each Category. I'm aiming to have only those Assets with the Category value appear under it in the table.

-Liz
KC ShaferKC Shafer
That makes sense, I sort of figured that was what you were going for. Depending on how you have things set up you can do this one of two ways.

1. If assets are related to the categories they belong to, use a single query to retrieve categories and assets using a subquery. Then when you use an apex repeat on the categories, you can use another repeat on the category.assets. 

2. You create a map of Map<String, List<Asset__c>> where the string is the category name, and then you retrieve it in the visualforce like
<apex:repeat value="{!assetsByCagetory[cat.Name]}" var="a">. You have to do a little work in the controller to set this up.

Both examples are in the code below.

--KC
<apex:page controller="NestedController">
    <h1>Subquery Example</h1>
    <apex:repeat value="{!accounts}" var="a">
        {!a.Name}
        <br/>
        <b>Contacts</b>
        <br/>
        <apex:repeat value="{!a.Contacts}" var="c">
            {!c.Name}
            <br/>
        </apex:repeat>
        <br/>
    </apex:repeat>
    
    <h1>Map Example</h1>
    <apex:repeat value="{!accounts}" var="a">
        {!a.Name}
        <br/>
        <b>Contacts</b>
        <br/>
        <apex:repeat value="{!accountContacts[a.Id]}" var="c">
            {!c.Name}
            <br/>
        </apex:repeat>   
    </apex:repeat>

</apex:page>
public with sharing class NestedController {
    public List<Account> accounts {get;set;}
    public Map<Id,List<Contact>> accountContacts {get;set;}
    
    public NestedController() {
        //subquery example
        this.accounts = [SELECT Name, Id, (SELECT Name, Id FROM Contacts) FROM Account];
    
        //mapped example
        this.accountContacts = new Map<Id,List<Contact>>();
        for(Contact c : [SELECT Name,AccountId FROM Contact]) {
            if(!this.accountContacts.containsKey(c.AccountId)) {
                this.accountContacts.put(c.AccountId, new List<Contact>());
            }
            
            this.accountContacts.get(c.AccountId).add(c);
        }
    }
}


 
Liz Gibbons 16Liz Gibbons 16
Thanks KC, that's super helpful! The main challenge I'm having is that Category__c is a field on Asset__c and not a separate object. So I can get the Asset__c records to display properly using the map, but only if there's a record in each category, which won't always be the case. Any thoughts on how to display the category even if it's empty?
Liz Gibbons 16Liz Gibbons 16
Here's how I've got the class 
public with sharing class ListViewController {
	
	public List<String> catList {get; set;}
	public Map<String, List<Asset__c>> assetMap {get; set;}
	
	
	public ListViewController(){
		catList = new List<String>();
		Schema.DescribeFieldResult fieldResult = Asset__c.Category__c.getDescribe();
		System.debug(fieldResult);
		List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();
		System.debug('--ple-->> ' + ple);
		for(Schema.PicklistEntry f : ple){
			catList.add(f.getLabel());
		}
		
	this.assetMap = new Map<String, List<Asset__c>>();
		for(Asset__c a : [select Name, Category__c, LastModifiedDate from Asset__c]){
			if(!assetMap.containsKey(a.Category__c)){
				this.assetMap.put(a.Category__c, new List<Asset__c>());
			}
			this.assetMap.get(a.Category__c).add(a);
		} 
	
	}

}