0

Continued from Case Response Time Calculation In Formula Field

Am trying to calculate remaining response time for the case.

Based on the priority of the case we are setting the remaining time to respond the case(in hours and mins).

Please see this link for description of the scenario.

Important thing is, we need to show the remaining time dynamically. That is, when the page is refreshed after 10 min, it should change the remaining time dynamically. So went for the formula field to calculate. Here is my formula,

IF( caseOwnerChanged__c , 
IF(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) / 60) = 0,
LEFT( TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) ,60)),2 ) 
&' min',
(TEXT(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) / 60)) 
&' hr'& 
LEFT( TEXT(ABS(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) ,60))),2 ) 
&' min')
), 

IF(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) / 60) = 0,
LEFT( TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) ,60)),2 ) 
&' min',
(TEXT(FLOOR((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) / 60)) 
&' hr '& 
LEFT( TEXT(ABS(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - NOW() ) * 24 * 60) ,60))),2 ) 
&' min') 
)
)

caseOwnerChanged__c - Checkbox field, which returns true if owner
changed. When the case owner changes, response time should stop wherever it is.

ResponseTime__c - formula field which returns 30 for P1, 120 for P2, 480 for P3...

caseOwnerChangedOn__c - Returns datetime when owner changed

So, am trying to save this formula but am getting

Error: Compiled formula is too big to execute (9,018 characters). Maximum size is 5,000 characters

I tried putting the formula's

  1. (((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60)
  2. TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60)

in seperate formula fields and referenced in the current main formula but it is reducing only less number of characters.

So, Any other way to simplify this formula?.

5
  • @Eric - we all know the compiled costs of each functions in formula which already salesforce released. But am asking for the help to simplify the formula's in my formula field. Dec 21, 2016 at 8:06
  • You asked for help reducing compiled size. You now have ALL of the sizes so you can modify your formula accordingly. Maybe someone will come along and do it for you...we will see. Formulas are a pain sometimes. If possible at this point I would switch to a trigger if it is possible for your use case.
    – Eric
    Dec 21, 2016 at 8:09
  • @Eric - I tried my level best to reduce the size of the formula to get the result but that compiled size problem forced me to put here and get the suggestions from the experts of formula functions. And I don't think my use case can be done in trigger. Because remaining response time should change when refreshing the page after sometime. So I think formula field is the only way Dec 21, 2016 at 8:12
  • you may be correct. But maybe you can pull a lot of the functionality out into code to populate fields and then the parts that must be in a formula can use those fields to calculate. This would reduce the size. One go the gurus here may be able to help you reduce it if possible though
    – Eric
    Dec 21, 2016 at 8:19
  • @Eric - Thanks for your suggestion. I've pulled the two nested if conditions out of the formula field and built that in trigger and assigned to new support fields. Then used those support fields in the nested if conditions in the formula field and suddenly it reduced to 3047 characters. So, now the compiled character size error is solved Dec 22, 2016 at 5:28

0

Browse other questions tagged .