function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Giorgos LosoffGiorgos Losoff 

Only set days counter to count when case is in a certain status

Hi, I am fairly new to Salesforce and this community, so please bare with me. On our case object, we have a field calls "Days Open" that counts when the case is not in the "Closed" status. However, we have added a status called "Pending" and I have run into a bit of a wall. I talked with SF Support and they suggested I turn to this community.

We want the "Days Open" counter to only count when the case is in "Open" status. Then for the counter to stop when it's moved to "Pending" and then start up again if moved back to "Open" and finally when the case is "Closed" to leave the counter in the final number. Meaning if the case is in open for 5 days, the days open counter says 5; then if I move it to pending it stays at 5 and then if I move it back to open for 3 more days, it would say 8. Then when I close the case, the counter still says 8. Any guidance at all would be appreciated and please let me know if I need to clarify anything further!
Best Answer chosen by Giorgos Losoff
Abdul KhatriAbdul Khatri
Then use the following code
  
global class CaseOpenDaysUpdateJob implements Schedulable {

    public static String jobname = 'Update Case Open Days';
    public static String CRON_EXP = '0 05 0 * *  ?';
       
    public static void Start()
    {
        System.schedule(jobName, CRON_EXP, new CaseOpenDaysUpdateJob());
    }   
    
    
    public static void Stop()
    {
        List<CronJobDetail> jobs = [SELECT Id FROM CronJobDetail WHERE Name = :jobName];
        
        if(!jobs.isEmpty()) {
            for(CronJobDetail job : jobs) {
                Id jobId = [SELECT Id from CronTrigger WHERE CronJobDetailId = :job.Id][0].Id;
                System.abortJob(jobId); 
            }           
        }
    }
    
    global void execute(SchedulableContext SC)
    {
    
        List<Case> caseToUpdate = [SELECT Id FROM Case WHERE Status = 'Open'];
        
        if (caseToUpdate == null) return;
        
        update caseToUpdate;
    
    }
    
}

You need to schedule it. Use the following Command in the anonymous window under Developer Console
  
CaseOpenDaysUpdateJob.start();

To Stop 
CaseOpenDaysUpdateJob.stop();

If you want to test the job 
CaseOpenDaysUpdateJob job = new CaseOpenDaysUpdateJob();
job.execute(null);

Once schedule you can see it by going
User-added image

Right now it is scheduled to run everynight 
User-added image

If you want to change, you can change the following code in the class. 
public static String CRON_EXP = '0 05 0 * *  ?';

For more details check this link
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_scheduler.htm

I hope this would work as you want. Nothing to change anything on the workflow. Keep the workflow as it is.
  

All Answers

Abdul KhatriAbdul Khatri
  • Create a Field on Case Object by the name Days Open
User-added image
  • Create a workflow on Case Object by the name "Calculate Days Open"
User-added image
  • Create Update Field Workflow Action
User-added image
User-added image
AmulAmul

Use this Formula:
IF( {!IsClosed},null, NOW() - {!CreatedDate} )

This document will help you alot.
http://crmsuccess.blogs.com/files2/100_sample_formulas_v6.pdf

 
Abdul KhatriAbdul Khatri
Hey Amul, Are you suggesting a formula field?

If Yes, don't you think that the value won't persist in that case what Giorgos is asking. I also thought of that but changed my solution to workflow because of that. Please confirm if my undestanding is correct.
Giorgos LosoffGiorgos Losoff
Hi Abdul,

I have put in your solution, but it will take a few days to confirm if it is correct. In the mean time, I will keep my eye on this thread in case you have any other suggestions! Thank you!
Abdul KhatriAbdul Khatri
Thats if fine, you can also confirm my solution in the sandbox with the test data.
Giorgos LosoffGiorgos Losoff
Hey Abdul, it appears that the rule is not working. I have left it in open status for a full day and a half and the counter has not moved up from 0. Any ideas why this might be?
Abdul KhatriAbdul Khatri
Please make sure you have activated the workflow. The button must look like in the screen shot below

User-added image
Giorgos LosoffGiorgos Losoff
It is activated, so I am not sure why it is not working.
Abdul KhatriAbdul Khatri
  1. First you need to update the opportunity to check if it execute.
  2. If you did above and even that didn't work. Please make sure you have selected the option on workflow as shown below
    • created and everytime time it's edited
User-added image

if all of them doesn't work please send me the screen shots of your configuration. Do it in a word doc.
Giorgos LosoffGiorgos Losoff
Okay, so I think I know what the problem is. The Days Open only populates once I change the status back to Open from pending. When we create a case the status is defaulted to open, so the workflow rule only triggers when you select the picklist option. Is there a way around this?
Abdul KhatriAbdul Khatri
Well it should work even upon creation also, that is what those options says right. Did you try with a new one?

created and everytime time it's edited
Abdul KhatriAbdul Khatri
Did you try?
Giorgos LosoffGiorgos Losoff
Hi, so the paramters were still correct the first time. I created a new case again and will wait 24 hours to see if the counter goes from zero to one. Is there anything else that I can do to help you with this?
 
Abdul KhatriAbdul Khatri
OK So let me get my head, You just opened a case and the Days Open field must be saying 0. You wanted when tomorrow you look at the Case it must tell you 1 and it should keep updating as days pass wihtout touching the Opporunity for any updates. Is that what you wanted?
 
Giorgos LosoffGiorgos Losoff
Correct, yes. And right now, when I looked back at a case I opened, after 48 hours it still said 0. But when I changed the case status, it then changed the counter to 2 and then started counting. Does that make sense?
Abdul KhatriAbdul Khatri
Then use the following code
  
global class CaseOpenDaysUpdateJob implements Schedulable {

    public static String jobname = 'Update Case Open Days';
    public static String CRON_EXP = '0 05 0 * *  ?';
       
    public static void Start()
    {
        System.schedule(jobName, CRON_EXP, new CaseOpenDaysUpdateJob());
    }   
    
    
    public static void Stop()
    {
        List<CronJobDetail> jobs = [SELECT Id FROM CronJobDetail WHERE Name = :jobName];
        
        if(!jobs.isEmpty()) {
            for(CronJobDetail job : jobs) {
                Id jobId = [SELECT Id from CronTrigger WHERE CronJobDetailId = :job.Id][0].Id;
                System.abortJob(jobId); 
            }           
        }
    }
    
    global void execute(SchedulableContext SC)
    {
    
        List<Case> caseToUpdate = [SELECT Id FROM Case WHERE Status = 'Open'];
        
        if (caseToUpdate == null) return;
        
        update caseToUpdate;
    
    }
    
}

You need to schedule it. Use the following Command in the anonymous window under Developer Console
  
CaseOpenDaysUpdateJob.start();

To Stop 
CaseOpenDaysUpdateJob.stop();

If you want to test the job 
CaseOpenDaysUpdateJob job = new CaseOpenDaysUpdateJob();
job.execute(null);

Once schedule you can see it by going
User-added image

Right now it is scheduled to run everynight 
User-added image

If you want to change, you can change the following code in the class. 
public static String CRON_EXP = '0 05 0 * *  ?';

For more details check this link
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_scheduler.htm

I hope this would work as you want. Nothing to change anything on the workflow. Keep the workflow as it is.
  
This was selected as the best answer
Giorgos LosoffGiorgos Losoff
So this is not exactly what I was looking for, but it definitely is a much bigger step in the right direction. I am putting in the code and will circle back here if I run into any problems. Thank you so much for your persistence and help!