Apex Code Development You need to sign in to do that
Don't have an account?
DBManager Sum value from within Repeat tag
I am fairly new to Visualforce, and am trying to get to grips with the more complex sides, such as Custom controllers and extensions, etc.
I have been looking at this problem for a while, and have found a few possible solutions, though none have worked.
The problem:
I am trying to create a list of invoices that can be generated for any particular Account. The list setup is fine, an I am happy with it. What I lack is a final total of the 'Gross Unpaid' column.
Possible solutions:
Following a lot of research, I found the following page:
Sum value that is within apex:repeat
Which suggested there were three ways to do this:
1. You can have a trigger on case on inset, delete, update event you can modify the sum of Cx_Case_Hours__c in this trigger.
2. Also you can use java script code to find the repeat column values and add them through loop which will display in the html variable
3. Or can use a separate function in your controller for the above calculation.
I have now tried all of these, with no joy.
No.1
This didn't work because Trigger wasn't being fired correctly
No. 2
I got an example from this page:
How to Count and Sum the Items and display it outside the apex:repeat
But it didn't work for me (not sure why, it just didn't bring back any values).
No.3
I tried pages like this one:
Can't figure out how to display a simple SUM function on my VF Page
But I'm not confident enough with Extensions to implement it, and the errors I got were a mystery to me!
Can anyone advise a good solution to do this.
My code is below - just to confirm, I want to finish the page with a sum of the Sales_Invoice__r.Gross__c field where Sales_Invoice__r.Status__c = 'Unpaid'.
Hope someone can help. Here's my page:
<apex:page renderAs="pdf" standardController="Account">
<apex:stylesheet value="{!URLFOR($Resource.A4Page, 'A4Page.css')}"/>
<apex:image id="CampdenLogo" value="https://emea.salesforce.com/servlet/servlet.ImageServer?id=01520000000liQP&oid=00D2000000075hl"
width="650" height="200"/><br></br>
<table cellpadding="0" cellspacing="0" align="right">
<tr><td>
<strong>Campden Media</strong><br/>
1 St John's Lane<br/>
London<br/>
EC1M 4PN<br/>
</td></tr>
</table>
<br/><br/><br/><br/><br/>
<table cellpadding="0" cellspacing="0">
<tr><td>
<strong>{!account.Name}</strong><br/>
</td></tr>
<tr><td>
{!account.BillingStreet}<br/>
</td></tr>
<tr><td>
{!account.BillingCity}<br/>
</td></tr>
<tr style="display: {!IF(account.BillingState == NULL, 'none', 'block')};"><td>
{!account.BillingState}<br/>
</td></tr>
<tr><td>
{!account.BillingPostalCode}<br/>
</td></tr>
<tr><td>
{!account.BillingCountry}<br/><br/>
</td></tr>
<tr><td>
<strong>Date:</strong> <apex:outputText value="{0,date, dd/MM/yyyy}"><apex:param value="{!NOW()}"/></apex:outputText><br/><br/>
</td></tr>
</table>
<br/>
<table cellpadding="0" cellspacing="0" align="center" style="font-weight:bold; text-align:center;">
<tr>
<td style="height:30px; width:120px; border-left:1px solid black; border-bottom:1px solid black; border-top:1px solid black;">
Invoice Date
</td>
<td style="height:30px; width:100px; border-left:1px solid black; border-bottom:1px solid black; border-top:1px solid black;">
Invoice No
</td>
<td style="height:30px; width:120px; border-left:1px solid black; border-bottom:1px solid black; border-top:1px solid black;">
Gross
</td>
<td style="height:30px; width:120px; border-left:1px solid black; border-bottom:1px solid black; border-top:1px solid black;">
Due Date
</td>
<td style="height:30px; width:100px; border-left:1px solid black; border-bottom:1px solid black; border-top:1px solid black;">
Days Aged
</td>
<td style="height:30px; width:100px; border:1px solid black;">
Status
</td>
</tr>
</table>
<apex:repeat var="cx" value="{!account.Sales_Invoices2__r}">
<table cellpadding="0" cellspacing="0" align="center" style="text-align:center;">
<tr style="display: {!IF(cx.Status__c == "Unpaid", 'block', 'none')};">
<td style="height:30px; width:120px; border-left:1px solid black; border-bottom:1px solid black;">
<apex:outputText value="{0,date,dd/MM/yyyy}"><apex:param value="{!cx.Invoice_Date__c}"/></apex:outputText>
</td>
<td style="height:30px; width:100px; border-left:1px solid black; border-bottom:1px solid black;">
{!cx.Name}
</td>
<td style="height:30px; width:120px; border-left:1px solid black; border-bottom:1px solid black;">
<apex:outputText value="{0,number, £#,##0.00}"> <apex:param value="{!cx.Gross__c}"/></apex:outputText>
</td>
<td style="height:30px; width:120px; border-left:1px solid black; border-bottom:1px solid black;">
{!DAY(cx.Due_Date_New__c)}/{!MONTH(cx.Due_Date_New__c)}/{!YEAR(cx.Due_Date_New__c)}
</td>
<td style="height:30px; width:100px; border-left:1px solid black; border-bottom:1px solid black;">
{!cx.Days_Aged__c}
</td>
<td style="height:30px; width:100px; border-left:1px solid black; border-bottom:1px solid black; border-right:1px solid black;">
{!cx.Status__c}
</td>
</tr>
</table>
</apex:repeat>
<br/><br/>
<p>
<strong>Total Gross Amount:</strong> <apex:outputText value="{0,number, £#,##0.00}"> <apex:param value="{!account.Gross_Unpaid__c}"/></apex:outputText>
</p>
</apex:page>Thanks in advance!
After much trial and error, I seemed to have fixed it.
I created the following class:
public class MyController { private final Account acct; public MyController(ApexPages.StandardController controller) { Account acct = [SELECT Id FROM Account WHERE Id = :ApexPages.currentPage().getParameters().get('id')]; } public string getTesta(){ return ApexPages.currentPage().getParameters().get('id'); } public integer getListSize(){ Sales_Invoices__c[] invoicelist = [SELECT Id, Gross__c, Company__c FROM Sales_Invoices__c WHERE Status__c='Unpaid' AND (Company__c = :ApexPages.currentPage().getParameters().get('id') OR Parent_Company__c = :ApexPages.currentPage().getParameters().get('id'))]; return invoicelist.size(); } public decimal getTotal(){ Decimal total=0; for(Sales_Invoices__c inv :[SELECT Id, Gross__c, Company__c FROM Sales_Invoices__c WHERE Status__c='Unpaid' AND (Company__c = :ApexPages.currentPage().getParameters().get('id') //OR Parent_Company__c = :ApexPages.currentPage().getParameters().get('id') )]){ total += inv.Gross__c; } return total; } }And I added
{!Total}to the appropriate place in the page.
All Answers
After much trial and error, I seemed to have fixed it.
I created the following class:
public class MyController { private final Account acct; public MyController(ApexPages.StandardController controller) { Account acct = [SELECT Id FROM Account WHERE Id = :ApexPages.currentPage().getParameters().get('id')]; } public string getTesta(){ return ApexPages.currentPage().getParameters().get('id'); } public integer getListSize(){ Sales_Invoices__c[] invoicelist = [SELECT Id, Gross__c, Company__c FROM Sales_Invoices__c WHERE Status__c='Unpaid' AND (Company__c = :ApexPages.currentPage().getParameters().get('id') OR Parent_Company__c = :ApexPages.currentPage().getParameters().get('id'))]; return invoicelist.size(); } public decimal getTotal(){ Decimal total=0; for(Sales_Invoices__c inv :[SELECT Id, Gross__c, Company__c FROM Sales_Invoices__c WHERE Status__c='Unpaid' AND (Company__c = :ApexPages.currentPage().getParameters().get('id') //OR Parent_Company__c = :ApexPages.currentPage().getParameters().get('id') )]){ total += inv.Gross__c; } return total; } }And I added
{!Total}to the appropriate place in the page.
If Sales_Invoices__c is a child of the Account object, couldn't you just have created a roll-up summary field on the Account object and set your filter (Status__c = 'Unpaid') there? That way, you wouldn't need the extension at all.
It's nice idea, but it is not a Master-Detail relationship between the two, and I couldn't make one.
That would be the best solution, though (and there are plenty of Ideas on the Success boards requesting that functionality).