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!
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.
Email Inbox Version 2
Well, it took about ten minutes from the release of Email Inbox Version 1 for people to request additional features, screenshots, etc. Version 2 includes some neat icons, and this post has the multiple-times-requested screenshots we promised.
First, a teaser screenshot. (There's a better one below the code.)
Here's the updated Visualforce code:
<apex:page controller="EmailMessageController" action="{!ViewData}">
<apex:sectionHeader title="Email Messages" subtitle=""></apex:sectionHeader>
<apex:pageblock id="emailblock">
<apex:facet name="header">
<apex:form >
<apex:panelGrid styleClass="list"
columnClasses="pbTitle,pbButton,pbHelp" columns="3" border="0"
cellpadding="0" cellspacing="0">
<apex:outputLabel ><h3>Messages</h3></apex:outputLabel>
<apex:commandButton value=" Refresh " styleClass="btn"
action="{!ViewData}" rerender="emailblock"></apex:commandButton>
<apex:SelectList value="{!wheretext}" size="1" id="controllerselectlist">
<apex:actionSupport event="onchange" action="{!ViewData}"
reRender="emailblock"></apex:actionSupport>
<apex:selectOptions value="{!views}" />
</apex:SelectList>
</apex:panelGrid>
</apex:form>
</apex:facet>
<apex:form >
<apex:pageblocktable value="{!Messages}" var="e" id="emailtable"
bgcolor="#F3F3EC" styleClass="list" rowClasses="dataRow"
onRowMouseOver="hiOn(this);" onRowMouseOut="hiOff(this);">
<apex:column >
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.Subject.label}{!IF(sortExpression=='Subject',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="Subject" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
<apex:outputLink value="/{!e.Id}" target="_blank">{!e.Subject}</apex:outputLink>
</apex:column>
<apex:column >
<apex:facet name="header">
{!$ObjectType.Contact.fields.Name.label}
</apex:facet>
<apex:outputLink value="/{!e.Parent.ContactId}" target="_blank"
rendered="{!IF(e.Parent.ContactId != '',true,false)}">{!e.FromName}</apex:outputLink>
<apex:outputtext value="{!e.FromName}"
rendered="{!IF(e.Parent.ContactId != '',false,true)}" />
</apex:column>
<apex:column >
<apex:facet name="header">
{!$ObjectType.Account.fields.Name.label}
</apex:facet>
<apex:outputLink value="/{!e.Parent.AccountId}" target="_blank">{!e.Parent.Account.Name}</apex:outputLink>
</apex:column>
<apex:column value="{!e.FromAddress}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.FromAddress.label}{!IF(sortExpression=='FromAddress',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="FromAddress" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column value="{!e.Status}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.Status.label}{!IF(sortExpression=='Status',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="Status" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column value="{!e.MessageDate}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.MessageDate.label}{!IF(sortExpression=='MessageDate',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="MessageDate" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="Inbound/Outbound{!IF(sortExpression=='Incoming',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="Incoming" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
<apex:image url="/img/emailInbound.gif" rendered="{!e.Incoming}" />
<apex:image url="/img/emailOutbound.gif" rendered="{!NOT(e.Incoming)}" />
<!--<apex:outputfield value="{!e.Incoming}" rendered="{!NOT(e.Incoming)}" />-->
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="Attachment{!IF(sortExpression=='HasAttachment',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="HasAttachment" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
<apex:image url="/img/emailHasAttach.gif" rendered="{!e.HasAttachment}"/>
<apex:outputfield value="{!e.HasAttachment}" rendered="{!NOT(e.HasAttachment)}" />
</apex:column>
<apex:column >
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.Case.fields.CaseNumber.label}{!IF(sortExpression=='ParentId',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="ParentId" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
<apex:outputLink value="/{!e.ParentId}">{!e.Parent.CaseNumber}</apex:outputLink>
</apex:column>
<apex:column value="{!e.ToAddress}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.ToAddress.label}{!IF(sortExpression=='ToAddress',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="ToAddress" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
</apex:pageblocktable>
</apex:form>
</apex:pageblock>
</apex:page>
The Apex controller code has not changed from Version 1.
Here's the screenshot, which includes some emails from around the Salesforce ecosystem:
Incoming (and auto-response) Emails
- Salesforce will search ANY Email field to match an incoming email to a Contact. (nice job, Salesforce!) - We know because Simon Fell's Contact.Email is sfell at salesforce.com, and his Contact.Secondary_Email__c is simon at fell.com in the sandbox. Ditto (with her own email addresses) for Irene Brodt.
- Auto-response emails will be included. We turned off auto-response after a few emails came in.
- Though the Email Address field is coded just to show the field value, Salesforce includes the Gmail link. (We assume that's because we activated the integration, but we may be wrong. Has anyone not activated that?)
- We did not (yet) implement the super Email/Web2Case trigger code that Marco Casalaina posted in the Salesforce Support Blog, but if we had, then every email in the list would be associated with a Contact. (Example: Michael Smith of Force2b.net, who will be a Contact from now on!)
Visualforce Email Inbox
Sonny Cloward, SysAd at Rainforest Alliance, approached us about writing a Visualforce page to display all incoming emails for a given Case Queue. This led a few interesting discoveries. Here's how we handled the (donated time) project:
First, the page was built upon the template Sam Arjimandi built at Salesforce
| Property | Description |
|---|---|
| Autonumber | The API creates an autonumber. |
| Create | Value for the field can be specified during create using the API. |
| Defaulted on create | When created, a default value is supplied if no other value is specified. |
| Delete | Value for the field can be deleted using the API. |
| Filter | Can be used as filter criteria in a SOQL query FROM or WHERE clause. |
| idLookup | Can be used to specify a record in an upsert() call. The Id field of each object has this property and some Name fields. There are exceptions, so check for the property in any object you wish to upsert(). |
| Nillable | The field can contain a null value. |
| Query | The field can be queried with SOQL using the API. |
| Replicate | The value of the field can be replicated using the API. |
| Restricted picklist | A picklist that depends on the value of another picklist for the values it displays. |
| Retrieve | Value of the field can be retrieved using the API. |
| Search | Can be searched with SOSL using the API. |
| Update | Can be updated using the API. |
The important one here is "Filter" because (as the documentation states) this allows the field to be used in a WHERE clause. Also, however, (as the documentation does not state) it allows the field to be used in an ORDER BY clause. So all fields on the EmailMessage object that do not allow filtering/ordering had to be presented plainly, without Sam's cool PageBlockTable sorting features. Once this was done, Sonny had some great ideas:
- Show the email subject, but make that a hyperlink to the email message itself
- Link the Case Number (EmailMessage.ParentId) to the Case (EmailMessage.Parent)
- Show the Case Contact (EmailMessage.Parent.Contact.Name), linking to the Contact (EmailMessage.Parent.ContactId)
- Show the Case Account (EmailMessage.Parent.Account.Name), linking to the Account (EmailMessage.Parent.AccountId)
- Provide filters - Incoming only, Unread only, etc.
public with sharing class EmailMessageController {
public String EmailMessage { get; set; }
private List<EmailMessage> messages;
private String sortDirection = 'ASC';
private String sortExp = 'MessageDate';
public String wheretext;
public EmailMessageController(){
wheretext = '';
}
public String sortExpression { get {
return sortExp;
}
set {
//if the column is clicked on then switch between Ascending and Descending modes
if (value == sortExp)
sortDirection = (sortDirection == 'ASC')? 'DESC' : 'ASC';
else
sortDirection = 'ASC';
sortExp = value;
}
}
public void setWhereText(String value) {
whereText = value;
}
public string getWhereText(){
return wheretext;
}
public List<SelectOption> getViews(){
List<SelectOption> options = new List<SelectOption>();
options.add(new SelectOption('WHERE e.id != null','All'));
options.add(new SelectOption('WHERE e.Incoming = true AND e.Status = \'0\' ','Incoming Unread'));
options.add(new SelectOption('WHERE e.Incoming = true AND e.Status = \'1\' ','Incoming Read'));
options.add(new SelectOption('WHERE e.Incoming = true','All Incoming'));
options.add(new SelectOption('WHERE e.Incoming = false','All Outgoing'));
options.add(new SelectOption('WHERE e.ToAddress = \'support@x2od.com\'','Support Queue'));
options.add(new SelectOption('WHERE e.ToAddress = \'support2@x2od.com\'','Support Queue2')); //etc.
return options;
}
public String getSortDirection() {
//if not column is selected
if (sortExpression == null || sortExpression == '')
return 'ASC';
else
return sortDirection;
}
public void setSortDirection(String value) {
sortDirection = value;
}
public List<EmailMessage> getMessages() {
return messages;
}
public PageReference ViewData() {
//build the full sort expression
string sortFullExp = sortExpression + ' ' + sortDirection;
//query the database based on the sort expression
messages = Database.query('Select e.FromAddress, e.Parent.ContactId, e.Parent.Contact.Name, e.Parent.Account.Name, e.ToAddress, e.Parent.CaseNumber, e.Parent.AccountId, e.TextBody, e.SystemModstamp, e.Subject, e.Status, e.ParentId, e.MessageDate, e.LastModifiedDate, e.LastModifiedById, e.IsDeleted, e.Incoming, e.Id, e.HtmlBody, e.Headers, e.HasAttachment, e.FromName, e.CreatedDate, e.CreatedById, e.CcAddress, e.BccAddress, e.ActivityId From EmailMessage e ' + wheretext + ' order by ' + sortFullExp + ' limit 1000');
return null;
}
}
And here's the Visualforce Page:
<apex:page controller="EmailMessageController" action="{!ViewData}">
<apex:sectionHeader title="Email Messages" subtitle=""></apex:sectionHeader>
<apex:pageblock id="emailblock">
<apex:facet name="header">
<apex:form>
<apex:panelGrid styleClass="list"
columnClasses="pbTitle,pbButton,pbHelp" columns="3" border="0"
cellpadding="0" cellspacing="0">
<apex:outputLabel><h3>Messages</h3></apex:outputLabel>
<apex:commandButton value=" Refresh " styleClass="btn"
action="{!ViewData}" rerender="emailblock"></apex:commandButton>
<apex:SelectList value="{!wheretext}" size="1" id="controllerselectlist">
<apex:actionSupport event="onchange" action="{!ViewData}"
reRender="emailblock"></apex:actionSupport>
<apex:selectOptions value="{!views}" />
</apex:SelectList>
</apex:panelGrid>
</apex:form>
</apex:facet>
<apex:form>
<apex:pageblocktable value="{!Messages}" var="e" id="emailtable"
bgcolor="#F3F3EC" styleClass="list" rowClasses="dataRow"
onRowMouseOver="hiOn(this);" onRowMouseOut="hiOff(this);">
<apex:column>
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.Subject.label}{!IF(sortExpression=='Subject',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="Subject" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
<apex:outputLink value="/{!e.Id}" target="_blank">{!e.Subject}</apex:outputLink>
</apex:column>
<apex:column>
<apex:facet name="header">
{!$ObjectType.Contact.fields.Name.label}
</apex:facet>
<apex:outputLink value="/{!e.Parent.ContactId}" target="_blank"
rendered="{!IF(e.Parent.ContactId != '',true,false)}">{!e.FromName}</apex:outputLink>
<apex:outputtext value="{!e.FromName}"
rendered="{!IF(e.Parent.ContactId != '',false,true)}" />
</apex:column>
<apex:column>
<apex:facet name="header">
{!$ObjectType.Account.fields.Name.label}
</apex:facet>
<apex:outputLink value="/{!e.Parent.AccountId}" target="_blank">{!e.Parent.Account.Name}</apex:outputLink>
</apex:column>
<apex:column value="{!e.FromAddress}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.FromAddress.label}{!IF(sortExpression=='FromAddress',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="FromAddress" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column value="{!e.Status}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.Status.label}{!IF(sortExpression=='Status',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="Status" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column value="{!e.MessageDate}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.MessageDate.label}{!IF(sortExpression=='MessageDate',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="MessageDate" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column value="{!e.Incoming}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.Incoming.label}{!IF(sortExpression=='Incoming',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="Incoming" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column value="{!e.HasAttachment}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.HasAttachment.label}{!IF(sortExpression=='HasAttachment',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="HasAttachment" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
<apex:column>
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.Case.fields.CaseNumber.label}{!IF(sortExpression=='ParentId',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="ParentId" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
<apex:outputLink value="/{!e.ParentId}">{!e.Parent.CaseNumber}</apex:outputLink>
</apex:column>
<apex:column value="{!e.ToAddress}">
<apex:facet name="header">
<apex:commandLink action="{!ViewData}"
value="{!$ObjectType.EmailMessage.fields.ToAddress.label}{!IF(sortExpression=='ToAddress',
IF(sortDirection='ASC','▼','▲'),'')}">
<apex:param value="ToAddress" name="column"
assignTo="{!sortExpression}"></apex:param>
</apex:commandLink>
</apex:facet>
</apex:column>
</apex:pageblocktable>
</apex:form>
</apex:pageblock>
</apex:page>
There are some other cool bits:
If there is no Case.Contact, the table will display the FromName, pulled from the email message.
An interesting point: You may notice that EmailStatus is presented in numerical form. For instance, Incoming Unread is 0, Incoming Read is 1, etc. The documentation, however, says, "Read only. The status of the email. For example, “New,” “Unread,” “Replied,” “Sent.”" So we're not sure of the exact mapping. 3 seems to be Sent, so 2 is probably Replied... but we're not sure.
Don't forget: EmailMessage has two lookups (foreign key): Case, and Activity. This Activity is the task created when Salesforce receives the email, and is - according to the documentation - assigned to the Case Owner. We're not sure what happens when the Case is owned by a Queue. Feel free to comment and share your experiences.
That's it! Enjoy.
