Salesforce IdeaExchange

1290

Roll-Up Summary Fields: Formulas Rather than Static Criteria

Currently, the Roll-Up Summary Field functionality only supports the use of static criteria for evaluating which records to COUNT, SUM, etc. Certainly, that works in many situations and this has been a great feature. However, there are so many more situations where a Roll-Up Summary field is needed but the necessary logic cannot be defined without the use of a formula. Hence, this idea...

Add the ability to use formulas when creating Roll-Up Summary fields.

Below are some examples of Roll-Up Summary fields that can and cannot be created today, for comparison. Scenario: a custom object called Project has a Mater-Detail relationship with the Opportunity object. In effect, one opportunity can have on or many related projects. Each project has fields for Start Date, Estimated End Date and Actual End Date.

Example 1 - CAN be accomplished today
Create a Roll-Up Summary field on Opportunity which provides a COUNT of all related Projects where Actual End Date is NULL (blank). In effect, a COUNT of all OPEN projects (though a bit suspect as it doesn't account for Projects that might not have started yet).

Example 2 - CANNOT be accomplished today
Create a Roll-Up Summary field on Opportunity which provides a COUNT of all related Projects where Start Date is less than or equal to TODAY and Actual End Date is NULL (blank). In effect, a more accurate COUNT of all open projects (excluding any that have not yet begun).

Example 3 - CANNOT be accomplished today
Create a Roll-Up Summary field on Opportunity which provides a COUNT of all related projects where Estimated End Date equals THIS MONTH and Actual End Date is NULL (blank). In effect, a COUNT of all projects expected to end in the current month but have not yet ended.

I'm sure that there are many other great examples of formula-based Roll-Up sumarry field logic. Many of which likely have nothing to do with date comparisons. Please provide comments with your other examples.

mscotton 12/21/07	Hi CThompson, Thank you for all of the thoughtful ideas and comments. We do support using Formulas in the Filter Criteria for Roll-Up Summary Fields as long as the formulas are not something that is variable based on time or date (as in your examples above). We are calculating and storing the value of the Roll-Up Summary Field, thus if we allowed those types of formulas, we would have to constantly re-calculate and store those values every time the time or date changed. That would have a performance impact on the whole system, which is why we have this limitation. - Mary
gokubi 01/12/08	I understand why you don't do these now, but adding date criteria would be huge to the value of these rollups. I have been writing a lot of Apex triggers that are really just rollups. It would make the platform much more powerful to offload all that Apex to rollup summary fields. I look forward to you guys figuring out the performance issues and taking a huge step forward for the platform!
JasonRogers 01/14/08	I agree with CThompson. I appreciate you sharing why this is a limitation today, but that doesn't equate to a reason to never have this functionality.
warrenlester 02/15/08	I would like to build formalae that would be used to concatenate string fields from related objects. For example, we have a custom object that storesd software license details for an account. It would be great in this case to have the ability to have a single field on the account that would then contain a comma separated list of all licensed products for that account.
RikvdW 02/26/08	I understand that time-based filters are more difficult to implement. But apart from that, the current Roll-Up Summery functionality is quite limited. One of the things I am missing is the ability to have a summery field that equals the value of "field A" in the record which has the maximum "field B"-value. An examples: - Field in Account, with the Owner of the largest open opportunity (max opportunity amount) - Field in Account, with amount, of last won opporunity (max close date)
sandrags 03/22/08	Mary's explanation of the reason why this feature is not available is fine but this doesn't resolve the issue of needing the functionality. As an additional date example: Opportunities for an account that have closed This Year (or Last Year, etc) would be very useful
chris925 06/04/08	While I get the limitation around using date criteria, it would be SO valuable to have it handy. Being able to roll up Activities MTD, Activities YTD, etc under any business-appropriate objects based on the special value criteria would be tremendous. It sounds like the current limitation is based on the "check" being a constant polling - what if it were a push instead (i.e., when a new Activity is created that should be decremented into the roll-up, tick it +1, etc...
scraps 07/15/08	I think this feature would be great, although the impact to performance needs to be fully overcome. I believe performance considerations take precedence. That being said, I go into one particular roll-up summary date field every week to change the date range, because of the inability to use 'THIS MONTH' or 'LAST MONTH' or 'TODAY' in the filter criteria. This is archaic! One way around the performance issue could be to replace the Automatic Calculation option with a 'Schedule Calculation', with Daily / Midnight as the minimum frequency.
tmbarry 08/28/08	How adding more "canned" mathematical functions, like, "average" or "standard deviation"?
marc_pannenberg 10/09/08	I'm trying to do exactly what sandrags is proposing: Adding up all won opportunities closed this year. I'm told by support that this isn't possible. I know it would be if I used VF and and Apex Controller, so why not make i generally available. That way salesforce.com has even more control over the efficiency of the code and its strain on the db.
CThompson 11/24/08	Hi mscotton, Perhaps I've gone blind, but the last time I checked there was no option to display the formula builder interface when creating a roll-up summary field. For example, when defining workflow rules, we have an option to run the rule if the following "criteria are met" or "formula evaluates to true." Selecting the latter provides the formula builder interface and all the glorious flexibility that goes along with it. This is not possible with Roll-up summary fields. I hope you can appreciate the difference and perhaps retract or rephrase your prior assurance that formulas are supported. This might be misleading or confounding to other IdeaExchange visitors.

track comments for this idea

Please log in to post a comment

last 100 promotions:

maksim
mcrist
bshardi
jemo86
mbsac
marc_pannenberg
bhughes
joseph_ferraro
redtag
robertcrav
tmbarry
calista
splashgordon
tevans
tbursey@borland.com
cbetz@kryptiq.com
BradPhillips
scraps
rro
andy_ratike
cilo
georget
Marie_Admin
CM@affy
bolaurent
klineberry
billm
carlsalz
kjoseph
patria.lanfranchi05012008
chris925
vorno
08/2/2007_8:31
kquadrat84
sandrags
bradcurrier
bushranger
RikvdW
susana_nts
ejw
ejwilburn10092007
sherwt
matthewlamb
datarzan
JohanLiljegren
EvanC
AlexCRMmanager
vw
Seth_Schneider
JasonRogers
Anand_-_NPower
kdc
Navigator
hudini007
ar
bigbrownboy
joe_pfeiff
steve_andersen
gokubi
e36mpower
MKPartners.com
Bob@Rupholdt.com
JustWondering
BWMoon
Rusty12
mda_tera
NicoleW
ErikO
hasplu
lahumada
gemini@work
TimMadigan
dfg713
danclements
wgra
miklev
jcohen
dFarns
jisaac
bc4saas
mark2008
flescor
James_McGill
alex.lares@v2sa.com
Suz
KBowen
Peter_Rozek
quick13
Ben-Generate
nilesh_v
builderadmin
michele_p
Hayley
ta_invisible
wennerj
joshsutterfield
ols
denizuner
08/7/2007_16:55
swireadmin

Resources

Ideas Under Consideration

Successforce.com

Roll-Up Summary Fields: Formulas Rather than Static Criteria - Salesforce IdeaExchange

Salesforce IdeaExchange

Roll-Up Summary Fields: Formulas Rather than Static Criteria

Search Community

Categories

1-800-NO-SOFTWARE | Privacy Statement | Security Statement