Thursday, July 24, 2014

Identify Ultimate Parent in an Account Hierarchy

To build Account hierarchy Salesforce has native "Parent Account" lookup field in Account object. Unfortunately Salesfroce does not go further to provide native solution to automatically calculate "Ultimate Parent" or "Top Level Account" if you have more than two levels in Account hierarchy. As per our experience organizations have up to 5 (sometimes more)  levels of Account hierarchy.

Having such "Ultimate Parent" field will allow SFDC Users/Administrators to do a lot of advanced data exercises using grouping, summation or aggregation by the entire account tree.
For instance:
  • total sales to multi-level hierarchies
  • list of Contacts in entire account hierarchy
  • views, reports and dashboards for account families
There are a few workarounds to create such useful field. Please find below the most popular workarounds with analysis about their PROS and CONS.

#1 Use formula field to calculate Ultimate Parent Name or ID

Most obvious and popular solution. Using formula field you can determine the ultimate parent in the hierarchy. Formula below returns Ultimate Parent as a text formatted to hyperlink to use it in Account layout. Click to the link will open Ultimate Account page. This formula supports up to 10 levels of Accounts hierarchy.

IF(LEN(Parent.Name) < 1, HYPERLINK("/"&Id, Name,"_parent"), 
IF(LEN(Parent.Parent.Name) <1, HYPERLINK("/"&Parent.Id,Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Name) < 1, HYPERLINK("/"&Parent.Parent.Id,Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Name) < 1, HYPERLINK("/"&Parent.Parent.Parent.Id,Parent.Parent.Parent.Name,"_parent"),
IF(LEN(Parent.Parent.Parent.Parent.Parent.Name) < 1, HYPERLINK("/"&Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), 
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"),
IF(LEN(Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name) < 1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), "Ultimate Parent Beyond 10 Levels"))))))))))
If needed formula could be updated to return text instead of hyperlink.

PROS: Easy to implement
CONS: Covers very limited number of use cases because the result of the formula is text. In move advanced cases you need Ultimate Account as a lookup field to use in reports, APEX code or in Roll-up formulas.

#2 Singe synchronous APEX trigger to calculate the Ultimate Parent when Account created or updated.

The trigger requires and has to calculate/re-calculate custom "Ultimate Parent" lookup field on Account object (read-only for users).

Let's consider few scenarios using following Accounts hierarchy tree as example:

Accounts hierarchy

Accounts 1 and 8 are "Ultimate or Top Accounts" (i.e. do not have parent Accounts)

Case 1: New Account created as a child of Account 3. The trigger has to calculate Account 1 as Ultimate Parent for new Account and update this Account with calculated value for "Ultimate Parent" field. This is easy recursive update.

Case 2: Account 3 updated to be the child of Account 1 instead of Account 2 (i.e. Account 3 moved up along the Account 1 hierarchy tree branch).
In this case Account 1 still Ultimate Parent for Account 3, 4 and trigger no need to update these two Accounts.

Case 3: Account 2 updated to be on the top of  the hierarchy tree  (i.e. Account 2 will become Ultimate Account for all related child Accounts).
In this case the trigger has to calculate Account 2 as Ultimate Parent for Accounts 3-7 and update these five Accounts with new Ultimate Parent. This is quite complicated recursive update. To find all impacted child Accounts the trigger will have to traverse down through Account 3 and Account 5 branches (layers) to identify all related to these branches Accounts and update them with new Ultimate Parent value.

Case 4: Account 2 updated to be the child of Account 8 instead of Account 1 (i.e. Account 2 with all related child Accounts moved to another [Account 8] hierarchy tree branch).
In this case trigger has to calculate Account 8 as Ultimate Parent for Accounts 2-7 and update these six Accounts with new Ultimate Parent value. This is also complicated recursive update which involve multiple parent to child account layers.

The most complicated part here is to traverse parent-child relationships from account to account. Unfortunately SFDC does not support such traverse so you can not do this in a single SOQL query. What you can - is to include parent.parent.parent.parent.parent.id in your SOQL select. Than loop through the result and if none of them are null - launch another SOQL query until you find a parent without parent. And then assign it as Ultimate Parent for involved Accounts.

Sounds complicated even in theory. But after number of experiments and brainstorming we finally built such trigger and even used it for a while. Unfortunately the trigger code was pretty ugly and solution (as appeared) was fragile. In particular cases - when hierarchy has a lot of parent to child account layers - trigger hits SFDC's governor limits. In cases when the trigger was able to handle governor limits it took a lot of time to finish - up to 5 seconds - this was not acceptable from performance point of view.

That's why we DO NOT recommend such approach and do not sharing here the trigger's code.

PROS: Works in not complicated SFDC implementations where accounts hierarchy does not have a lot of parent to child account layers.
CONS: Covers only limited use cases. Ugly and fragile code which apparently hits SFDC governor limits. Performance issues.

#3 Two components solution: synchronous APEX trigger and APEX batch

In this solution the trigger has to handle only inserts and updates along the single Account hierarchy tree branch (see Cases 1 and 2 above) and use overnight APEX batch to catch and calculate more complicated cases.

APEX batch class has more wider governor limits, could be launched overnight and does not have strict performance requirements.

The final solution consists of following components:
  • custom "Ultimate Parent" lookup field on Account object (read-only for users).
  • an APEX Trigger that populates "Ultimate Parent" field for new created or updated Accounts
  • scheduled APEX batch class to recalculate all Accounts to catch cases not covered by trigger (see Cases 3 and 4 above).
PROS: The solution covers all possible use cases. Stable code which does not hit SFDC governor limits. No performance issues.
CONS: In cases when Account moved to another branch (family) Ultimate Parent will be re-calculated only next night. But such scenarios happens quite rare.

APEX Trigger: SetUltimateParent
trigger SetUltimateParent on Account (before insert, before update) 
/*
Version        : 2.0
Company        : Websolo Inc. 
Description    : The trigger calculates Ultimate Parent ID ONLY for the updated/inserted Account with Hierarchy of parent Accounts to 5 levels up.

Scheduled APEX job will recalculate Ultimate Parent ID for all Account overnight to make sure they calculated properly in case if for example Account moved to another Account
*/ 
{
    list<Account> ListAcc = new list<Account>();
    set<id> idAcc = new set<id>();
    for(Account a: Trigger.new)
    {
        if(a.ParentId != null)
        {
            ListAcc.add(a);
            idAcc.add(a.id);            
        }
    }
    
    List<Account> AccUltP = [select id, Name,
                                     ParentId,
                                     Parent.ParentId,
                                     Parent.Parent.ParentId,
                                     Parent.Parent.Parent.ParentId,
                                     Parent.Parent.Parent.Parent.ParentId                                     
                                     from 
                                        Account
                                     where 
                                        ParentId != null
                                        and
                                        id IN: idAcc];   
   if(AccUltP.size() > 0)
   {                                                                        
       for(Account a: ListAcc)
       {
        for(Account b: AccUltP)
        {           
            if(a.id == b.id)
            {
                if(b.Parent.Parent.Parent.Parent.ParentId != null)
                {
                    a.Ultimate_Parent__c = b.Parent.Parent.Parent.Parent.ParentId;
                }           
                else
                {
                    if(b.Parent.Parent.Parent.ParentId != null)
                    {
                        a.Ultimate_Parent__c = b.Parent.Parent.Parent.ParentId;
                    }       
                    else
                    {
                        if(b.Parent.Parent.ParentId != null)
                        {
                            a.Ultimate_Parent__c = b.Parent.Parent.ParentId;
                        }       
                        else
                        {
                            if(b.Parent.ParentId != null)
                            {
                                a.Ultimate_Parent__c = b.Parent.ParentId;
                            }       
                            else
                            {
                                if(b.ParentId != null)
                                {
                                    a.Ultimate_Parent__c = b.ParentId;
                                }
                                else
                                {
                                    a.Ultimate_Parent__c = b.id;
                                }                               
                            }                   
                        }               
                    }           
                }                   
            }
         }
      }
   }                                    
}

APEX Batchable Class: UltimateParentUpdateBatchable
/*
Version        : 2.0
Company        : Websolo inc. 
Date           : 11/2013
Description    : This batchable class calculates Ultimate Parent for Account with Hierarchy of parent Accounts to 5 levels up.

*/ 
global class UltimateParentUpdateBatchable implements Database.Batchable<SObject>, Database.Stateful 
{
    global Database.QueryLocator start(Database.BatchableContext bc) 
    {
        return  Database.getQueryLocator([select id, Name, ParentId, Parent.ParentId, Parent.Parent.ParentId, Parent.Parent.Parent.ParentId, Parent.Parent.Parent.Parent.ParentId from Account]);
    }
    global void execute(Database.BatchableContext bc, List<SObject> batch)      
    {               
        list<Account> ListAcc = new list<Account>();
        set<id> idAcc = new set<id>();  
        for (Account a : (List<Account>) batch) 
        {
           if(a.ParentId != null)
           {
              ListAcc.add(a);
              idAcc.add(a.id);            
           }                
        }
        List<Account> AccUltP = new List<Account>();
        if(Test.isRunningTest())
        {
            Account Ac1 = new Account();
            Ac1.id = ListAcc[0].id;
            Ac1.Name = 'Test1';
            AccUltP.add(Ac1);
        }
        else
        {
                            AccUltP = [select id, Name,
                                             ParentId,
                                             Parent.ParentId,
                                             Parent.Parent.ParentId,
                                             Parent.Parent.Parent.ParentId,
                                             Parent.Parent.Parent.Parent.ParentId                                     
                                             from 
                                                Account
                                             where 
                                                ParentId != null
                                                and
                                                id IN: idAcc]; 
        }  
       if(AccUltP.size() > 0)
       {                                                                        
           for(Account a: ListAcc)
           {
            for(Account b: AccUltP)
            {           
                if(a.id == b.id)
                {
                    if(b.Parent.Parent.Parent.Parent.ParentId != null)
                    {
                        a.Ultimate_Parent__c = b.Parent.Parent.Parent.Parent.ParentId;
                    }           
                    else
                    {
                        if(b.Parent.Parent.Parent.ParentId != null)
                        {
                            a.Ultimate_Parent__c = b.Parent.Parent.Parent.ParentId;
                        }       
                        else
                        {
                            if(b.Parent.Parent.ParentId != null)
                            {
                                a.Ultimate_Parent__c = b.Parent.Parent.ParentId;
                            }       
                            else
                            {
                                if(b.Parent.ParentId != null)
                                {
                                    a.Ultimate_Parent__c = b.Parent.ParentId;
                                }       
                                else
                                {
                                    if(b.ParentId != null)
                                    {
                                        a.Ultimate_Parent__c = b.ParentId;
                                    }
                                    else
                                    {
                                        a.Ultimate_Parent__c = b.id;
                                    }                               
                                }                   
                            }               
                        }           
                    }                   
                }
             }
          }
          update ListAcc;
       }                    
    }
    global void finish(Database.BatchableContext bc) 
    {
        
    }
    private static testMethod void test() 
    {
        Test.startTest();
        Account Acc1 = new Account();
        Acc1.Name = 'Test1';
        insert Acc1;
        Account Acc2 = new Account();
        Acc2.Name = 'Test2';        
        Acc2.ParentId = Acc1.id;
        insert Acc2;
        
        Test.stopTest();     
    }       
}

APEX Batchable Class: UltimateParentUpdateBatchable
global class UltimateParentUpdateSchedulable implements Schedulable
{
    global void execute(SchedulableContext ctx) 
    {
        Database.executeBatch(new UltimateParentUpdateBatchable(), 200);
    }
    private static testMethod void test() {
        Test.startTest();
        System.schedule('UltimateParentUpdateBatchable', '0 0 0 * * ?', new UltimateParentUpdateSchedulable());
        Test.stopTest();
    }    
}

Monday, July 14, 2014

Custom Validation For Lead Conversion

To increase data quality Sales Managers encourage their Sales Representatives to gather as much as possible information about Leads before Conversion it to Contact, Account and Opportunity.

There are multiple techniques to enforce SFDC Users to enter values to all required fields before the Conversion. Let's discuss these techniques with implementation details and all its PROS and CONS.

How to enforce Sales Reps to enter values to required Lead fields before the Conversion.


#1 Make fields required on Lead layout

The most obvious and straight forward solution. But not the best one. Salesforce Lead layout allows Administrator to make some fields required (mandatory). But it's hard for Sales representative to get values for all required fields at once. In most cases Sales person can enter some data initially and then revisit the Lead record to update the rest of the important fields later.

PROS: Easy to implement
CONS: Not convenient for Users. To make any update (edit the Lead) User have to enter values in all required fields at once.

#2 Use a validation rule to fire when Lead is actually converted

Such validation rule will help to make certain fields required before converting the Lead but not require them to be entered when User edits the Lead's record.

Here is example of Validation Rule (could be adjusted to meet particular business requirements)

Rule Name:  Lead_Conversion_Validation

Error Condition Formula:
AND(IsConverted,
OR(
ISBLANK(Email),
ISBLANK(Phone),
ISBLANK(Website),
ISBLANK(TEXT(LeadSource)),
ISBLANK(TEXT(Range_of_Revenues__c)),
ISBLANK(State),
ISBLANK(Street),
ISBLANK(City),
ISBLANK(PostalCode),
ISBLANK(Country)
))

Error Message:
Please fill out following fields before conversion:
- Email
- Phone
- Full Address
- Website
- Range of Revenues
- Lead Source

PROS: Pretty easy to implement
CONS: Still not convenient for Users. The Validation Rule fires ONLY when user hit the Convert button on the Lead page, entered data on Conversion page and clicked Convert button. If something goes wrong (any required field is missed) - user will lose his time and will have to return back to the Lead page to enter missed data. Also Error Message is very general and does not really help the User to identify the missed field(s).

#3 Use workflow to update the Lead's Record Type/Layout when User enters all required for conversion fields

To build this solution you will have to create and support two Lead Record Types and related identical Lead layouts:
 - NotReadyForConversion (default) Lead Record Type and related Lead layout without Convert button
 - ReadyForConversion Record Type and related Lead layout with Convert button

Custom workflow have to check/monitor if all required fields have data.
When the necessary fields are filled out workflow action will update the Lead's Record Type to ReadyForConversion and Users will see the Lead in new layout - with Convert button.

PROS: Allows user to edit the Lead and Convert it only when all required fields are filled out.
CONS: Required two Record Type and two Layouts. Difficult to maintain - especially if you have multiple Leads Record Types/Layouts/User Profiles. Still not convenient for Users. No Error message. User can't see/check out which fields are still missing.

#4 Override the standard 'Convert' button with custom button and related VF page/Controller

SFDC Administrator/Developer can create a new custom 'Convert' button to be used on Lead layout instead of native one. Click to the button will call popup window with custom VF page and Controller to check if all required fields have data (based on particular business requirements).
  • If all required fields are filled out - embedded in VF page JavaScript will auto close the pop up window and redirect User to Conversion page.
  • If any required field(s) not filled out yet - VF page will show User Error message with information about which field(s) have to be entered. By click to Close button / or lose pop up window focus / or in 10 seconds of inactivity - close/auto close pop up window. User still on the Lead page and can enter data in missed field.
PROS: Allows user to edit the Lead and Convert it only when all required fields are filled out. User can easy check out which fields are missing to let them to convert the Lead.
CONS: Required some Force.com development. But final result worth the efforts - Sales Users are happy with the solution which help then save time and meet data quality criteria.

Solution Code Sample: (please contact us if you have questions or want to modify this solution to meet your particular business requirements)

Custom Button

Label: Convert
Object Name: Lead
Name: ConvertCustom
Behavior: Execute JavaScript
Display Type: Detail Page Button
OnClick JavaScript

var url = "/apex/Lead_Conversion_Validation?id={!Lead.Id}";
var width = "350";
var height = "350";
window.open(url, '','scrollbars=no,resizable=no,status=no,toolbar=no,menubar=no, width=' + width + ',height=' + height + ',left=' + ((window.innerWidth - width)/2) + ',top=' + ((window.innerHeight - height)/2) );

Visualforce Page: Lead_Conversion_Validation
<!--
Version        : 1.0
Company        : Websolo Inc. (websolo.ca)
Date           : 07/2014
Update History :
-->
<apex:page standardController="Lead" showHeader="false" sidebar="false" extensions="LeadConversionValidation">
<style>
h2
{
  width: 300% !important;
}
</style>
   <script>
window.onload = function(){
window.onblur = function(){window.close();}
     if(document.getElementById('sd').innerHTML == "")
     {
      var ids = document.getElementById('ids').innerHTML;
      window.opener.location.href="/lead/leadconvert.jsp?retURL=%2F" + ids + "&id=" + ids;
      window.top.close();
     }
     else
     {
         setTimeout(function(){
          window.close();
        }, 10000);   
     }
};
   </script>
      <div id="sd" style="display: none;">{!reft}</div>
      <div id="ids" style="display: none;">{!leadobjid}</div> 
 <apex:pageBlock title="Lead Conversion Validation">
      <apex:pageBlockButtons location="bottom">
       <button onclick="window.close();">Close</button>
     </apex:pageBlockButtons>
      <apex:pageMessage severity="error" strength="1">
          <apex:outputText value="{!error}" escape="false" />
          <apex:outputText value="{!sterrmsg}" escape="false" rendered="{!sterr}"/><br />         
      </apex:pageMessage>
  </apex:pageBlock>
</apex:page>


Apex Class: LeadConversionValidation
(controller - code could be adjusted to meet particular business requirements)
/*
Version        : 1.0
Company        : Websolo Inc. (websolo.ca)
Date           : 07/2014
Update History :
*/
public class LeadConversionValidation
{
    public id leadobjid {get; set;}
    public String error {get; set;}
    public String reft {get; set;}
    public Boolean sterr {get; set;}
    public String sterrmsg {get; set;}
    public LeadConversionValidation(ApexPages.StandardController controller)
    {
      sterr = false;
      sterrmsg = 'To convert Lead please change Lead Status to Contacted';
      error = 'Please fill out following fields before conversion:<br />';
      reft = '';
      leadobjid = ((Lead) controller.getRecord()).id;
      Lead leadobj = [select Status, LastName, Keywords__c, Financing_Notes__c, Email,Phone,Website,Employees__c,Rapport__c,LeadSource,Range_of_Revenues__c,State,Street,City,PostalCode,Country from Lead where id =: leadobjid ];
      if(leadobj.LastName == null ||
        leadobj.Keywords__c  == null ||
        leadobj.Financing_Notes__c == null ||
        leadobj.Email == null ||
        leadobj.Phone == null ||
        leadobj.Website == null ||
        leadobj.Employees__c == null ||
        leadobj.Rapport__c == null ||
        leadobj.LeadSource == null ||
        leadobj.Range_of_Revenues__c == null ||
        leadobj.State == null ||
        leadobj.Street == null ||
        leadobj.City == null ||
        leadobj.PostalCode == null ||
        leadobj.Country == null)
        { 
          reft = '1';
          if(leadobj.Keywords__c  == null){error = error + '- Keywords<br />';}
          if(leadobj.Financing_Notes__c  == null){error = error + '- Call Notes (at least 100 characters)<br />';}
          if(leadobj.Email == null){error = error + '- Email<br />';}
          if(leadobj.Phone == null){error = error + '- Phone<br />';}
          if(leadobj.Website == null){error = error + '- Website<br />';}
          if(leadobj.Employees__c  == null){error = error + '- Employees Range<br />';}
          if(leadobj.Rapport__c == null){error = error + '- Rapport<br />';}
          if(leadobj.LeadSource == null){error = error + '- Lead Source<br />';}
          if(leadobj.LastName == null){error = error + '- Lead Name<br />';}
          if(leadobj.Range_of_Revenues__c == null){error = error + '- Range of Revenues<br />';}
          if(leadobj.State == null || leadobj.Street == null || leadobj.City == null || leadobj.PostalCode == null || leadobj.Country == null){error = error + '- Full Address<br />';}     
        }
        if(leadobj.Status != 'Contacted')
        {
          //sterr = true;
          if(reft == '1')
          {
           sterr = true;
          }
          else
          {
           error = '';
           reft = '1';
           sterr = true;
          }
        }         
    }
}


Apex Class: TestLeadConversionValidation (test coverage)
Version        : 1.0
Company        : Websolo Inc. (websolo.ca)
Date           : 07/2014
Update History :
*/
@isTest
private class TestLeadConversionValidation
{
static testMethod void myUnitTest() 
{
Lead ld = new Lead();
ld.LastName = 'test';
ld.Company = 'test';
ld.Email = 'test@test.com';
ld.Phone = '1112223456';
insert ld;
LeadConversionValidation obj = new LeadConversionValidation(new ApexPages.StandardController(ld));
}
}