+ Start a Discussion
Jaime BlalockJaime Blalock 

Workflow to add status times when the process is not linear and may be revisted multiple times.

Hello! I am looking for advice on if and how we can build some workflows in Salesforce to collect some information we need. We had some in place but they rarely fired as I believe that because of the nature of what we are trying to do, Salesforce was confused. Out of 700 instances, they only fired 120 times so I deleted them all and want to start new. I am open to suggestions and your thoughts as to if we can even accomplish our goals.
Here are the details:
I created a custom object called Legal Support, it is where the sales team must go anytime they need to request our legal teams assistance with contracts, amendments, terms, etc. Once they fill out the deals of the Legal Support Object, there is a custom button that submits the request over to our legal team so that they may work on the request. While each request is in legal, the request will move through a series of statuses I have listed below. The legal team will change the phase in the Legal Support object in Salesforce each time the request goes to a different status in their control.
  • Unassigned
  • Working
  • Waiting
  • Backlog
  • Blocked
  • Done
  • Lost

The statuses are not linear events as they do not have a natural directional progression. For example, a request may go from unassigned to working, then to waiting, then back to working, then into blocked and back to working, etc. This can happen many times and legal makes redline changed to documents for our clients and they go back and forth many times until an agreement is reached. Our legal team wants to track how long the request is in each status. That means the workflows would need to in essence remember every time a request went back to each status (which could be three, four, five times) and add the times together to give us a total time the request was in each status. The goal is to analyze how long each legal status takes for our deals, I have tried several variations of flows but nothing I know of works and I believe it’s because this is not a linear process and due to the fact that the status can be changed back to the same status many times on one request. As of now I simply have Salesforce timestamping when we enter and leave each stage and providing the time in the status. Easy and simple but not telling us what we want as we want the sum of all the times that request went into each status. As it is now, each time it goes back into that status it overwrites the fields and starts counting again.
 
I have exhausted all avenues and I think this is not possible, but wanted to ask those who are much smarter than I and have much more Salesforce experience. I am happy to provide more details if need be. Thank you!
 
Best Answer chosen by Jaime Blalock
Waqar Hussain SFWaqar Hussain SF
Hi Jaime, 

What I have understood is that, you or your legal team wants to track the Status of Legal support i.e. the duration in days of each status.

I personaly thinks that the solution can not be acheived using Workflows. The solution which would work is to develop a trigger on "Legal Support" object, which will calculate the total duration of each status in days. You will need enterprise or higher edition for this solution.

Create custom fields for each status total duration. Then develop a trigger that will fire whenever status is changed, and have that field update increment the value in the appropriate status duration total. 


This solution is untested, but based on my experience, it seems like it would work.

Best, 
Waqar

All Answers

prateek jainprateek jain
Hi
Please let me know I have  got your problem right
Suppose you create  a record in  Legal Support object today( for example 7 November) with status as working then after  2 days(9 November)  you changed the status  to waiting so no of day record was in working stage was 2 day  then you changed stage to backlog  after 1 day (10 November)  so the record was in working stage for 1 day now again you changed the stage to working  after 1 day (11 November) then you changed status to backlog after 5 days so the record was in working status for 5 days in total of 7 days (5+2).
This is what you want to record.
If yes let me know.I will be happy to help you out
Thanks 
 
Waqar Hussain SFWaqar Hussain SF
Hi Jaime, 

What I have understood is that, you or your legal team wants to track the Status of Legal support i.e. the duration in days of each status.

I personaly thinks that the solution can not be acheived using Workflows. The solution which would work is to develop a trigger on "Legal Support" object, which will calculate the total duration of each status in days. You will need enterprise or higher edition for this solution.

Create custom fields for each status total duration. Then develop a trigger that will fire whenever status is changed, and have that field update increment the value in the appropriate status duration total. 


This solution is untested, but based on my experience, it seems like it would work.

Best, 
Waqar
This was selected as the best answer
Jaime BlalockJaime Blalock
Yes, I think you understand what I want. I want to reiterate that a legal request could go back to any status, for example “working” five of six times before it is complete. I need the total amount of time in every status for every time it was in that status on that particular legal support request. [Description: cid:image001.jpg@01CC9A22.531AE9A0] Jaime Blalock Senior National Account Executive General Information Services (GIS) Email: jblalock@geninfo.com | Office: 888.333.5696 ext. 2514 Cell: 803.530.4969 | Fax: 803.932.3957 Take Our Client Satisfaction Survey | Email Security Best Practices Information [cid:image020.jpg@01CEB08D.77F64A70] [cid:image021.jpg@01CEB08D.77F64A70] [cid:image022.jpg@01CEB08D.77F64A70] [cid:image023.jpg@01CEB08D.77F64A70] [cid:image006.png@01D02A5B.B957BD70] » Find out more about our company by clicking here » GIS is an accredited US employment background screening firm
prateek jainprateek jain
Hi
Who got your problem?
thanks

 
Jaime BlalockJaime Blalock
Hello, I am working with Waqar Hussain and will post the solution as soon as we get it worked out. Thank you!
Jaime BlalockJaime Blalock
Wagar Hussain has the best answer! Here's the trigger that worked:


trigger CalculateStageDuration on Legal_Support__c(before insert, before update){
    if(trigger.isInsert){
        for(Legal_Support__c ls : trigger.new){
            ls.Status_Last_updated__c = system.now();
            ls.Time_Backlogged__c = 0;
            ls.Time_Blocked__c = 0;
            ls.Time_Unassigned__c = 0;
            ls.Time_Waiting__c = 0;
            ls.Time_Working__c = 0;
        }
    }
    
    if(trigger.isupdate){
        for(Legal_Support__c ls : trigger.new){
        
            if(ls.Status_Last_updated__c == null)
            ls.Status_Last_updated__c = system.now();
            if(ls.Time_Backlogged__c == null)
            ls.Time_Backlogged__c = 0;
            if(ls.Time_Blocked__c == null)
            ls.Time_Blocked__c = 0;
            if(ls.Time_Unassigned__c == null)
            ls.Time_Unassigned__c = 0;
            if(ls.Time_Waiting__c == null)
            ls.Time_Waiting__c = 0;
            if(ls.Time_Working__c == null)
            ls.Time_Working__c = 0;
            
            Integer duration = date.valueOf(ls.Status_Last_updated__c).daysBetween(system.today());
            system.debug('DURATION:: '+duration );
            
            Long dt1Long = ls.Status_Last_updated__c.getTime();
            Long dt2Long = system.now().getTime();
            Long milliseconds = dt2Long - dt1Long;
            Long seconds = milliseconds / 1000;
            Long minutes = seconds / 60;
            Long hours = minutes / 60;
            Long days = hours / 24;
            
            system.debug('minutes:: '+minutes);
            system.debug('hours:: '+hours);
            system.debug('days:: '+days);
            
            if(trigger.oldmap.get(ls.Id).Status__c == 'Unassigned' && trigger.oldmap.get(ls.Id).Status__c != ls.Status__c){
                ls.Time_Unassigned__c += hours ;
                ls.Status_Last_updated__c = system.now();
            }
            else if(trigger.oldmap.get(ls.Id).Status__c == 'Working' && trigger.oldmap.get(ls.Id).Status__c != ls.Status__c){
                ls.Time_Working__c += hours ;
                ls.Status_Last_updated__c = system.now();
            }
            else if(trigger.oldmap.get(ls.Id).Status__c == 'Waiting' && trigger.oldmap.get(ls.Id).Status__c != ls.Status__c){
                ls.Time_Waiting__c += hours ;
                ls.Status_Last_updated__c = system.now();
            }
            else if(trigger.oldmap.get(ls.Id).Status__c == 'Backlog' && trigger.oldmap.get(ls.Id).Status__c != ls.Status__c){
                ls.Time_Backlogged__c += hours ;
                ls.Status_Last_updated__c = system.now();
            }
            else if(trigger.oldmap.get(ls.Id).Status__c == 'Blocked' && trigger.oldmap.get(ls.Id).Status__c != ls.Status__c){
                ls.Time_Blocked__c += hours ;
                ls.Status_Last_updated__c = system.now();
            }
        }
    }
}