Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mohammed AzarudeenMohammed Azarudeen 

Help to reduce formula field compiled size

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 http://salesforce.stackexchange.com/questions/153040/case-response-time-calculation-in-formula-field/153046?noredirect=1#comment224405_153046 for scenario description.

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?. 
Alain CabonAlain Cabon
Hello,

If you are blocked by the limit size for this formula and given that this value is just used in a page, you can transform the formula in apex and used the new calculated field in a visualforce page containing just one field.

Floor and Mod exist in the class Math, the rest is just string formatting (class String)

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_math.htm

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_string.htm

Regards.
Alain CabonAlain Cabon
Hello,

The translation in Apex seems fully automatic. This is an example of the class helper that you need to call with caseOwnerChangedOn__c or Date.now().getTime() 
public class remaining_response_time {
    public static String get_remaining_response_time(DateTime ResponseTime,DateTime caseOwnerChangedOn_or_now,DateTime createddate) {
        double value1 = Math.floor((((CreatedDate.getTime() + ( ResponseTime.getTime() /1440 )) - caseOwnerChangedOn_or_now.getTime() ) * 24 * 60) / 60);
        double value2 = Math.mod((((CreatedDate.getTime() + ( ResponseTime.getTime() /1440 )) - caseOwnerChangedOn_or_now.getTime() ) * 24 * 60) ,60);
        double value3 = Math.abs(Math.mod((((CreatedDate.getTime() + ( ResponseTime.getTime() /1440 )) - caseOwnerChangedOn_or_now.getTime() ) * 24 * 60) ,60));
        String result = '';
        IF(value1 == 0) {
            result = (String.valueOf(value2)).left(2) + ' min';
        } else {
           result = String.valueOf(value1) + ' hr'+ (String.valueOf(value3)).left(2) + ' min';
        }        
        return result;
}

String.left(2) should be replaced in Apex but that is exactly what it is done by the formula basically.

As soon as you have the good result in Apex, the associated visual page is the easiest part

Alain

 
Mohammed AzarudeenMohammed Azarudeen
@Alain Cabon

Thank you very much for your interest in my question. I've did the same thing to show the field in visualforce page. Now i need to send an email Alert to case owner when the time reaches the count 0. I don't know how  to get the count on the fly and send email. You have any suggestions
Alain CabonAlain Cabon
@Mohammed Azarudeen  

1)  There is a PDF of Salesforce of Tips for Reducing Formula Size

Interesting but the first problem here is a big number of calls to functions ( FLOOR TEXT LEFT ... ) and there is not a solution by formula.

https://resources.docs.salesforce.com/204/latest/en-us/sfdc/pdf/salesforce_formula_size_tipsheet.pdf

2) "send an email Alert to case owner when the time reaches the count 0": you need a daily batch (+ an updated flag in the case ideally).

When the time reaches the count 0, there is no external event (no data change in the case) and the processes/workflows need a data change or a fix delay from a stored date for their scheduled jobs (the time which reaches the count 0 is not stored here). Otherwise you store a new calculated date (due date) and you can use it it for automatic scheduled processes. 

Every night, a schedule batch reads all the cases (not flagged), calculates if a time reaches to count 0 for every read case and send the emails and finally, updates the flag "email_sended" (if it could be resend the next day).

3) You can define rules that trigger Time-Based Workflow actions off of any date or date/time field on the Object. ​
https://help.salesforce.com/articleView?id=000005245&type=1

The principle is simple:  When salesforce.com triggers a workflow rule, its time-dependent actions are placed in the workflow queue.
  setup > Monitor > Time-Based Workflow