function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
noedskovnoedskov 

Roll-up summary fields and formula fields

Hi all,

On our opportunity object we have a formula field called "Monthly value" which returns a currency. The formula looks like this (Contract_length_mth__c is a picklist):

Amount  /  VALUE(CASE(Contract_length_mth__c, "1", "1", "3", "3", "6", "6", "9", "9", "12", "12", "15", "15", "18", "18", "21", "21","24", "24", "36", "36", "0"))

Now, on our account object I want to create a field called "Monthly value total" which is a sum of the field above for a certain set of opportunities defined by a filter. According to SFDC's Help & Training section that should be possible:

"Roll-up summary fields can calculate the values of formula fields if they do not contain functions that automatically derive values on the fly, such as NOW or TODAY."

My formula above does not (to my knowledge) use any automatically generated values. However, when creating the roll-up summary field on account and selecting opportunity as the object to summarize (using SUM) only 4 values appear for me to select and none of them is my formula field Monthly value.

Any ideas?

Thanks.

/Søren Nødskov Hansen
AngiemeAngieme
Question.. If you have an available value for contract length to = 0 and you have Amount/zero - won't you get an error? If you had one error on one opportunity (even if the rest were ok) then maybe the roll-up won't work because it can't roll up any error values.
noedskovnoedskov
The last value in the formula (the zero) is simply the default value. That is, if none of the other values match the default value is used.

Now, since the contract length is a picklist the sales rep. will never be able to input something himself but only choose from a set of pre-defined values and none of those are 0 (zero).

So, in our particular case it will never become a problem (as we don't allow 0 as contract length). And to answer your question I think you are right (I'm not 100% sure, though).

Any other suggestions as to how I can achieve my goal of summarizing a formula field on the opportunity object and place the result in a field on account (if not by using the roll-up summary as I mentioned in my first post)?

/Søren Nødskov Hansen
KML1234KML1234
The Salesforce documentation states:

"The value of a formula field can result in "#Error!", which affects the summarized total. If your roll-up summary type is COUNT, records are included regardless of whether they contain a formula field with an error, but when the Field to Aggregate is a formula field that results in "#Error!", calculations of type MIN, MAX, and SUM exclude those formula values."

So it looks like Angieme was correct.

Kevin
noedskovnoedskov
Thanks for the input guys.

I had already read that section but maybe I'm just misunderstanding the phrasing. Here is how I read it:

  • Say you have 5 opportunities that all have a formula field
  • 3 of those formula fields result in a number between 1 and 3 (both included)
  • 2 of those formula fields result in "#Error!"
  • When using COUNT the result of the roll-up will be 5 because the COUNT ignores the "#Error!" message
  • The result of the roll-up when using MIN will be 1, it will be 3 when using MAX and it will be 6 when using SUM because MIN, MAX & SUM cannot process the "#Error!" message and therefore ignores it (or exclude it as they say in the documentation)
Anyway, I tried changing my formula so the default value is 1 instead of 0. By doing so the formula can NEVER encounter division by zero and therefore it should not be possible to run into the "#Error!" message.

However, that didn't seem to solve the problem. I still cannot choose the formula field monthly value on opportunity when creating a roll-up summary field on account.

All ideas are more than welcome as this is starting to annoy me more than it should :smileymad:

/Søren Nødskov Hansen
Angieme1Angieme1

Actually, how I understand it, is if you had 3 correct (1-3) and 2 with an #error, then you would not get a value at all (it would not be 6) it would not even show up as a possible field to summarize on.

- Even though you changed the formula so it can never be zero- are there any existing formulas that have an error? Could you run a report and check?

- One more thing to check would be the lenghth of your field. If the Opportunity length is different from the account field length - that might cause an issue.

I don't think you can use a workflow since saleforce doesn't allow cross-object workflows....

I would check the above and I'll let you know if I have any other ideas.

 

 

 
noedskovnoedskov
Hi Angieme1,

Thanks for your input so far. It's much appreciated!

Right, I have found the reason as to why my roll-up summary field isn't working. It seems I have overlooked a vital paragraph in the Help & Training section. Please see below:

"If your organization has advanced currency management enabled, currency roll-up summary fields are invalid if they are on accounts and summarizing opportunity values or on opportunities and summarizing custom object values."

Needless to say, we use the advanced currency management and my roll-up summary is on account summarizing an opportunity value. Ah, the relief! :)

Anyway, now that I have you on the line (sort of speak) could I perhaps ask you to take a look at another thread/question of mine?

http://community.salesforce.com/sforce/board/message?board.id=general_development&thread.id=22645

Thanks again for all the input!

/Søren Nødskov Hansen
Force2b_MikeForce2b_Mike

noedskov,

 

Not sure if this helps, but another approach to this problem is to use a trigger on the Opportunity to update a custom (number) field with the converted amount. Then create a roll-up of that custom field on the Account.

 

I blogged about this recently and posted the trigger code as well:

http://www.sfcnmore.com/index.php/2009/01/sfc-multi-currency-improvement/

 

Mike

reidjnreidjn

For those interested in checking it out, there is a free AppExchange app called Rollup Helper that helps to build rollup summaries using lookup relationships or in other cases where the platform simply won't let you to. The app has a UI to configure user defined conditional rollups and can even reference source values on related tables.

 

The free version is available here:
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI

 

Enjoy!

 

Jerry Reid

Passage Technology