Pages

Tuesday, October 28, 2008

How to Easily Bring Graphical Charts into your CRM?

There is no doubt that nothing communicates better and faster than graphics with users. It has been always the case that users fail to utilize software solutions merely because the information is not delivered to them intuitively, comprehensively and graphically.

Managers often require summarized business critical information quick and easy to grasp. Utilizing charts in Web Applications has been a challenge for many of us over the past two decade and now it is all much easier than ever!

Google Chart is an excellent tool available for free! It is actually very easy to work with and practical.

In this article I will demonstrate how you can utilize this outstanding component to bring new life to your Salesforce pages!

Example: I have a requirement to create a Visualforce page to view a pie chart that shows number of Accounts in our organization divided by their Types.


Here is how it works:
The Google Chart API requires me to pass the chart information via URL parameters and then in return Google will send me an image in PNG format. Then all I need to do is to view the image using the HTML img tag!

Ingredients:
Chart Server URL: http://chart.apis.google.com/chart
Parameters List:
  • chs: is in fact the chart size in pixels ex: 300x200
  • chd: you will need to pass the chart data using this parameter. ex: t:60,40
  • cht: chart type, ex: p3
  • chl: chart items' label, ex: Data 1|Data 2
The above are all basic required parameters in order to create charts. So there is more to it if you are interested.

So in order to utilize this component I create a Visualforce page as follows:



<apex:page controller="googleChartCon" tabStyle="Account">
<apex:sectionHeader title="Accounts by Type"></apex:sectionHeader>
<apex:image url="{!chartData}"></apex:image>
</apex:page>


This page only contains an apex:image component which talks to the controller to obtain it's image URL.

The URL is where the magic happens so we will need to focus on that.

In order to do this I first need to get a list of Account's "Type" picklist values and then
in my custom controller, I create an internal Apex class to save the chart's data while I am running through Account's records and find out how many of which type we have in our database.




public class googleChartCon {
private String chartData;

public String getChartData()inter
{
return chartData;
}

public googleChartCon()
{
//obtain a list of picklist values
Schema.DescribeFieldResult F = Account.Type.getDescribe();
List<Schema.PicklistEntry> P = F.getPicklistValues();
//where chart data should be stored.
List<ChartDataItem> items = new List<ChartDataItem>();

//iterate through each picklist value and get number of accounts
// I wish we could do GROUP BY in SOQL!
for(Schema.PicklistEntry pValue : P)
{
integer Count = [select count() from Account where Type = :pValue.getValue() limit 10000];
if (Count > 0)
items.add(new ChartDataItem(pValue.getValue()+ '-['+ Count.format() + ']' , Count.format()));
}

//Prepare the chart URL
String chartPath = 'http://chart.apis.google.com/chart?chs=600x200&cht=p3';
chartData = chartPath + getChartData(items);
}

private String getChartData(List<ChartDataItem> items)
{
String chd = ''; //23,34,56
String chl = ''; //Hello|World

for(ChartDataItem citem : items)
{
chd += citem.ItemValue + ',';
chl += citem.Label + '|';
}
//remove the last comma or pipe
chd = chd.substring(0, chd.length() -1);
chl = chl.substring(0, chl.length() -1);

String result = '&chd=t:' + chd + '&chl=' + chl;
return result;
}

public class ChartDataItem
{
public String ItemValue
{
get;
set;
}

public String Label
{
get;
set;
}

public ChartDataItem(String Label, String Value)
{
this.Label = Label;
this.ItemValue = Value;
}


}

}


For some security might be a concern sending the data via URL parameters.
Up to this point, I have not been able to confirm if Google is officially supporting SSL connections to rectify this problem. However the following seems to be fine:

https://www.google.com/chart?cht=p3&chd=t:60,40&chs=250x100&chl=Hello|World


In this article I have barely scratched the surface of Google Charts API, if you are interested to learn more about it go to http://code.google.com/apis/chart/

Thursday, October 23, 2008

Working With Multiselect Picklists

Working with Picklists essentially is rewarded since it limits the options of the user and standards what is valid as a value for a field in the system. As good as they are however they bring their own complexity with them.

This is especially true when you are working with multi-select Picklists. Mostly Visualforce’s inputField is used to view multi-select Picklists as Salesforce team is working on it to be more effective and error-free.

In this article we will see how to use the multi-select Picklists.

First off, let’s see how we can load values into our Picklist and view it on our Visualforce page.
Let’s say I have a custom multi-select Picklist field on my User object to define sales regions my users operate on.

The picklist has values such as:
  • North America
  • Latin America
  • Europe
  • Asia
  • Middle East
  • Africa
and so on

In my controller class I add the following property:




public class multiselectPicklist {
private User salesRegions;

public multiselectPicklist()
{
salesRegions = new User();
}
public User getSalesRegions() {
return salesRegions;
}
public void setSalesRegions(User Value) {
salesRegions = Value;
}
}




As you can see it is enough to create a new User object and later on use the picklist field to show the values on your page. Unfortunately there is no other way through which you would more control over what values are shown on your multi-select control. May be in future Salesforce will supply a Component for multi-select operations.

Please note that I have created a setter property as well that receives a User object. This setter allows us to later on read the selected values by user.

Now that we have our controller ready we can program our page to view our multi-select picklist.
in this example what I am trying to achieve is that to allow the user choose as many regions as he/she wants and them filter the Users of the system and view only those who are assigned to those regions.




<apex:page controller="multiselectPicklist" tabStyle="User" sideBar="false">
<apex:form >
<apex:sectionHeader title="Users by Region Report"></apex:sectionHeader>

<apex:pageBlock id="pageBlock" title="Search Filter">
<apex:pageMessages ></apex:pageMessages>
<apex:pageBlockSection title="Filters" columns="2" collapsible="true">
<apex:inputField id="salesRegions" value="{!salesRegions.Sales_Regions__c}" ></apex:inputField>
</apex:pageBlockSection>
</apex:pageBlock>
<apex:pageBlock id="searchResults" title="Results">
<apex:pageBlockButtons >
<apex:commandButton value="Run Report" action="{!runReport}" status="status" rerender="searchResults"></apex:commandButton>
</apex:pageBlockButtons>
<apex:pageMessages ></apex:pageMessages>
<apex:pageBlockTable value="{!users}" var="u" rendered="{!NOT(ISNULL(users))}" rules="cols">
<apex:column value="{!u.UserName}"></apex:column>
<apex:column value="{!u.IsActive}"/>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>
</apex:Page>




Something that you should bear in mind is that even though multi-select option suggests that you should be dealing with a list of Strings as a result of user’s interaction with your control on the page all you will receive will just a semicolon separated string. It is your job to separate the values and use then in whatever endeavor you intend to have.



Below on my button action I have demonstrated how you can read the selected values back and use them in your query to get a list of users in those sales regions:





public PageReference runReport() {

if (salesRegions.Sales_Regions__c == null || salesRegions.Sales_Regions__c == '') {
apexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO, 'Please select one or more regions first.'));
return null;
}
// read the values into an array
string[] regions = salesRegions.Sales_Regions__c.split(';',0);
if (regions != null && regions.size() > 0)
{
//query the database based on the user's selection
users = [select Username, IsActive, Id from user where Sales_Regions__c in :regions and IsActive = true order by Username];
}

return null;
}

private List<User> users;

public List<User> getUsers()
{
return users;
}
}





Something funny that occurred to me was that I tried to hook the Multi-select Component (inputField) with actionSupport to capture onChange event and load values of another Picklist item on my project which was totally a disappointment! It seems that the Salesforce team has been able to get that one to work yet.

Wednesday, October 22, 2008

How to Create Custom Related-List on Visualforce Pages

In the past few weeks in many cases I came across the requirement to add a custom list to the Object's detail page.

The list should be just like related-lists such as "Open Activities" or "Google Docs, Notes, & Attachments", however it is suppose to view a list of data which is somehow relevant to the object but not necessarily from an object directly related to the current object's page.

Let's consider this example, imagine we need to view a list of opportunities related to a Contact through it's Account relationship (not the ones that are directly linked to the Contact).



This is to say that if Contact A is linked to Account X and Account X has 5 opportunities linked to it, by viewing Contact A's details we would be able to view those opportunities as well.

Ok, now action, in order to do this we will need to create a new Visualforce Page and Controller extension.

So I add this text in front of http://yourOrg.salesforce.com/apex/SampleDetailPage and create the new page instantly (This is only possible when you profile is set to work in developer mode).


I set the page's Controller attribute to "Contact" and call my extension "SampleDetailPageCon".
On the page all I need to do is to show the existing Contact details based on whatever active layout target user has chosen and then add my related-list to the bottom of the page. The page code goes like this:


<apex:page standardController="Contact" extensions="sampleDetailPageCon">
<style>
.fewerMore { display: none;}
</style>
<apex:form >
<apex:pageMessages />
<apex:detail relatedList="true"></apex:detail>
<apex:pageblock id="CustomList" title="Related Opportunities" >
<apex:pageBlockTable value="{!oppz}" var="o" rendered="{!NOT(ISNULL(oppz))}">
<apex:column value="{!o.Name}"/>
<apex:column value="{!o.Account.Name}"/>
<apex:column value="{!o.Type}"/>
<apex:column value="{!o.Amount}"></apex:column>
<apex:column value="{!o.CloseDate}"/>
</apex:pageBlockTable>
<apex:outputLabel value="No records to display" rendered="{!(ISNULL(oppz))}" styleClass="noRowsHeader"></apex:outputLabel>
</apex:pageblock>
</apex:form>
</apex:page>



A few tips, normally at the end of detail page Salesforce publishes a component for viewing a few more or a few less records to adjust the size of the page.

Since our related list will be rendered under this component it might not be a bad idea to get rid of it. If you pay attention to be page's code you will see that a style tag has been added to the page that will just do that.

Apart from this everything else is very straight forward. Now all we need to do is to extend the Contact's standard controller and make it able to generate a list of related Opportunities as well.

Here is the Controller's Code:



public class sampleDetailPageCon {
private List<Opportunity> oppz;
private Contact cntact;
public sampleDetailPageCon(ApexPages.StandardController controller) {
this.cntact= (Contact)controller.getRecord();
}
public List<Opportunity> getOppz()
{
Contact con = [Select id, Account.id FROM Contact where id = :cntact.id];
if (con.Account == null)
return null;
oppz = [Select id, Name, Account.Name, CloseDate, Amount, Type from Opportunity where Account.id = :con.Account.id];
return oppz;
}
}




As you can see this also very simple. In the above code, I have just added a method called "getOppz()" in which I first try to see if the Contact is linked to any accounts and if yes then use the Account id to get a list of its Opportunities.



That's it! Now if you need your page to be used by Salesforce as default detail page all you need to do is:
  • Go to: Setup -> Customize -> Contacts -> Buttons and Links
  • Click on "override" next to "View" label
  • Select "Visualforce Page" and then your page from the drop down list

Now there is only one thing left, as you all have noticed every related-list has a hover feature on the top of the page where user can quickly view all the related-lists without scrolling down the whole page.



In my next article I will show you how you can go through the backdoor to add your related-list on the top of the page!


Friday, October 10, 2008

Bringing Google Calendar into Salesforce CRM pages


Since the very first time I saw Google Calendar' API, I wondered if it can be used for any bizarre or genius applications (having a sense of a-tool-for-public) or not. For example I really liked to be able to load Google Calendar into my page and then using the API add as many events as I desire on it from my own database without any authentication requirement and just for the sake of showing the data to user, but I have not been able to get it work in that order so far.

Yet there are a lot of other benefits that Google Calendar can bring along, in terms of sharing events, publishing, flexibility and integration with other portals/applications in the cloud.

One of things you might find useful is the capability of sharing a Google public calendar with your Salesforce CRM users.

At first I tried to publish my own personal calendar into Salesforce, and I was successful to some extent. I could see the calendar is being shown properly and I can switch between the weekly, monthly, etc views. All nice, but I noticed that the Calendar is empty and my events are missing.

In Google Calendar's documentation is not mentioned that you should use a public calendar to be able to share it in other websites or maybe that goes without saying! So I initially thought that any Calendar could be shared and as long as my event is set as public people should be able to see them.

It was more like one of those scenarios where you show a button on your page to a user but say "Do not click on it, ok my boy?"

Anyway, in order to publish the calendar on your Salesforce CRM, you should first think about where you want to place it and you may require to create a new Visualforce page before copying the calendar HTML code into it.

In order to get the calendar's HTML code go to your Google Calendar page and either create a new Calendar to publish it as public calendar or if you already have one then click on settings (on the left side of the screen).

Once you have your public calendar ready go to its "details" page and look for a spot that says "Embed This Calendar", copy the code and paste it into your Visualforce page. That's all!



<apex:page tabStyle="Campaign">
<apex:form >
</apex:form>
<iframe src="http://www.google.com/calendar/embed?src=6lectl1l76jrmul8st527rbq6c%40group.calendar.google.com&ctz=America/New_York&color=%23336699;" style="border: 0" width="800" height="600" frameborder="0" scrolling="no"></iframe>
</apex:page>



And you will see something like below in your page:

Thursday, October 9, 2008

Utilizing Dynamic Apex Feature in Winter 09 Release!


Well for some of us yet the developer edition is not supporting the all glorious Salesforce CRM winter 09 features such as Dynamic Apex.

However, those who have been able to get their hands on it already know that this release has opened up a lot of new possibilities for developing Visualforce pages. Even though here and there I still stumble over some glitches or nice-to-haves, I have decided to devote some of my time each day to learning the new stuff and incorporate them into my apps.

In this article we will see how we can build Dynamic SOQL queries, execute them, get exactly the records we want and moreover we will see how we can now easier than ever hook our selectLists to object's picklists.

For this example, consider filtering Account records based on Name, Type, Ownership and also we want the user to choose how many records they want to get back on the screen.

Points:
I have used a PanelGrid to put my filter Components in and PageBlockTable to view the results.
The page is pretty standrad, what Winter 09 features are about in this article are all in the Controller's Code.




<apex:page controller="myPageController" tabStyle="Account">
<apex:form >
<apex:sectionHeader title="Customer Search" />
<apex:PanelGrid columns="2" id="panel" cellpadding="2">

<apex:outputLabel value="Account Name: "></apex:outputLabel>
<apex:inputText id="textbox" value="{!searchText}" size="40" style="height:13px;font-size:11px;"></apex:inputText>

<apex:outputLabel value="Type: "></apex:outputLabel>
<apex:selectList size="1" id="accountType" value="{!accountType}">
<apex:selectOptions value="{!accountTypes}"></apex:selectOptions>
</apex:selectList>

<apex:outputLabel value="Ownership:"></apex:outputLabel>
<apex:selectList size="1" id="accountOwnership" value="{!accountOwnership}">
<apex:selectOptions value="{!accountOwnerships}"></apex:selectOptions>
</apex:selectList>

<apex:outputLabel value="Result:"></apex:outputLabel>
<apex:selectList size="1" id="resultNo" value="{!resultNo}">
<apex:selectOption itemValue="50" itemLabel="50"></apex:selectOption>
<apex:selectOption itemValue="100" itemLabel="100"></apex:selectOption>
<apex:selectOption itemValue="150" itemLabel="150"></apex:selectOption>
</apex:selectList>

<div></div>
<apex:commandButton action="{!search}" value="Search" rerender="pageblock"></apex:commandButton>
</apex:PanelGrid>



</apex:form>
<br/>
<apex:pageblock id="pageblock" >
<apex:messages></apex:messages>
<apex:pageBlockSection title="Results" id="results" columns="1" collapsible="true">
<apex:pageBlockTable value="{!accountResults}" var="a" rendered="{!NOT(ISNULL(accountResults))}">
<apex:column value="{!a.Name}"></apex:column>
<apex:column value="{!a.Type}"></apex:column>
<apex:column value="{!a.Ownership}"></apex:column>
<apex:column value="{!a.Industry}"></apex:column>
</apex:pageBlockTable>
</apex:pageBlockSection>
</apex:pageblock>
</apex:page>

In this Controller I have developed a procedure "getObjPicklistValues" which receives a DescribeFieldResult object as parameter, corresponding to the picklist I want to show in the filter components' area. DescribeFieldResult methods provide you with most reflection capabilities you require to be able to programmatically write reusable codes (as you can see in this example).

The other benefit that previously was not an option in Visualforce is Dynamic Apex. Now you can build your query based on user's choices on the fly, then run it against Salesforce database and view the customized data to the user.

In this example, if the user is not entering any value for Account Name or has not chosen any Account type, then simply those fields will not be queried and will be excluded from the SOQL statement.

And the controller's code:


public class myPageController {

private List accountResults;

public List getAccountResults() {

return accountResults;

}

public String searchText

{

get;

set;

}

public String accountType

{

get;

set;

}

public String accountOwnership

{

get;

set;

}

public String resultNo

{

get;

set;

}

public List getAccountTypes()

{

return getObjPicklistValues(Account.Type.getDescribe());

}

public List getAccountOwnerships()

{

return getObjPicklistValues(Account.Ownership.getDescribe());

}

private List getObjPicklistValues(Schema.DescribeFieldResult picklistDesc)

{

List pList = picklistDesc.getPicklistValues();

List items = new List();

items.add(new SelectOption('','--None--'));

for (Schema.PicklistEntry pl : pList) {

items.add(new SelectOption(pl.getValue(),pl.getLabel()));

}

return items;

}

public PageReference search()

{

String query = 'Select name, type, ownership, industry from account ';

String queryLimit = ' limit ' + resultNo;

String conditions = '';

if (searchText != null && searchText != '')

conditions += ' name LIKE \'' + searchText + '%\'';

if (accountType != null)

{

if (conditions != '') conditions+= ' and ';

conditions += ' Type = \'' + accountType + '\'';

}

if (accountOwnership != null)

{

if (conditions != '') conditions+= ' and ';

conditions += ' Ownership = \'' + accountOwnership + '\'';

}

if (conditions != '')

query += ' where ' + conditions;

query += ' order by CreatedDate DESC ';

query += queryLimit;

//apexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO, query));

accountResults = Database.query(query);

return null;

}

}






Tuesday, October 7, 2008

A Picklist Component For Your Visualforce Pages

The last couple of months, numerous times in order to view picklist values in a form either for filter the views or creating custom data entry forms, I had to come up with ways to get around the limitations and somehow hook the inputField with the picklist to show what I needed.

Now, with the Salesforce Winter 09 improvements now it is much easier to tackle this issue.

In this article we will design an Apex component that gets the picklist name as attribute and renders the picklist values in form of a dropdown list.

This component will be really useful for non-multi-select picklists. I will post another article on multiselect picklists in near future.

Let's see how the Component will be added to the Page:

<c:picklist id="industryPicklist" value="{!accountIndustry}" picklistField="Industry" systemEntity="Account" usedefaultvalue="true"/>

What you need to define for the component is what object, which picklist field of that Object and a property of your page's Controller allowing you to read what user's choice was.

Now let's dive into the code and see how this component is developed. Like most other Visualforce page developments our component has two parts. The User Interface elements and the controller behind it.


<apex:component controller="picklistController">
<apex:attribute name="SystemEntity" description="" type="String" required="true" default="Account" assignTo="{!systemObject}"></apex:attribute>
<apex:attribute name="picklistField" description="" type="String" required="true" default="Type" assignTo="{!picklist_Field}"></apex:attribute>
<apex:attribute name="value" description="" type="String" required="true"></apex:attribute>
<apex:attribute name="defaultLabel" description="default value to be shown to user" type="String" assignTo="{!listDefaultLabel}" default="--None--"></apex:attribute>
<apex:attribute name="defaultValue" description="default value to be shown to user" type="String" assignTo="{!listDefaultValue}" default=""></apex:attribute>
<apex:attribute name="useDefaultValue" description="whether a default value to be shown or not" type="Boolean" assignTo="{!haveDefaultValue}" default="false"></apex:attribute>

<apex:selectList value="{!value}" size="1">
<apex:selectOptions value="{!items}"></apex:selectOptions>
</apex:selectList>
</apex:component>


The Component has only a selectList and a few attributes. Below are a list of attributes used for this component:

  • systemEntity: API name of the Object which has a picklist field
  • picklistField: API name of the Object's picklist field
  • value: This field should be bound to a property in parent Page's Controller
  • defaultLabel: Label of the default value shown to user, only visible when useDefaultValue is set to true.
  • defaultValue: Value of the default item shown to user, only visible when useDefaultValue is set to true.
  • useDefaultValue: A Boolean value tells the component whether to show a default value or not.
Now let's see how the Controller is look like:


public class picklistController {

//System object name, such as Account

private String systemObject;

public String getSystemObject() {

return systemObject;

}

public void setSystemObject(String value)

{

systemObject = value;

}

//picklist name such as "Type" of account object

private String picklist_Field;

public String getPicklist_Field() {

return picklist_Field;

}

public void setPicklist_Field(String value) {

picklist_Field= value;

}

//default item's value

public String listDefaultValue

{

get;

set;

}

//default item's label

public String listDefaultLabel

{

get;

set;

}

// whether the default item should be shown

public Boolean haveDefaultValue

{

get;

set;

}

public List getItems()

{

List entries_local;

if (systemObject != null)

{

//use GlobalDecribe to get a list of all available Objects

Map gd = Schema.getGlobalDescribe();

Set objectKeys = gd.keySet();

for(String objectKey: objectKeys)

{

//Iterate through all objects to locate selected Object

if (objectKey == systemObject.toLowerCase())

{

Schema.SObjectType systemObjectType = gd.get(objectKey);

Schema.DescribeSObjectResult r = systemObjectType.getDescribe();

Map M = r.fields.getMap();

Set fieldNames = M.keySet();

if (picklist_Field == null)

{

break;

}

//iterate through all fields of the object to locate the picklist field

for(String fieldName: fieldNames)

{

if (fieldName == picklist_Field.toLowerCase())

{

Schema.SObjectField field = M.get(fieldName);

Schema.DescribeFieldResult fieldDesc = field.getDescribe();

//extract the picklist values

entries_local = fieldDesc.getPicklistValues();

break;

}

}

}

}//end for

}

//Loading the picklist values and default item for our selectList

List options = new List();

//take care of Default value

if (haveDefaultValue == true)

{

if (listDefaultValue == null)

listDefaultValue = '';

if (listDefaultLabel== null)

listDefaultLabel = '--None--';

options.add(new SelectOption(listDefaultValue,listDefaultLabel));

}

//take care of picklist values

if (entries_local != null)

{

for(Schema.PicklistEntry picklistItem : entries_local)

{

options.add(new SelectOption(picklistItem.getValue(),picklistItem.getLabel()));

}

}

return options;

}

}



Thursday, October 2, 2008

A Visualforce Component that Beautifies actionStatus!

I have always liked to be able to have one of those fancy AJAX "loading..." messages for my AJAX postback calls. The background screen grays out, all components (textboxes, buttons, etc) become disabled and a little box says "Working on your request..." or "Running..." with that famous GIF animator of a spinning wheel and hey, I finally did it!

In this article I will show how to design the HTML to place the loading message into the center of the screen and gray out the background and the whole nine yards...

In this example I will first create a Visualforce component so that potentially I could use my beautiful "loading..." message on every page I desire, furthermore by forming it as a Component I can parameterize the look and feel of it and easily deploy it into different pages with different coloring styles.

In order to create a new Component click on the "setup" link on the right top corner of the page and find "Develop" item on the left hand-side and expand it, and click on "Components". Alternatively you can browse:
http://yourinstance.salesforce.ccom/apexcomponent/{YourComponentName}

Below you will be to get the code that goes into the component. The HTML code includes two Div elements, one for the gray background and the other for the actual box that the message is shown in and a javascript peice that adjusts the sizes of the div element based on the client browser's resolution and avialable place.


<apex:component >
<!-- Attribute Definitions -->
<apex:attribute name="BorderColor" type="String" required="true" description=""></apex:attribute>
<apex:attribute name="Width" type="String" required="true" description=""></apex:attribute>
<apex:attribute name="Height" type="String" required="true" description=""></apex:attribute>
<apex:attribute name="BackColor" type="String" required="true" description=""></apex:attribute>
<apex:attribute name="BackColor" type="String" required="true" description=""></apex:attribute>
<apex:attribute name="BorderSize" type="String" required="true" description=""></apex:attribute>
<apex:attribute name="ImageUrl" type="String" required="false" description=""></apex:attribute>
<apex:attribute name="Message" type="String" required="false" description=""></apex:attribute>
<apex:attribute name="messageStyle" type="String" required="false" description="Message inline style"></apex:attribute>
<apex:attribute name="BorderStyle" type="String" required="false" description="Message box border style: solid, outset, inset, etc"></apex:attribute>

<div id="salesforceSource_blurybackground" style="position:absolute; left:1px; top:1px; width:100%; height:100%; text-align:center; vertical-align: middle; background-color: #dcdcdc; opacity:0.6;filter:alpha(opacity=60)">
</div>
<div id="salesFroceSource_StatusBox" style="position:absolute; left:100px; top: 100px;width: {!Width}; height:{!Height}; opacity:1;filter:alpha(opacity=100)">
<table border="{!BorderSize}" cellpadding="0" cellspacing="0" style="border-left-color: {!BorderColor};
border-bottom-color: {!BorderColor}; width: {!Width}; border-top-color: {!BorderColor}; height:{!Height};
border-right-color:{!BorderColor}; border-style:{!BorderStyle}; background-color:{!BackColor};">
<tr>
<td align="left" valign="top">
<table border="0" cellpadding="4" cellspacing="0" style="width: 100%; height: 100%">
<tr>
<td style="border-bottom-color:{!BorderColor}; border-bottom-width:1px; border-bottom-style:solid;vertical-align:middle;">
<img src="{!ImageUrl}"/></td>
<td style="border-bottom-color:{!BorderColor}; border-bottom-width:1px; border-bottom-style:solid;vertical-align:middle;{!messageStyle}">
&nbsp;{!Message}</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
<script type="text/javascript">
var AgreementForm = document.getElementById("salesforceSource_blurybackground");
AgreementForm.style.height = window.screen.availHeight + "px";
AgreementForm.style.width = window.screen.availWidth + "px";

var ContainerElem = document.getElementById("salesFroceSource_StatusBox");
//ContainerElem.style.display = "block";
AlignToCenter(ContainerElem);

function AlignToCenter(Element)
{
var availableHeight = 0;
var availableWidth = 0;
if (Element.ownerDocument)
{
var docElement = Element.ownerDocument.documentElement;
availableHeight = parseInt(docElement.clientHeight);
if (availableHeight == "NaN") availableHeight = 0;

availableWidth = parseInt(docElement.clientWidth);
if (availableWidth == "NaN") availableWidth = 0;
}

if (availableHeight == 0 || availableHeight == "NaN")
availableHeight = window.screen.availHeight - 200;
if (availableWidth == 0 || availableWidth == "NaN")
availableWidth = window.screen.availWidth - 100;

var msgBoxTop = parseInt((availableHeight - parseInt(Element.clientHeight))/2);
var msgBoxleft = parseInt((availableWidth - parseInt(Element.style.width))/2);

if (msgBoxTop == "NaN" || msgBoxTop == 0)
msgBoxTop = 100;

Element.style.left = msgBoxleft + "px";
Element.style.top = msgBoxTop + "px";
}
</script>
</apex:component>


Pay a good attention to the Attribute section of the of the component those lines will empower you to change the look of the component entirely!

Ok, Now let's see how we can use this component. In Force.com in order to call your component you will need to call it a prefix tag called "c" and don't ask why because that's the way it is.


<apex:actionStatus id="status">
<apex:facet name="start">
<c:enhancedActionStatus BackColor="#efefef" borderColor="#336699" borderSize="3" height="50px" width="120px" ImageUrl="{!$Resource.AjaxAnimation}" Message="Loading..." messageStyle="color:darkred;font-size:11pt;font-weight:bold;"/>
</apex:facet>
</apex:actionStatus>

Wednesday, October 1, 2008

How To Mass Update All Your Object's Data?

We know all too well that in many cases organizations have way above 8000 Account records after couple of years of using Salesforce CRM and this number in even higher when we talk about Contacts, Opportunities, Tasks, etc.

Many Salesforce partners have provided solutions to mass update/insert data into your objects, but more often you are required to examine data and make sure about its integrity and soundness.

Limitations:
Based on governing limitation provided by force.com platform:
  • You can only retrieve 1000 records at a time
  • You can only update/insert/delete 100 records at a time
Luckily, there is something that you can do about this on your own. By doing a little bit more extra programming, use of visualforce components and apex Triggers you can overcome this problem.


The solution won't be as fast, but it will work!

Let's explore our example here: Let's say we need to check all of our Account records and make sure Parent child relationship between our Accounts are correctly in place.

In order to enforce that let's say we have added Dun & Bradstreet ID and Parent Dun & Bradstreet ID numbers as custom fields to our Account object.

Therefore, if the D&B Parent ID has a value but the Parent Account field is empty we have a broken link. I should point out here that the above is just an example, however you may have any type of logic to validate individual objects records, checking Accounts agaist Opportunities, Quotes against Invoices, etc.

Here is the idea, I have over 12,000 records to process and this is well above the governing limits. So if I could divide the task into smaller number of records and process each at a time then I could iterate through the records and fix them one by one without hitting into any limits.

So, I added an integer flag custom field to Account object to mark those I fix and move on through the further records in the next iterations.

I employ an actionPoller that runs every 10 seconds that triggers my operation of processing my Account records in batches of 50. The actionPoller is deactivate in the begining and can become activated via a button "Start". The same button will be able stop the process.

I follow this logic, at first I select the first 50 accounts which do not have parent but do have Parent D&B number and I will try to locate the Account Id of the parent via "Parent D&B number" if not sucessful I mark the Account Heirarchy status as "Broken Link" (for reporting purposes), if the parent was not found I mark the Account Heirarchy Status as "Parent Missing" and if everything was ok then I link up the Account to its parent.

Here the how the page code looks like:




<apex:page controller="AccountHierarchyBuilderCon" tabStyle="Account">
<apex:sectionHeader title="Account Hierarchy Builder"></apex:sectionHeader>
<apex:form >
<apex:pageBlock title="{!StatusText}" id="pageBlock">
<apex:pageBlockButtons >
<apex:commandButton value="{!buttonCaption}" action="{!TaggleActionPoller}" id="theButton"></apex:commandButton>
<apex:commandButton value="Launch Reports" action="{!LaunchReports}" id="ButtonReports" disabled="{!actionPollerEnabled}"></apex:commandButton>
<apex:commandButton value="Clean up" action="{!UpdateAccounts}" id="secButton" disabled="{!actionPollerEnabled}" onclick="return window.confirm('Removing status values will prepare this tool to re-examine invalid hierarchies. Are you sure about this?');"></apex:commandButton>
<apex:commandButton value="Clean up All" action="{!UpdateAccountsAll}" id="CleanAllButton" disabled="{!actionPollerEnabled}" onclick="return window.confirm('Removing all status values will prepare this tool to start over. Are you sure about this?');"></apex:commandButton>
</apex:pageBlockButtons>
<apex:actionPoller enabled="{!actionPollerEnabled}" interval="10" action="{!RunBatch}" rerender="pageBlock" status="status"></apex:actionPoller>
<apex:actionStatus id="status" startText="Working on the next batch..."></apex:actionStatus>
<br/>
<apex:pageBlockSection columns="1" id="resultsBlock">
<apex:pageMessages />
<div style="background-color:white;display:{!IF(invalidAccountSize=0,'none','block')};"> List of Invalid Accounts:</div>
<apex:pageBlockTable value="{!invalidAccounts}" var="a" title="Current Invalid Records" rendered="{!NOT(invalidAccountSize=0)}">
<apex:column value="{!a.Id}" ></apex:column>
<apex:column value="{!a.Name}" ></apex:column>
<apex:column value="{!a.parent_D_B_number__c}"></apex:column>
<apex:column value="{!a.Hierarchy_Status__c}"></apex:column>
</apex:pageBlockTable>
</apex:pageBlockSection>

</apex:pageBlock>
</apex:form>
</apex:page>


As you observed in the code, I have added a pageBlockTable component to show the invalid Accounts (Broken Links or missing parents) to the user between the intervals.

Here is the controller's code:

public class AccountHierarchyBuilderCon {
private Boolean pisWorking;
private Boolean pActionPollerEnabled;
private Integer ptotalRecords;
private Integer ptotalProcessed;
private List InvalidAccounts;
private String pbuttonCaption;
private Integer pTotalModified;
private Boolean pIsTesting = false;

public AccountHierarchyBuilderCon()
{
InvalidAccounts = new List();
pisWorking = false;
pbuttonCaption = 'Start';
pActionPollerEnabled = false;
ptotalProcessed = 0;
pTotalModified = 0;
ptotalRecords = -1;
}

public Boolean getIsTesting()
{
return pIsTesting;
}
public void setIsTesting(Boolean Value)
{
pIsTesting = Value;
}

public boolean getIsWorking()
{
return pisWorking;
}

public String getTotalRecords()
{
if (ptotalRecords == -1)
{
if (pIsTesting == true)
ptotalRecords = 10000;
else
ptotalRecords = [select count() from Account WHERE (Hierarchy_Status__c = '' OR Hierarchy_Status__c = null) AND (parent_D_B_number__c != null) limit 10000];
}

if (ptotalRecords == 10000)
return '10,000 or more';
else
return ptotalRecords+ '';
}

public Integer getTotalProcessed()
{
return ptotalProcessed;
}

public Boolean getActionPollerEnabled()
{
return pActionPollerEnabled;
}
public void setActionPollerEnabled(Boolean Value)
{
pActionPollerEnabled = Value;
}

public String getStatusText()
{
return ptotalProcessed + ' Account(s) out of ' + getTotalRecords() + ' are processed.';
}

public String getButtonCaption()
{
return pbuttonCaption;
}

public Integer getActionPollerInterval()
{
return 10;
}


public List getInvalidAccounts()
{
return InvalidAccounts;
}

public Integer getInvalidAccountSize()
{
return InvalidAccounts.size();
}


public PageReference TaggleActionPoller()
{
if (pActionPollerEnabled)
{
pActionPollerEnabled = false;
pbuttonCaption = 'Start';
}
else
{
pActionPollerEnabled = true;
pbuttonCaption = 'Stop';
}
return null;
}

public PageReference RunBatch()
{
if (pisWorking)
return null;
else
{
pisWorking = true;
}

try
{
UpdateAllAccounts();
}
catch(Exception ex)
{
ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.FATAL,ex.getMessage()));
TaggleActionPoller();
}

//end of the procedure
pisWorking = false;
return null;
}

public PageReference UpdateAllAccounts()
{
Integer sucessCount = 0;
InvalidAccounts = new List();
List Accounts = [select id, Name, parent_D_B_number__c, ParentId, Hierarchy_Status__c from Account WHERE (Hierarchy_Status__c = '' OR Hierarchy_Status__c = null) AND (parent_D_B_number__c != null) limit 50];


for(Account a : Accounts)
{
String ParentDBNo = (a.parent_D_B_number__c == null a.parent_D_B_number__c == '')? 'dummy_unknown' : a.parent_D_B_number__c;

List parents = null;
try
{
parents = [Select id, Name From Account where (D_B_Number__c = :ParentDBNo AND id != :a.id)];
}
catch(Exception e)
{}

Account parent = null;
if (parents == null parents.size() <= 0) { a.Hierarchy_Status__c = 'Broken Link'; InvalidAccounts.add(a); } else if (Parents.size() > 1)
{
a.Hierarchy_Status__c = 'Duplicated Parent';
InvalidAccounts.add(a);
}
else
{
parent = parents[0];
}

if (parent != null && parent.id != a.id)
{
a.ParentId = parent.id;
a.Hierarchy_Status__c = 'Updated';

sucessCount++;
}

try
{
update a;
}
catch(Exception ex)
{
a.Hierarchy_Status__c = 'Update Failed';
try
{
a.ParentId = null;
update a;
}
catch(Exception ex2)
{
//do nothing
}

InvalidAccounts.add(a);
}

ptotalProcessed += 1;
}//end for


String msg = '';

if (Accounts.size() == 0)
{
msg = 'All Records are up to date.';
TaggleActionPoller();
}
else
{
msg = ' A total of ' + sucessCount + ' Account(s) updated and ' + InvalidAccounts.size() + ' failed to get updated. This list will updated in '+ getActionPollerInterval() + ' seconds.';
}
ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.INFO, msg));

return null;

}


public PageReference UpdateAccounts()
{
//updates only invalid ones
updateAccountHierarchy('Updated','');
return null;
}

public PageReference UpdateAccountsAll() {
//updates all accounts
updateAccountHierarchy('','');
return null;
}

private void updateAccountHierarchy(String Find, String updateTo)
{
if (pIsTesting == true)
{
//do nothing. We are testing!
}
else
{
//this code cleans up all accounts Hierarchy_Status__c
for(Account[] accs : [Select id, Hierarchy_Status__c from Account where Hierarchy_Status__c != :Find limit 10000])
{
for(Account a : accs)
{
a.Hierarchy_Status__c = updateTo;
}

update accs;
}

Integer Count = [Select count() from Account where Hierarchy_Status__c != :Find limit 10000];
if (count > 0)
updateAccountHierarchy(Find,updateTo);

}
}


public PageReference LaunchReports()
{
PageReference ref = new PageReference('/apex/AccountHierarchyReports');
ref.setRedirect(true);

return ref;
}


}




The controller also forwards the user to another visualforce page which user will be able to see the report (all invalid accounts). This is something that you can implement easily on you own.

Now the last touch-ups. Our page iterates through all Accounts and mark them once and for all. Therefore, we will face two problems:
  • What if we wanted to re-run the process?
    This can be taken care of if you write a method to remove the flags from all or some of the records based on what makes sense to you.
  • What if I fixed one of the invalid Accounts, how the can the system know or the flag to be set to OK?
    The answer is a Trigger on the Account object.





trigger HierarchyStatusReferesh on Account (before update) {
// this trigger removes the previous value of "Hierarchy_Status__c" field
// if the account's parent_duns_number__c or Ultimate_Parent_D_B_Number__c are changed.
// this trigger work in conjunction with Account Hierarchy Builder tool.


Map entries = new Map(Trigger.old);

for(Account newAcc: Trigger.new)
{
Account acc = entries.get(newAcc.id);
if (Acc.parent_D_B_number__c != newAcc.parent_D_B_number__c)
newAcc.Hierarchy_Status__c = '';
}

}