Dreamforce is Coming!

I keep telling Paul Overy, my colleague and co-founder of Force Geeks, to start a blog. I think he'd be great at it. Here he is as a guest monkey, err, blogger. This is a reprint of a mailing Paul sent to the Salesforce.com New Hampshire User Group.

Dreamforce'09 is only 1 week away. Are you going?

Here are some things that you might find interesting:

- You can log in to the attendee portal to add sessions to your calendar. You can even have your updated calender emailed to you.

- A Dreamforce 2009 Tweet-up is scheduled for Tuesday night at 6pm. Originally planned by David Schach (@dschach), but Salesforce has agreed to host this event and give out free stuff. Hope to see you there!

- Another Tweet-up hosted by Model Metrics is scheduled for 8pm on Tuesday at Johnny Foley's Irish House. Stop by for a pint!

- Steve Anderson (@gokubi) wants you to join him next Wednesday in creating a collaborative document of the Dreamforce opening keynote using Google Wave. If you need an invitation to Google Wave, let me know and I'll send one.

- If you know of any parties, you can list them on the Dreamforce Party Planner, or you can just check out the spreadsheet to see what else is planned.

- If you still need a reason to get excited about Dreamforce, Salesforce is posting a count down of 8 reasons to get excited about Dreamforce.

That's all for now. Feel free to forward on links of your own.

Till next time,
Paul Overy
NHUG Co-Leader

Calculating Business Hours

The Problem

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:

1. 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.
   


2. 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.
   


3. 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.
   


4. 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!