+ Start a Discussion
DBManagerDBManager 

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!

Best Answer chosen by Admin (Salesforce Developers) 
DBManagerDBManager

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

DBManagerDBManager

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.

This was selected as the best answer
J&A_DevJ&A_Dev

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.

DBManagerDBManager

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).