Facebook
Salesforce
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:
- Enter a DateTime for an appointment start time (
starttime__c) - Enter a duration (though in a production system, I'd include a value on the
Product2sObject, we'll just enter a value here) (minutes__c) - Display a read-only DateTime field with the end time (
endtime__c) - 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:
- Create DateTime field
starttime__c - Create DateTime field
endtime__c - Set
endtime__cfield-level security to Read-Only for all profiles - Create Number (18,0) field
minutes__c - 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.
Overload Apex Class to be Controller AND Extension
Wow - today brought an interesting discovery. Here's the situation:
Coding the new premium version of Mass Update Contacts (details to come), I replaced the two parts of the page with Apex Components. This will allow the app to support custom address fields and international address formats.
I didn't want to write one ControllerExtension for the main page, a CustomController for the view section component, and another CustomController for the pageblocktable component. So here is the overloaded class constructor. Note that this works because an extension passes the StandardController to the constructor, and a CustomController passes nothing:
public with sharing class VersatileClass {
private Account account;
public VersatileClass(){
system.debug('OPERATING AS CONTROLLER');
if(System.currentPageReference().getParameters().get('id')==null){
//Include error checking here
} else{
string AId = System.currentPageReference().getParameters().get('id');
account = [select id, name from Account where id = :AId];
//And whatever else you want to do
}
}
public VersatileClass(ApexPages.StandardController controller) {
system.debug('OPERATING AS EXTENSION');
if(System.currentPageReference().getParameters().get('id')==null){
//Include error checking here
} else{
this.account = (Account)controller.getRecord();
//And whatever else you want to do
}
}
}
Enjoy! This should save people a lot of time.
Eclipse as a Password Manager
Caution: Only use this tip if your computer is SECURE.
Recently, Judi Sohn wrote about a URL hack to save your Salesforce username and password (in an exposed, plaintext manner). This is a great way to save a lot of time logging into Salesforce.
There is another way to save un/pw combinations: Use Eclipse to store the un/pw/securitytoken. There's a catch (there always is): Once they're entered, you can't see them in plaintext for, say, doing a copy/paste of your token into Connect for Outlook, Excel Connector, etc.
For each client, I create a project in the Eclipse Force.com IDE, entering the username, password, and security token into the appropriate form. (This does require me to have a System Administrator, or at least "View All Data" and possibly "Edit All Data" profile permissions.) Then, instead of getting into Salesforce through the browser, I open a file in the project, such as an Apex Class, right-click, and select "Show in Salesforce Web." The browser does the rest, opening the org, and taking me to that Apex Class. It does not take me to the Home screen, but I'm willing to use that one extra click. Plus, it's nice to get directly into the Setup area sometimes.
That's it! Keep all your projects in Eclipse, and (as long as you're a developer and work in Eclipse a lot) it will save time.
To reiterate the above caution: Anyone who can open Eclipse can get into any of those orgs, so be careful with this! Always secure your computer. On Vista, require a password when waking from the screen saver, and use other security features whenever possible.
Pulling Code Out of Triggers
To date, triggers have only been accessible on each object's setup page, leading to a lot of hunting for code within the Salesforce CRM application. Even in Eclipse, switching between the Class and Trigger folders for a given project can be a pain.
These pains can partially be alleviated by keeping all Apex code in one place--as Apex Classes. With the addition of a consolidated trigger list in Summer09, some may feel that this post is superfluous, but consolidating code in one place, combined with the trigger list, will lead to a better development, debugging, and org administration experience.
First, here's a sample trigger (written by Jeff Douglas):
trigger AddOwnerColor on Account (before insert, before update) {
// create a set of all the unique ownerIds
Set<Id> ownerIds = new Set<Id>();
for (Account a : Trigger.new)
ownerIds.add(a.OwnerId);
// query for all the User records for the unique userIds in the records
// create a map for a lookup / hash table for the user info
Map<Id, User> owners = new Map<Id, User>([Select Favorite_Color__c from User Where Id in: ownerIds]);
// iterate over the list of records being processed in the trigger and
// set the color before being inserted or updated
for (Account a : Trigger.new)
a.Owner_Favorite_Color__c = owners.get(a.OwnerId).Favorite_Color__c;
}
Let's pull the code from the trigger into an Apex Class and leave a reference to that class & method in the trigger. We need to pass the list Trigger.new as a parameter to the new class's method:
The trigger:
trigger AddOwnerColor on Account (before insert, before update) {
AccountTriggers.AddOwnerColor(Trigger.new);
}
And the class:
public class AccountTriggers {
public static void AddOwnerColor(Account[] accts) {
// create a set of all the unique ownerIds
Set<Id> ownerIds = new Set<Id>();
for (Account a : accts)
ownerIds.add(a.OwnerId);
// query for all the User records for the unique userIds in the records
// create a map for a lookup / hash table for the user info
Map<Id, User> owners = new Map<Id, User>([Select Favorite_Color__c from User Where Id in: ownerIds]);
// iterate over the list of records being processed in the trigger and
// set the color before being inserted or updated
for (Account a : accts)
a.Owner_Favorite_Color__c = owners.get(a.OwnerId).Favorite_Color__c;
} // close AddOwnerColor
}
While this may seem trivial, it has a few advantages:
- Easier to work in Eclipse (all code in the Classes section)
- Easier to write test code (can see tests and their associated methods in one place)
- Can promote code reuse by allowing other classes and triggers to call the same method.
- For those who like to include test code in the same class as the regular Class, this allows them to do so.
There's a catch (there always is):
You should comment into your Class which trigger is calling the class because otherwise, it is almost impossible to see at a glance where the code flows. This will especially help when writing and debugging tests.
Just a matter of personal style: It may be a good idea to write an Apex Class for each object's triggers (such as class AccountTriggers above). Code reuse is still possible, but it can track where triggers were originally used.
Happy coding!
Developer Preview Releases for the Google Visualization API
Dreamforce 2008 brought many exciting new features from salesforce.com, both on the CRM/front-end side and the Force.com platform/back-end side. One of the favorites was the announcement that salesforce.com had released a Google Visualization code share project. Google has invited developers to participate in Developer Preview Releases for the Visualization API.
It looks like a pretty cool way to work on RESTful integrations while making some pretty pictures for your users.
For those unfamiliar with the Salesforce/Google Visualization toolkit, developer.force.com has a great introduction.
