Thursday 13 April 2017

SObject row was retrieved via SOQL without querying the requested field in Controller

I just struck with one of the issue when developing something in visualforce page.

The problem is when I load the visualforce page am seeing this below error

Error : SObject row was retrieved via SOQL without querying the requested field


I was trying to fetch the field in controller like below,

Public class caseController{

     public Case cas {get; set;}
     private ApexPages.StandardController controller {get; set;}
     public string caseOwnId{get;set;}

     public caseController(ApexPages.StandardController controller) {
        this.controller = controller;
        cas = (Case) controller.getRecord();
        string caseOwnId = cas.OwnerId;
}

When I use the string field caseOwnId in visualforce page to display the owner id, it is saying that 'SObject row was retrieved via SOQL without querying the requested field'. Isn't this weird?.

Usually, we will see this error if we missed the field while querying. right?.

After some time I found the solution for this as well. Ofcourse the solution is very new to me.

The solution is, we can add the field API name in the controller itself. see below,

controller.addFields(new List<String>{'OwnerId'});

We have method called addFields in the controller. By using that, we just need to add the api name of the field to the list. That's it. So, the full constructor would be,


Public class caseController{

     public Case cas {get; set;}
     private ApexPages.StandardController controller {get; set;}
     public string caseOwnId{get;set;}

     public caseController(ApexPages.StandardController controller) {
        this.controller = controller;
        controller.addFields(new List<String>{'OwnerId'});
        cas = (Case) controller.getRecord();
        string caseOwnId = cas.OwnerId;
}

Tuesday 3 January 2017

Case Response Time In Salesforce

I have come across the process entitlement and milestone which is very useful standard salesforce processes to respond the case and manage the case as per SLA.

Salesforce milestones can only be seen on Feed View of the Case record. But my client won't use feed view so we are in a situation to create a custom response time for the case.

Use case: There is no way to know a case with response time SLA expiring. So we need time count as per priority of the case.

We have Priorities P1, P2, P3, P4
Timeframe to respond the P1 case is 30 min
Timeframe to respond the P2 case is 2 hour
Timeframe to respond the P3 case is 8 hour 
Timeframe to respond the P4 case is 24 hour

  1. When P1 case is created I need to show the remaining time to respond the case in Custom Field of the object
  2. Remaining response time should change dynamically when page refreshes
  3. When Remaining response time count comes down to 0 min send an email alert to case owner saying that the case SLA is expired.
  4. When Case is assigned to user/owner is changed, Remaining Response time should stop wherever the count it is.
  5. Case Remaining Response Time should go in negative if SLA is expired.
  6. Format of the remaining response time should be,
                         Response Time - 30 min (because timeframe for P1 is 30 min)
                         Response Time - 1 hour 30 min (after 30 min of P2 case. Because P2 timeframe is 2 hr)
                         similarly for remaining priorities 

Solution:  Formula Field with trigger.

Custom Fields:
  1. Response Timeframe - Formula Field(Number) - Return 30 min for P1, 120 min P2, 480 for P3, 1440 for P4
  2. SLA Expiry Remaining Time - Formula Field to show dynamic time count
  3. SLA Expiry Support Field1 - Number(18,0) - To use in trigger
  4. SLA Expiry Support Field2 - Number(18,0) - To use in trigger
  5. Is Owner Changed -  Checkbox - Check when owner is changed in Trigger
  6. Owner Changed On - DateTime - Catch the DateTime when owner changed in Trigger
Response Timeframe =

IF( ISPICKVAL( Priority , 'P1') , 30, IF(ISPICKVAL( Priority , 'P2'), 120, IF(ISPICKVAL( Priority , 'P3'), 480, IF(ISPICKVAL( Priority , 'P4'), 1440, NULL) ) ) )


Trigger :

trigger caseTrigger on Case ( before insert, after insert, before update, after update) {
    
    if(trigger.isAfter && trigger.isInsert){
        List<Case> updateList = new List<case>();
        List<Id> currentcase = new List<Id>();
        for(Case caseLoop : trigger.new){
            Case myCase = trigger.new[0];
            currentcase.add(myCase.Id);
        }
        List<Case> updcase = [select id, caseResponseTime__c, CreatedDate, SLA_Expiry_Support2__c from case where id = :currentcase];
        for(Case caseLoop : updcase){
        //(((CreatedDate + ( caseResponseTime__c /1440 )) - NOW() ) * 24 * 60) / 60;
        integer responseTime = integer.valueOf(caseLoop.caseResponseTime__c);
                Long createdDate = caseLoop.CreatedDate.addMinutes(responseTime).getTime();
                Long currentDate = System.now().getTime();
                Long milliseconds = createdDate - currentDate;
                Long seconds = milliseconds / 1000;
                Long minutes = seconds / 60;
                Long hours = minutes / 60;
                caseLoop.SLA_Expiry_Support2__c = hours;
            updateList.add(caseLoop);
        }
        if(updateList.size() > 0){
           update updateList;
        }
    }
    if(trigger.isBefore && trigger.isUpdate){
        for(Case caseLoop : trigger.new){
            Case cas= trigger.oldmap.get(caseLoop.Id);
            if(caseLoop.OwnerId!= cas.OwnerId){
                caseLoop.caseOwnerChanged__c = true;
                caseLoop.caseOwnerChangedOn__c = system.now();
                
                //(((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) / 60
                integer responseTime = integer.valueOf(caseLoop.caseResponseTime__c);
                Long createdDate = caseLoop.CreatedDate.addMinutes(responseTime).getTime();
                Long caseOwnerChangedDate = caseLoop.caseOwnerChangedOn__c.getTime();
                Long milliseconds = createdDate - caseOwnerChangedDate;
                Long seconds = milliseconds / 1000;
                Long minutes = seconds / 60;
                Long hours = minutes / 60;
                caseLoop.SLA_Expiry_Support1__c = hours;
                
            }
            else{
                caseLoop.caseOwnerChanged__c= false;
            }
        }
        
    }

Utilize the supported fields and Owner fields in main SLA Expiry Remaining Time Formula Field

SLA Expiry Remaining Time =
IF( caseOwnerChanged__c , 
IF(FLOOR(caseSLAExpirySupport1__c) = 0, 
LEFT( TEXT(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) ,60)),2 ) 
&' min', 
(TEXT(FLOOR(caseSLAExpirySupport1__c)) 
&' hour '& 
LEFT( TEXT(ABS(MOD((((CreatedDate + ( ResponseTime__c /1440 )) - caseOwnerChangedOn__c ) * 24 * 60) ,60))),2 ) 
&' min') 
), 

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

)


That's it. Now SLA Expiry Remaining Time formula field is sets up. It will change the value when time reduces.

SLA Expiry Response Time