+ Start a Discussion
sgottreusgottreu 

Display Quote by Looping OpportunityLineItems by Product Family

I am working with a client that wants to produce a quote that lists all OpportunityLineItems sub-divided by Product Family. So Family A all grouped together with a subtotal and then Family B and so on.  Then when all items are outputted, to give the grand total.
I've modified some of the sample code and can get the basic output.  It will list all OpportunityLineItems but I now need to be able to separate by product family and add in a subtotal.

public class mySecondController {
    public Opportunity getOpportunity() {
        //Manually entering ID for testing purposes
        ID id = '0068000000NyTHe';
        return [SELECT Id, Name, (SELECT Quantity, UnitPrice, TotalPrice,
                    PricebookEntry.Name,
                    PricebookEntry.Product2.Family
                    FROM OpportunityLineItems) FROM Opportunity where Id = :id];
                    // where id = :ApexPage.currentPageReference().getParameters().get('id')];
    }

    public String getName() {
        return 'My Second Custom Controller';
    }
}

<apex:page controller="mySecondController" tabStyle="Opportunity" showHeader="false" renderas="pdf">
    Opportunity: {!Opportunity.Name}
    <br/>
    Id: {!Opportunity.Id}
    <br/>
        
    <apex:repeat value="{!Opportunity.OpportunityLineItems}" var="line">
      <p>Name: {!line.PricebookEntry.Name}<br/>
      Family: {!line.PricebookEntry.Product2.Family}</p>
    </apex:repeat>  
    
</apex:page>
Ron HessRon Hess
You will need a list of line items for each family.  ends up with several repeats, one for each family, each fed with a seperate list, and a subtotal getter for each family.
sgottreusgottreu

Well, let's take this a step at a time.

 

1. Querying Product Family

 

I have used the ApexExplorer and I have yet to be able to find the table that holds the items for the ProductFamily column.  I can't do a DISTINCT so I need to be able to query just those values. 

Or when you said "ends up with several repeats" were you meaning just hard coding the repeats into the page and hardcoding the WHERE statement for each query.

 

2. Calculating Subtotal 

 

I've updated the code to calculate the subtotal for each section.

 

public class mySecondController {
    public Opportunity getOpportunity() {
        //Manually entering ID for testing purposes

        ID id = '0068000000NyTHe';
        Integer subtotal;
       
        List<Opportunity> opportunityList = [SELECT Id, Name, (SELECT Quantity, UnitPrice, TotalPrice,
                   PricebookEntry.Name,
                   PricebookEntry.Product2.Family  
                   FROM OpportunityLineItems) FROM Opportunity where Id = :id];

        for (Opportunity c:opportunityList){
               subtotal = subtotal + c.TotalPrice;
        }

      

         return opportunityList;

    }

    public String getSubtotal() {
        return this.subtotal;
    }
}

 

Yet when I try and save the Apex class, I get the following error from the Development Editor in SF.

 

Error: Compile Error: Invalid field TotalPrice for SObject Opportunity at line 20 column 35

 

I'm assuming I need to reference TotalPrice as an arrary within the for loop but not sure how.

 

I appreciate any help that anyone can provide.

Ron HessRon Hess

1. Yes, repeats for each section , hard coded on the page is the simplest way

 

2. you will have to construct a wrapper class that holds a series of lines and a total for them, you can then build a list of these wrapper objects and send that back to the page to display

DSchullerDSchuller

Ron,

 

The app is incrementing the line items numbers every time we regenerate the quote.  This doesn't make sense.  The quote line numbers should always stay the same for a particular quote.  Any way to fix this?

MMA_FORCEMMA_FORCE

Hi Happy New Year...

Just wondering if you ever got this to work... I need the exact same thing...

Can you post your final code to see what you did?

 

Thanks

 

sgottreusgottreu

So here's what I was finally able to come up with.  I was able to get the values out of the picklist and then I just loop through the list generating a SOQL statement for just that product family.  I created a new field to store the line count and store the subtotal in the Amount field.

 

    //############## Apex Class    

 

    public class TreuTechQuote {
         Schema.DescribeFieldResult F = Product2.Family.getDescribe();
         List<Schema.PicklistEntry> P = F.getPicklistValues();
        
         public Opportunity TreuTech { get; set; }
        
         public ID id = ApexPages.CurrentPage().getParameters().get('id');
        
         public List<Opportunity> TreuTechProducts = new List<Opportunity>();
        
         Integer Counter = 1;
        
         public ApexPages.StandardController controller;

         public TreuTechQuote(ApexPages.StandardController stdController) {
             controller = stdController;
            
             for (Schema.PicklistEntry fam:P){
                 Integer i = 0;
                 String FamilyLabel = fam.GetLabel();
            
                 TreuTech = [SELECT o.Id, o.Name, o.Amount, o.ProductFamily__c,
                             (SELECT op.Quantity, op.UnitPrice, op.TotalPrice,              
                             op.PricebookEntry.Name, op.OpportunityId, op.PricebookEntry.ProductCode,          
                             op.PricebookEntry.Product2.Family, op.LineCount__c                       
                             FROM OpportunityLineItems op WHERE op.PricebookEntry.Product2.Family = :FamilyLabel)
                             FROM Opportunity o where Id = :id];               
                   
                    TreuTech.Amount = 0;
                    TreuTech.ProductFamily__c = FamilyLabel;
                                         
                    for(i=0;i<TreuTech.opportunityLineItems.size();i++) {
                         TreuTech.Amount += TreuTech.opportunityLineItems[i].TotalPrice;  
                         TreuTech.opportunityLineItems[i].LineCount__c = Counter;
                         Counter++;
                    }
            
                 TreuTechProducts.add(TreuTech);
             }
         }
        
         public List<Opportunity> getTreuTechProducts() {
             return TreuTechProducts;
         }
     }

 

     //########## VisualForce Page

 
     <apex:page standardController="Opportunity" renderas="pdf" showHeader="false"
          extensions="ProbeQuote,ProbeContact">
         
          ........  Header Information & Other Pertinent Info ..........
         
     <apex:repeat value="{!TreuTechProducts}" var="p">
        <apex:outputpanel rendered="{!IF(p.Amount = 0, 'false', 'true')}">
          <table border="0" cellpadding="0" width="100%" class="product_family">
               <caption class="caption">{!p.ProductFamily__c}</caption>
               <thead>
                    <tr class="head">
                           <td width="6%" class="head_cell"><font face="Arial">Line</font></td>
                           <td width="49%" class="head_cell"><font face="Arial">Description</font></td>
                           <td width="15%" class="head_cell"><font face="Arial">Product Code</font></td>
                           <td width="12%" class="head_cell">Unit Price</td>
                           <td width="6%" class="head_cell">Quantity</td>
                           <td width="12%" class="head_cell">Total Price</td>
                    </tr>
               </thead>
               <tbody>
                    <apex:repeat value="{!p.OpportunityLineItems}" var="line">
                   
                        <tr class="row">
                              <td width="6%" class="linecount">{!TEXT(line.LineCount__c)}</td>
                              <td width="49%" class="productname">{!line.PricebookEntry.Name}</td> 
                              <td width="15%" class="productcode">{!line.PricebookEntry.ProductCode}</td>
                              <td width="12%" class="unitprice"><apex:OutputField value="{!line.UnitPrice}"/></td>
                              <td width="6%" class="quantity">{!TEXT(line.Quantity)}</td>
                              <td width="12%" class="totalprice"><apex:OutputField value="{!line.TotalPrice}"/></td>
                        </tr>
       
                    </apex:repeat>
                </tbody>               
               <tfoot>
                   <tr class="foot">
                       <td align="right" colspan="5" width="88%" class="subtotal_label">
                              Subtotal:
                         </td>
                       <td align="right" width="12%" class="subtotal">
                           <apex:OutputField value="{!p.Amount}"/>
                       </td>
                   </tr>
               </tfoot>
          </table>
          <br/>
            <br/>
        </apex:outputpanel>     
     </apex:repeat>

vijethavijetha

am doing same kind of work.

 

if i use the code below....

i get error.... in following line.....

for (Schema.PicklistEntry fam){
there should be "=" sign instead of )

vijethavijetha
pls reply .. its urgent !!!!!!
sgottreusgottreu

That line should read...

 

for (Schema.PicklistEntry fam: P){

 

You need to take out the space between the colon and the P.  The editor keeps turning it into a emoticon.

Message Edited by sgottreu on 01-16-2010 05:30 PM
vijethavijetha

hi,

 

 

this code is not working..... i want the visualforce code for opportunitylineitems...

i cannot create class ... because in setup there is no option for Apex Class.....

 

 

pls help me to proceed !!

 

thanku !!! :)

sgottreusgottreu
It's not going to work without the Apex class.