+ Start a Discussion
BshardiBshardi 

How to Sum Fields using Apex in Visualforce Email Template

I'm still new to visualforce and apex but I'm trying to figure out how to calculate the sum of fields in an visualforce email template.

 

I am making a receipt that can be emailed and I want to sum the total of payments made. I'm using an app called pervasive integrated credit card processor. The payments object's parent is Accounts. The visual force template is under Opportunities. 

 

It would be nice if I could do a simple roll-up summary field on the opportunity to sum the payments and then pull the amount but since payments is a child of accounts it does not allow me to do this. 

 

So, saying all that I just need to sum the payments where the opportunity ID is the ID of the current opportunity being used. 

 

How would I code this? Is there an easier way?

jkucerajkucera

Sounds like you want an RSF of Account Payments to shown on the template.  If you can't use an RSF on Account, you'll have to fake it w/ triggers on Payment:

  • Create custom field on Account: Total_Payments__c
  • Create insert trigger on payment: Total_Payments__c+=This Payment
  • Create an Update trigger on payment: Total_Payments__c+=trigger.new - trigger.old
  • Create a before delete trigger on payment: Total_Payments__c-=This payment
BshardiBshardi

John,

 

Thanks for the help. I was able to do a RSF on the Account side, only problem is it totaled all payments on the account; which initially sounds right but here is the problem. If I have multiple opportunities on an account with payments on each it sums all the payments on both opportunities together and then shows that total. So what happens is I end up with a Receipt that shows the product total as $1,000.00 but the Payment Total as $1,500.00 (just and example imagining the 2nd opportunity has a payment of $500). 

 

Does that make sense?

 

So what I really need is a way to sum just the payments for the opportunity it was made on. Any ideas?

jkucerajkucera

Honestly, I was wondering why you put payments tied to Account instead of tying them to Oppty.

 

The easiest approach would be to create the lookup from payment to Oppty, RSF sum on Oppty, and then RSF sum the Oppty payments on Account.  

 

Not sure if you can do an RSF on an RSF though.  

 

Alternately, you could keep your lookup to Account, and add a 2nd lookup to Oppty that's required for all payments.  You still might be able to do the RSF on oppty then to get the info you need.  Worst case, you could use the triggers approach.

BshardiBshardi

John,

 

You're not alone in wondering why the payments is under Accounts. Unfortunately, I did not make this app. It was something we got from the app exchange. Everything works great on it except for this one thing. I've talked with the company and they said a fix was coming in the new version but I don't know when that will be. 

 

I'll keep looking

 

Thanks

BshardiBshardi

What about this:

 

Below is some code I'm using in the visualforce email template to run through all the opportunity products listed on the opportunity. 

 

Is there a way using Apex or Javascript that I can take the total of all the records in the last line (The one with a class of totalcol2) and sum them together? If so I could do the same thing with payments by looping through them and then summing them together.

 

Any Ideas?

 

 

 

<apex:repeat value="{!relatedTo.OpportunityLineItems}" var="line">


<tr class="products">
<td align="center"><apex:outputText value="{!line.PricebookEntry.Name}"/></td>
<td align="center"><apex:outputField value="{!line.ListPrice}"/></td>
<td align="center"><apex:outputField value="{!line.UnitPrice}"/></td>
<td align="center"><apex:outputField value="{!line.Quantity}"/></td>
<td align="center"><apex:outputField value="{!line.Discount_Amount__c}"/></td>
<td class="totalcol2" align="center" width="17%"><apex:outputField value="{!line.TotalPrice}"/></td>
</tr>


</apex:repeat>

 


 

jkucerajkucera

I'm 90% sure you can't sum in a VF email template, but I'm checking w/ the email PM

 

 

src0010src0010

 


jkucera wrote:

I'm 90% sure you can't sum in a VF email template, but I'm checking w/ the email PM

 

 


 

Did you ever find out if you are able to SUM in a VF email template?  I was looking into this as well.  Thanks!

 

jkucerajkucera

Thx - was able to track him down.  Turns out you cannot in VF as suspected.  Sorry for the delay.