My client has a Service Level Agreement (SLA) in which they must respond to a certain type of service request within 2 business days. In Salesforce, they were trying to track what % of orders are serviced within the SLA period. To date, they had been calculating the SLA Due Date with a workflow rule. The workflow rule auto-assigned the SLA Due Date with a simple formula: today()+1. This was problematic for two reasons: (1) the formula didn’t factor in weekdays or company holidays, and (2) the process didn’t give consideration to “time of day”.
They understood the first point, but didn’t quite understand the significance of the second point. To illustrate, I described the following scenario: “Let’s consider the ideal case - an order comes in today at precisely 9:00 AM. By your formula, you have all of today and all of tomorrow to finish that order. If it takes you longer than tomorrow, you have exceeded your SLA. Now, let’s suppose an order comes in today at 4:50 PM. By your formula, you have only 10 minutes left from today and all of tomorrow to finish that order – anything after that and you have exceeded your SLA. Also, what happens if an order comes in anytime on a Saturday? By the time you get to work on Monday, you have already exceeded your SLA with the current formula.”
The light bulbs clicked on. We agreed they needed to start tracking the date/time of the order submission, and calculate the SLA Due Date by adding 16 business hours to that Order Submission date/time stamp.
Thought Process -- Getting to the End Solution
A reader recently asked me to share more details about how I get from Problem to Solution, so here we go:
- I started by checking out the Workflow formulas; in general, if I can solve a problem with clicks and formulas, rather than code, I’ll take that option. In this case, I couldn’t come up with a simple solution. There are no functions that allow for determining the “Day of Week” in a date field, nor an easy way to exclude company holidays from the SLA Due Date calculation.
- Whatever can’t be managed in a formula can almost certainly be managed in an Apex trigger. I visited the Salesforce Developer Community message boards, to see if anyone had done something similar. Before I write any code, I always visit the AppExchange and developer boards, looking for a short cut, inspiration or at least potential gotcha’s that other devs have run into. My quick search didn’t bear much fruit. Blanka and Rup3 had creative formulas that could be used in Workflow rules, but neither solved the problem of company holidays or calculating the value based on business hours. I spot checked a few other posts, but none provided they guidance I needed.
- Next, I pondered how non-Salesforce Developers might have tackled this problem. I did a Google search on “calculate business hours” and came up with this interesting post (and code sample) on the Code Project boards. The sample was written by perle1, and was exactly the logic I needed. Well, almost. Whle it would be pretty simple to convert perle1's C++ logic into Apex, his code still hadn’t tackled the problem of holidays.
- RTFM - Read the Foolish Manual. Yes, I probably should have done this sooner, perhaps at step 1, but I wasn’t entirely sure what I was looking for until I’d latched on to Perle1’s code. His sample focused my attention on company holidays. It was then that I recalled Company Profile configurations, which are a standard part of your organization setup in Salesforce.com. There had to be a way for my Apex triggers to use and offset with these configuration settings.
So I poked around the Apex Developers Guide, found the BusinessHours object and some nice sample code … and was pleasantly surprised. This was going to be a piece of cake! In fact, it looked like I’d be able to write this trigger in 4 lines of Apex code (compared with Perle1’s 90 lines of C++ code). Plus, I’d have the ability to handle holidays in the business hours calculation – without writing any extra code.
Here's the step-by-step:
1.) First, I entered in all of the company holidays (click Setup -> Company Profile -> Holidays). A nice Salesforce.com feature is that the holidays can be configured as "recurring", so you can set the holiday once, and then never have to worry about it. Recurring holidays are flexible, in that they can be set up to occur on a specific day of the year (i.e., Christmas is December 25th, every year), or on a date pattern (i.e., Labor Day occurs on the 1st Monday of each September).
2.) Next, I set up the business hours for the work group that was using this "Submitted Order" process. In Salesforce, click Setup -> Company Profile -> Business Hours. There was already a Business Hours record labeled "Default". I left that one there, and created a new Business Hours record labeled "BSC". I added in the service teams business hours, and included the company holidays. I set these hours as the new "default" for the org.
3.) Finally, here's the Apex Code that does all the work:
trigger calcBusinessHours on Submitted_Order__c (before insert, before update) {
// Assumes the BSC work hours are the default for the Org
BusinessHours stdBusinessHours = [select id from businesshours where isDefault = true];
for (Submitted_Order__c so : Trigger.new) {
if ((so.WB_Submitted__c != NULL) && (stdBusinessHours != NULL)) {
// BSC works 11.5 hours / day (8:00 AM - 7:30 PM, M-F). Our SLA is 2-days (23 business hours)
so.SLA_Due_Date__c = BusinessHours.addGmt (stdBusinessHours.id, so.WB_Submitted__c, 23 * 60 * 60 * 1000L);
}
}
}
Submitted_Order__c is the custom object that I want to determine the SLA Due Date from. This custom object has a date/time field labeled "WB_Submitted__c", and another date/time field labeled "SLA_Due_Date__c".
The "WB_Submitted__c" field has a date/time value when it is uploaded into Salesforce (by an external process). This is the date and time that the order was submitted to our support services group.
This function is triggered whenever a Submitted_Order__c record is inserted or updated in Salesforce. The trigger grabs the BSC work hours (which we configured as "default" in step 2), and then adds 23 business hours to that value to calculate the SLA_Due_Date__c field. NOTE: You'll want to change this if you're using 8-hour business days.
What if you needed to calculate the number of business hours between two date/time values, and record that into a number field? There's already a BusinessHours.diff method that does that work for you! NOTE: The answer is returned in milliseconds, so you need to do a little math to convert it to hours:
Hours_to_Close__c = BusinessHours.diff (stdBusinessHours.id, so.WB_Submitted__c, so.OA_Complete__c) / 1000 / 60 / 60;
How simple is that? Easy-peasy, lemon-squeezy!
Would you mind posting your test code? What you provided looks like it would work for me and while I can figure out how to modify the trigger for our needs, I don't know how to write the test cases so that I could put this into Production.
ReplyDeleteMy actual Apex Trigger and test code have quickly grown to be a lot more complex than the sample I've provided above. I know do a lot of cross-object lookups and pollination of data. If I provided both my new trigger and test code, it might confuse the reader.
ReplyDeleteSo I'm going to write this test method from memory. It should allow you to install the code fragment above.
DISCLAIMER: I haven't tested this test code. While it should give 100% test coverage, it's still not a very thorough piece of work. It doesn't cover all possible trigger uses, positive and negative test cases, bulk inserts, etc. All it does is insert a test record, read that same record back, and then verifies that the fields updated by the trigger are no longer null.
It's a hack, and you'll almost certainly want to add more test code around your own triggers! Still, it should give 100% test coverage -- for some folks, that's all that matters!
=============================================
@isTest
private class testOrdersClass {
static testMethod void insertDatedOrder() {
// Insert a Submitted Order w/ non-blank WB_Submitted DT value
System.debug('JPS:Inserting SO with valid WB_Submitted DT value');
Submitted_Order__c soDated = new Submitted_Order__c (Name='1234561', WB_Submitted__c=System.now());
insert soDated;
// Read the record back and verify that the fields modified by this trigger are not null
soDated = [select Id, Name, SLA_Due_Date__c, Hours_to_Close__c,
from Submitted_Order__c
where Name = '1234561'];
System.assert (soDated.Hours_to_Close__c != NULL);
System.assert (soDated.SLA_Due_Date__c != NULL);
}
} // testOrdersClass
=============================================
Hello I am doing a similar task but approached it from a trigger that would retrievea custom BusinesHours object from salesforce and use this in the dif method along with two datetime variables I have and would return the time difference that I use tol add to an existing field that shows how long since the last status change. Below is hte code. I just cant seem to get the diff method working!! please help,
ReplyDeleteif (oldWo.Status__c!=newWO.Status__c && newWO.WO_Last_Status_Changed__c !=null) {
if(newWO.Status__c != 'Pending'){
DS_BusinessHours = [SELECT Id From BusinessHours WHERE Name = 'DS_BusinessHours'];
timeSinceLastStatus = BusinessHours.diff(DS_BusinessHours, newWO.WO_Last_Status_Changed__c, System.now())/3600000.0;
newWO.WO_Time_with_DS__c += timeSinceLastStatus;
}
}
Hi JP,
ReplyDeleteI am not going to ask you a question. just want to tell, I had come across the same problem, tried the things in the same order but I ended up doing everything custom. 3 objects, 1 for Holidays, 1 for locale and 1 for calculation and a very good size of apex (about 400 lines, 18 kb in size.)When I saw your approach, I couldnt help myself from jumping from my seat....
its excellent, superb, marvelous and anything good word you can think of...
Great job Man.. keep it up..
Thanks a ton..:)
nice
ReplyDeleteThe stdBusinessHours != NULL test is bogus, because the query will throw an exception if the stdBusinessHours was not found. Perhaps you really wanted something like:
ReplyDeleteBusinessHours stdBusinessHours = null;
for( BusinessHours bh : [select id from businesshours where isDefault = true limit 1]) {
stdBusinessHours = bh;
}
Note: The limit on the query is usually a good idea. It tells the database call it can stop looking for records as soon as it has found one... It also tells the reader why you did not put a break inside the for loop.
Has anyone had an issue where this happens...
ReplyDeleteError: Compile Error: unexpected token: 'BusinessHours' at line 2 column 0
i noticed on the code that it does not include the holidays.. might want to double check because the holidays does not automatically disable business hours.. but correct me if im wrong as i might miss something here..
ReplyDeleteso what about an international company, how do you handle time zones...? isn't the Holidays running off one org default time zone?
ReplyDeleteWhat about international companies, with time zones... doesn't he holiday feature run from one default org time zone?
ReplyDeleteExtremely useful post!
ReplyDeleteI used your idea to generate a much-wanted IsHoliday() function, based on the standard holiday calendars.
What I did is retrieve the WorkingHours, then, by BusinessHours.addGmt, I add 1 second to the datetime to be checked and see if that matches with a 'normal' addition of 1 second to the datetime to be checked. If both values match, then it is a working day (datetime).
global static Boolean isHoliday(Date checkDate){
ReplyDeleteBusinessHours stdBusinessHours = [select id from Businesshours where isDefault = true];
Date outDate = (BusinessHours.add(stdBusinessHours.id, checkDate, 1)).date();
if(checkDate == outDate){
return false;
} else {
return true;
}
}
Have you ever seen the issue that I posted on the force.com message board? Basically I am getting inconsistent results when using the add function, especially if I try to go backwards (negative interval). It is resulting in a non-working day.
ReplyDeletehttp://boards.developerforce.com/t5/Apex-Code-Development/Inconsistent-Results-from-BusinessHours-Add-Function/td-p/596303