Sophisticated DateTime “Formula Fields” with Apex and Field-Level Security

What do you do when you want to calculate a formula-like field but a regular formula won't work?

Salesforce CRM's formulas handle dates very well. If you want to enter a date value and have formula fields display, for instance, mydate__c + 21 days, that's simple. Just use mydate__c + 21.

Side note: If you try going the long way around and use DATE( YEAR( mydate__c ), MONTH( mydate__c ), DAY( mydate__c ) + 21 ) and mydate__c = 09/17/2010, Salesforce returns #Error! because there's no date 09/38/2010. Similarly, adding three months to a date like 1/31/2010 will also give an error. More about this in a future post.

DateTime fields are like Date fields, but they include... wait for it... a time component (and can be created in the running user's local time zone or in GMT).

Here's a use-case for a DateTime formula field:

A photography studio schedules photo shoots, and different packages include different durations. Similarly, we could use a hair salon which offers different services, each with a different duration, a dentist... you get the idea.

Requirements:

1. Enter a DateTime for an appointment start time (starttime__c)
2. Enter a duration (though in a production system, I'd include a value on the Product2 sObject, we'll just enter a value here) (minutes__c)
3. Display a read-only DateTime field with the end time (endtime__c)
4. The end time must be read-only to all users, like any formula field

Here's what won't work:

• A formula field won't work because there are no MINUTE(), HOUR(), SECOND() formula functions
• Workflow won't work because it depends on formulas to fill new values for date/datetime fields

That leaves Apex. First, the configuration:

1. Create DateTime field starttime__c
2. Create DateTime field endtime__c
3. Set endtime__c field-level security to Read-Only for all profiles
4. Create Number (18,0) field minutes__c
5. Create a trigger on the sobject

Here's the trigger:

trigger timeTrigger on TestObject__c (before insert, before update) {
    for (TestObject__c t : Trigger.New){
    	if(t.StartTime__c != null && t.minutes__c != null){
        datetime myDateT = t.StartTime__c;
        double d = t.minutes__c;
        Integer shootmins = d.intValue();
        if(mydateT != null && shootmins != null)
        	t.EndTime__c = myDateT.addminutes(integer.valueof(shootmins));
       	}
    }
} 

Regular readers will note that I do usually split triggers into a trigger and a class, but I've not done so here purely for the sake of brevity.

Here's the test code:

public without sharing class shootTimesTriggerTest {

    private static testMethod void ShootCalculateEndTime_PositiveTestCases() {
        TestObject__c to;
        TestObject__c l;    
        test.starttest();
        l = new TestObject__c (name = 'test');
        datetime myDateTime = datetime.newInstance(2008, 12, 1, 12, 30, 2);
        l.StartTime__c = myDateTime;
        l.minutes__c = 90;
        insert l;
        to = [SELECT id, EndTime__c FROM TestObject__c WHERE id = :l.id];
        datetime newDateTime = datetime.newInstance(2008, 12, 1, 14, 0, 2);
        system.assertequals(to.EndTime__c, newDateTime);
        l.minutes__c = 45;
        update l;        
        to = [SELECT id, EndTime__c FROM TestObject__c WHERE id = :l.id];
        newDateTime = datetime.newInstance(2008, 12, 1, 13, 15, 2);
        system.assertequals(to.EndTime__c, newDateTime);
        test.stoptest();
    }

    private static testMethod void OppCalculateEndTime_NegativeTestCases() {
        test.starttest();
        TestObject__c l = new TestObject__c (name = 'test');
        l.minutes__c = null;
        insert l;
        system.assertequals(l.EndTime__c, null);
        test.stoptest();
    } 
}

A few points about how this works:

• Triggers run in System mode, so they don't respect field-level security. Thus, we can set a field to read-only for all profiles, and the EndTime__c field will still be updated.
• The test code runs in System mode as well, avoiding any potential problems if the field were set to invisible to a profile and we used System.RunAs() to test for various profiles.
• Although I'm not a fan of using SOQL queries this often, I used these in the interest of saving time. Keep in mind that if you had quite a few queries in your regular code, adding these two might put you over the limit, so use queries sparingly!
• This is the only way I know of to add minutes to a DateTime.

Did I miss anything? Please let me know in the comments.