+ Start a Discussion
Adam RycroftAdam Rycroft 

Unable to select a field for a roll-up summary

Hi there,

I have a formula to track crisis bed nights accessed by clients. The formula returns a value on bed nights at the start date and stops counting when an end date is added. The formula returns a numerical value.

VALUE(TEXT(IF(NOT(ISBLANK( Date_of_Exit__c)),  Date_of_Exit__c  -  Date_of_Entry__c ,  TODAY()  -  Date_of_Entry__c )))

I want to add this value into a roll-up summary on the client's account to show how many nights within a six month period have been used, however, the field is not an option. How can I get this work?.
Parker EdelmannParker Edelmann
You will need clients to be the master object of bed nights. Choose Sum as the type of Roll-up Summary and use your formula as the field to aggregate. Insert criteria below. Please let me know if this does the trick.

Adam RycroftAdam Rycroft
Hi Parker,

Yes, the client is the master object of the bed night. When I select sum, the field to aggregate dropdown has no options to choose from though. The formula is returning a number so I'm confused why it's not displaying as an option under sum.
Parker EdelmannParker Edelmann
I recreated the conditions with custom objects in my Developer Edition, and it resulted in the same problem. I researched if you could use formulas in Roll-up Summary fields, and you can, but on certain conditions. I found this help article:


Here's a snippet of the article I found as the first point under Management:

"If a roll-up summary field doesn’t contain cross-object field references or functions that derive values on the fly, such as NOW orTODAY, it can calculate the values of formula fields."

To re-phrase that, you may use a formula as a summarizable field if it doesn't reference fields on other objects and does not use in any way shape or form, functions like NOW and TODAY. Therefore, a roll-up summary field will not work at this moment in time. Also while I was researching, the only work-around I saw was to write Apex. If you think about it, that truly is the only answer. Seeing as how Roll-up Summary fields won't work, and processes/workflows can't do on-the-fly calculation, we're left with apex or using a report instead of a field. I don't like being the bearer of bad news, but they do have this rule in place so that it isn't constantly taking up resources. If you only need the information, and don't require it to be on the page layout, I'd recommend a report. Sorry I couldn't help you further.

Thanks for reading,