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.
New Developer Library Released
Today, Developer Force (http://developer.force.com) released its new library. Here are a few of them. All can be found at http://wiki.developerforce.com/index.php/Documentation.
Workbook
http://www.salesforce.com/us/developer/docs/workbook/index.htm
Fundamentals
http://www.salesforce.com/us/developer/docs/fundamentals/index.htm
Cookbook
http://www.salesforce.com/us/developer/docs/cookbook/index.htm
Apex Advanced Code Example
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_shopping_cart_example.htm
https://sites.secure.force.com/appexchange/listingDetail?listingId=a0N30000001saDCEAY
And many more to come!
Trigger to help Salesforce for Twitter
Salesforce for Twitter is one of the best AppExchange packages I've seen. It fulfills the promise salesforce.com made to bring the Service Cloud to all orgs of all sizes. And it works well.
Though a supplemental/unofficial guide to customizing SFDC for Twitter will be released soon on this site, I wanted to share a trigger I just wrote to add new Leads to a campaign:
Firstly, thank you to Scott Hemmeter at Arrowpointe, who wrote the original code that I customized.
Secondly, you could easily duplicate this trigger and set it to run on the Contact object as well.
Please don't set the trigger to "after update," as in testing, it ran into problems when converting a Lead and merging with a Contact already on the "Twitter" campaign.
trigger AddToTwitterCampaign on Lead (after insert) {
// List containing each Lead being processed
list<Lead> theLeads = new list<Lead>();
//We only execute if we have a campaign named "Twitter"
if([SELECT Count() FROM Campaign WHERE name = 'Twitter'] == 1){
Campaign TC = [SELECT id, name FROM Campaign WHERE name = 'Twitter' LIMIT 1];
for(Lead l:trigger.new) {
if (l.leadsource.indexOf('Twitter',0 ) >= 0 || l.leadsource.indexOf('Tweet',0 ) >= 0 ){
theLeads.add(l); // add lead to the main lead list
}
}
// List containing Campaign Member records to be inserted
list <CampaignMember> theCampaignMembers = new list<CampaignMember>();
for (Lead ld:theLeads) {
CampaignMember cml = new CampaignMember();
cml.leadid = ld.id;
cml.campaignid = TC.id;
theCampaignMembers.add(cml);
}
//Insert the list of Campaign Members
if(!theCampaignMembers.isEmpty()){
insert theCampaignMembers;
}
}
}
The trigger requires that you have a Campaign called "Twitter," but feel free to change that to anything else you'd like.
Don't worry if you have other triggers that add Leads to Campaigns - this can work alongside them, so you can add Leads to as many Campaigns as you'd like.
A Mention in the Developers Challenge
The salesforce.com Developer Force Challenge has concluded, and the team of Force Squared and The Enforcer won a mention!
Our Daily Shinro site was listed “for sheer exuberance!”
I’m really proud of the site, though the lion’s share of the kudos go to John for the concept and site design. I just coded whatever he told me to code; he’s the creative one!
So if anyone is looking for a custom Force.com Site or website integration to Salesforce, contact us and let’s discuss your needs!
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.
