0

Updated I've updated the question because it was not clear and some of you gave me hints.What i originally want to achieve is the following example on visualforce page

I want them to be grouped and rendered as:

-Family
 -Brand
  -Product1
  -Product2
  -Product3
-Family
 -Brand
  -Product1
  -Product2
  -Product3

I am trying to create a visualforce page that will group products by Family/Brand. Can anyone advise me how or what is the easiest way to do this? I am trying to render the page as PDF for my sales team.

Controller

    public with sharing class StandardProductDisplayController{ 
public List<Product2> records {get; set;} 
public StandardProductDisplayController(){ 
records = [select Name, ProductCode, Brand__c, Family from Product2 order by Family]; 
} 
}

Apex

   <apex:page controller="StandardProductDisplayController" renderAs="pdf"> 
    <apex:pageBlock title="Product List"> 
        <apex:pageBlockTable value="{!records}" var="record"> 
            <apex:column > 
                <apex:facet name="header">Product Name</apex:facet> 
                <apex:outputText value="{!record.Name}"/> 
            </apex:column> 
            <apex:column > 
                <apex:facet name="header">Product Brand</apex:facet> 
                <apex:outputText value="{!record.Brand__c}"/> 
            </apex:column> 
            <apex:column > 
                <apex:facet name="header">Family</apex:facet> 
                <apex:outputText value="{!record.Family}"/> 
            </apex:column> 
        </apex:pageBlockTable> 
    </apex:pageBlock> 
</apex:page>
1
  • That SOQL query will not execute unless you change GroupParameter to Brand__c. Aggregate queries require that you summarize or group all SELECTed fields.
    – David Reed
    Aug 16 '18 at 14:21
2

Update

If you just want to order your Product2 records by family, do not use aggregate queries. Instead, include an ORDER BY clause:

SELECT Name, Brand__c, Family__c FROM Product2 ORDER BY Family

Original

You cannot select individual fields on each record if you are performing an aggregate query. Each field must be grouped (e.g. GROUP BY Family) or aggregated (e.g. count(Name)).

If you just want to know how many records are in each family, it can be done:

SELECT Family, count(Id) FROM Product2 GROUP BY Family

Now the default alias for each aggregation is expr0, expr1, etc. Since you have just one here, you would use the zero index.

<apex:column value="{!aggregate['Family]} />
<apex:column value="{!aggregate['expr0']} />

Note, however, that aggregate queries support aliasing of these expressions to make your code/markup more readable.

SELECT Family, count(Id) recordCount ...

If you add such an alias, you can change your second column field name from expr0 to recordCount.

2
  • Thanks Adrian for your fast reply, But i am not seeking to find the number of records. Maybe my query and approach is bad. I am seeking to list all products by family and show them on the page. Aug 16 '18 at 14:29
  • Hi Adrian, I've updated the question for more clearance you gave me a good hint. Aug 17 '18 at 8:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.