Apex Code Development You need to sign in to do that
Don't have an account?
sgottreu 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>
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>
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.
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
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?
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
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>
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 )
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.
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 !!! :)