• Welcome
  • About
    • Leadership
    • Blogroll
  • Force-Squared Blog
  • Support
    • Knowledge Base
    • Log a Case
  • Contact
  • Welcome
  • About
    • Leadership
    • Blogroll
  • Force-Squared Blog
  • Support
    • Knowledge Base
    • Log a Case
  • Contact
  • Tips and Tricks
  • Configuration
  • Development
You are here: Home / Salesforce CRM / Development / Apex / Visualforce Email Inbox

Visualforce Email Inbox

July 14, 2009 by David Schach 4 Comments

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. Instead of the Account object, we substituted the EmailMessage object. But something didn't work.

Fields have certain attributes:
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:

  1. Show the email subject, but make that a hyperlink to the email message itself
  2. Link the Case Number (EmailMessage.ParentId) to the Case (EmailMessage.Parent)
  3. Show the Case Contact (EmailMessage.Parent.Contact.Name), linking to the Contact (EmailMessage.Parent.ContactId)
  4. Show the Case Account (EmailMessage.Parent.Account.Name), linking to the Account (EmailMessage.Parent.AccountId)
  5. Provide filters - Incoming only, Unread only, etc.
You'll see in the Apex Code where we added parent object fields to the SOQL query, and where we used a List to populate the query's filter.
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.

Related

Filed Under: Apex, Development, salesforce.com, Visualforce, X-Squared On Demand Tagged With: Configuration, Projects, Visualforce

← Eclipse as a Password Manager Email Inbox Version 2 →

Filed Under: Apex, Development, salesforce.com, Visualforce, X-Squared On Demand Tagged With: Configuration, Projects, Visualforce

Comments

  1. Jon Mountjoy says

    July 14, 2009 at 12:06

    Can you include a screenshot of the app? How about a link to it running on Sites on a Developer Edition? :-)

    Reply
  2. Kyle Roche says

    July 15, 2009 at 08:45

    Nice job David. I’d be interested in the screenshot / link as well. Good stuff.

    Reply

Trackbacks

  1. Email Inbox Version 2 : X-Squared On-Demand says:
    July 15, 2009 at 19:41

    […] The Apex controller code has not changed from Version 1. […]

    Reply
  2. Email Inbox Version 2 : X-Squared On-Demand says:
    July 15, 2009 at 19:41

    […] The Apex controller code has not changed from Version 1. […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Is it Dreamforce Yet?

Find out!

Find us on the AppExchange

Mass Update Contacts Pro

Get It Now



Mass Update Contact Address

Get It Now


Recent Posts

  • Lightning Component With Running User Information
  • ChatterBINGO is Now Open-Source
  • Display Only My Role’s Records on a Report
  • Chatter Publisher Actions (Part 2): Object Custom Action
  • Chatter Publisher Actions (Part 1): Create a Record

Post Categories

Salesforce Blogs

  • MK Partners
  • Arrowpointe
  • Technology, Coding and Bears… OH MY!
  • Embracing the Cloud
  • TehNerd
  • Decoding Salesforce
  • Salesforce Source
  • Vertical Coder
  • The Silver Lining
  • Force Monkey

Popular Tags

#df09 #df10 Akismet Apex AppBuilder AppExchange Appirio Astadia Blogs Chatter Configuration Content DreamFactory Dreamforce Eclipse IDE Enterprise Force.com Builder Force.com Platform Google Infowelders Integration Just for fun Lightning New Developments New Features Partner Program PersonAccount Projects Publisher Salesforce.com Salesforce1 Salesforce for Google Apps sfdcverse Sites Visualforce Web-to X-Squared On Demand

Like Us on Facebook

Like Us on Facebook

Share This Page

Find Us Online

  • Twitter
  • Facebook
  • LinkedIn
  • Google Plus
  • X-Squared G+ Page
  • RSS

Subscribe

RSS Feed Comments

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 5,205 other subscribers

Copyright © 2008–2018 X-Squared On Demand · Genesis Framework by StudioPress · WordPress · Log in