+ Start a Discussion
KerryTKerryT 

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
Gareth DaviesGareth Davies

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.

KerryTKerryT

Thanks Gareth, we have not done a lot of work with the API yet, but I will look into it.

Cheers Kerry

EricBEricB
Hi 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

OSJMgrOSJMgr
You could create a custom S-Control that is attached to a custom link at the top of the account record.  I use the following Java in my S-Control, it works like a charm.  Just change the highlighted fields to fit your needs.  By the way, I do not know Java.  I just found this S-Control on the Appexchange in Salesforce's free Expense application.  Go there to see how it works and then customize this code with your own field names. 
 
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<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>&nbsp;</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>
Carl_V1Carl_V1

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

DutchyDutchy
Regarding the SOQL code that describes the query. I've tried to modify this code for my Org and it returns errors.
 
I have an opportunity and under that opportunity a related object with prices. The total of these prices which are all single records in the price object need to be summarized onto the opportunity page.
 
Field on Opportunity page: Total_of_Prices
Field to summarize on Price page: Customer_Price
 
My Query code looks like this:
 
var AmtQuery = sforceClient.query( "select Id, Customer_Price__c from Price__c where Opportunity__c = " + "'{!Opportunity.Id}'" );

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:

 

 


 
briguybbbriguybb

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

RufussRufuss
Been to busy to build it yet Im sorry - will definitely get to it though - I'll try for next 30 days to have something for you...
DutchyDutchy
"select Id, Amount__c from Cancel_Rebill__c where SFDC_Expense_Header__c = " + "'{!SFDC_Expense_Header_ID}'" );
I understand that the value on the Expense Header ID field needs to be equal to the expense header ID off all the expenses in the related list. (Otherwise you get all expenses back).
 
My question is, How do I find what my "Expense" Header is called (In my case Opportunity header)?
 
I hope someone can help me, cause I'm running out of time . . .
 
:robotvery-happy:
briguybbbriguybb
Based on the package it appears that SFDC_Expense_Header__c is the Parent obejct to SFDC_Expense_Line_Item__c.  So my guess here is that in your Case SFDC_Expense_Header__c would be replaced with Account since Account is the parent to Opportunity. 
 
You may also need some additional code to pull only the won opportunities as well.  Hope this helps somewhat.
WilksyWilksy

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

 

klarsonklarson
So there is actually no way to simply calculate a total amount for an account based on individual opportunities?  (Or at least one that I can access with a professional license)

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?
klarsonklarson

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.

 

lpadminlpadmin
Eric, any update on the status of the "Summary Field" rollout?
MathieuMathieu

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,

OSJMgrOSJMgr
Just spoke with our Salesforce Rep last week.  Guess what...it won't be included in Winter 08 release either.  Bummer!
MathieuMathieu
hmmm... that really does throw a stick in my spokes.  Any ideas on an easy way I can accomplish what I'm trying to do?
rsimmonsrsimmons
Is this available yet?  and will it give you totals from a related list, (I have a related list under opportunities called Suppliers Expenses, I would like to total and populate into an Opportunity Field)?
OSJMgrOSJMgr

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

 

HerosHeros
"Roll-up summary fields support custom master detail relationships only"

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
SL TanSL Tan
Hi Heros
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
SL TanSL Tan
Hi Heros
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
hudinihudini
there is an s-control that does it
Marc PannenbergMarc Pannenberg
This is no longer correct!

Roll-ups from opportunity to account are now possible.

Marc