Apex Code Development You need to sign in to do that
Don't have an account?
KerryT Calculate Total Expenditure
Hi
I would like to create a custom field on the Account tab that calculates a customer's total expenditure. The only way I can think of doing this is to add all the Amounts for the Closed Won Opportunities related to the Account. When I try to create a formula in a custom field I only seem to be able to reference field on that tab (Accounts). Does anyone know how to made this work or suggest an alternaltive method?
We do not currently use Products so I would be interested to know if this gives us the functionality I'm after.
Thank you
Kerry
Hi Kerry,
If you have enterprise edition you can achieve this by programming against the API. We created a similar program for a charity here in the UK who wanted to know the total amount of donations made so that they could classify important supporters.
We did this using the AJAX interface and the logic was basically:
1. For each account select all opportunities.
2. Itterate over each opportunity summing the total amount.
3. Write total amount back to the account record.
We then broke this out into a smaller version that ran on a single account and was triggered from a custom link on the page. At the end of the routine the page refreshed showing the new data.
Hope that helps
Gareth.
Thanks Gareth, we have not done a lot of work with the API yet, but I will look into it.
Cheers Kerry
There is a feature on the platform roadmap called a "Summary field" that will allow you to create custom fields that are aggregations -- sums, counts, min, max, etc. -- of fields on child records. This is similar to what you can do with summary reports today, but you will be able to display the summary fields on page layouts, list views, etc.
Approximate timeframe for this feature: 2007.
Cheers,
Eric
Eric Bezar
AppExchange Product Management
<html>
<head>
<script src="http://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js" type="text/javascript"></script>
<script type="text/javascript">
<!--
// Default to true so user will have to confirm if they try to close window
var userClose = true;
// If we are programmatically closing the window, set flag so user is not asked to confirm
function closeWindow(){
userClose = false;
window.close();
}
// Perform query and calculations, if successful, refresh Opp and close popup
function onLoad() {
if (getUnits() == true) {
opener.location.reload();
closeWindow();
}
else {
// Close popup without refreshing Opp
closeWindow();
}
} // onLoad()
// Triggered when the window close event is triggered
function onBeforeUnload() {
// Check to see if user initiated the close or if we tried to close it due to script completion
if (userClose) {
event.returnValue = "Salesforce.com is currently Calculating the Investment Total for this Cancel/Rebill. If you close this window, the calculation may not complete. Please click 'Cancel' to allow calculation to complete.";
} // if (userclose)
} // function onBeforeUnload
function onBlur() {
self.focus();
return false;
} // function onBlur
Array.prototype.map = function(func) { // function called on each element of the array
var ret = [];
for(var x=0;x<this.length;x++) {
func(this[x]);
ret.push(this[x]); // return the entire list
}
return ret.length>0?ret:null;
}
function getUnits(){
// AJAX toolkit init
sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}");
var AmtQuery = sforceClient.query(
"select Id, Amount__c from Cancel_Rebill__c where SFDC_Expense_Header__c = " +
"'{!SFDC_Expense_Header_ID}'" );
if ( AmtQuery.className != 'QueryResult') {
alert ( "Query failed for Cancel/Rebill lines") ;
if (AmtQuery.className == 'Fault' ) alert('fault: '+AmtQuery.toString());
return false;
}
if ( AmtQuery.size < 1 ) {
// alert("No Line Items in this Cancel/Rebill?");
return false;
}
var Total_Investment_Amount__c = 0;
AmtQuery.records.map( function (p) { Total_Investment_Amount__c += p.get("Amount__c") } );
//Update to populate 'total units'
var bean = new Sforce.Dynabean("SFDC_Expense_Header__c");
bean.set("Id", "{!SFDC_Expense_Header_ID}" );
bean.set("Total_Investment_Amount__c", Total_Investment_Amount__c);
var sa = bean.save();
return (sa.success == true);
}// getUnits()
-->
</script>
<title>Calculating Units</title>
</head>
<body bgcolor="#FFFFFF" onBlur="onBlur();" onBeforeUnload="onBeforeUnload();">
<center>
<br>
<table width="100%">
<tr>
<td align=center>
<span class="moduleTitle">Calculating Total... Please Wait</span>
</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td align=center>
<img src="/img/waiting_dots.gif" border="0" width=156 height=34>
</td>
</tr>
</table>
</center>
<script type="text/javascript">
setTimeout("onLoad();",500);
</script>
</body>
</html>
Dude - thanks for posting this!
Im going to go one further and stick a wizard on the front of it so others not so tech savvy can also exploit this until the new functionality comes into play...
Watch this space :)
Regards,
Carl_V
Your code says:
var AmtQuery = sforceClient.query( "select Id, Amount__c from Cancel_Rebill__c where SFDC_Expense_Header__c = " + "'{!SFDC_Expense_Header_ID}'" );
What does this last part of the query do? and how do I get this into my example?
Please help, Thanks!!! :smileyvery-happy:
Carl
You mentioned that you would post a wizard to help those of us who are not as code savy. If you have something could you send it over.
BT
Hi Dutchy
Did you work out how to do this? I have the same problem understanding how to replace.
SFDC_Expense_Header__c = " + "'{!SFDC_Expense_Header_ID}'"
I have a custom object called Account_Module_c with a master relationship to Contracts, and assumed I was trying to relate the contract ID in my Account_Module to that in Contracts, but can't get that to validate.....?
This strikes me as really strange, since the reporting/dashboard functions have no problem giving a total amount by account but there seems to be no way for me to access it for custom fields.
Maybe if I describe my problem someone will be able to help a bit: for reporting reasons, accounting needs a "gross margin" percentage by each quarterly forecast number in our custom forecast report. However, there are often multiple opportunities per account, for different amounts, with different percentage values. Thus, an "average" for gross margin does not display correctly (it does not take total amount into consideration, just the unweighted percentage).
How can i combat this? Is there any way?
I am not just looking for a custom S-Control to calculate gross margins for me, but I cannot get that control you posted to work properly.
We use revenue scheduling, so all i really want is to show a gross margin on a per-account basis for every quarter. For example: Wal-Mart: Q1 23% gm. Q2 27% Gm etc. Dillards: Q1 30% gm. Q2 20% gm. etc.
Well, it's 2007 and I don't see this field type :smileywink:
Anyway, I'm new to Salesforce.com and I've been playing around with creating some custom objects to suit my company and this "Summary" field would certainly be a huge time saver. Trying to develop a billing app where I can add billing item objects and have the amounts from those summarized on the main billing item.
Thanks,
I am happy to report that the attached will probably make this whole post a moot point:
http://blogs.salesforce.com/features/2007/06/roll-up-summary.html
This means that it will NOT support the following:
standard master-detail relationships such as Account to Opportunity, Account to Case, and Opportunity to Opportunity Line Item
Could you please advises me where the Rollup Summary Fields can be accessed? I dont seem to be able to find this function ( I am in the Developer Edition )
Thanks in advance
SL
Please kindly ignore my earlier posting as I missed out the earlier post by OSJ Manager on the RollUp Summary documentation
Regret any inconvenience caused
Best Regards
SL
Roll-ups from opportunity to account are now possible.
Marc