How to count unique Account in an Opportunity report in Salesforce ?

Requirements background:
1. User would like to know how many accounts have closed won opportunity last quarter?
2. To show all opportunity related in point (1) above in the same report.

Analysis:
1. Create Opportunities report type with Summary report.
This approach maybe work, but if you have a lot of accounts, it is not a good solution to count number of account manually

2. Create Account report with Cross Filter with Opportunity
Using this approach, we can get number of Account, but:
- No information on Opportunity in the report
- We cannot get Opportunity Closed Date as filter or criteria

Solution: use the "Power of One"

Here you go:
1. Create a formula field in Account with return type = Number

2. Use this field in the report and Sum it

As you see in above report, it is exactly the same with the first report, but we just add a field to count. So, user can easily see that we have 8 closed won opportunity for 5 accounts last quarter.

You can use the same solution for other object related to Account, from: Case, Contact, Opportunity, etc and for other master-detail or lookup relationship objects.

What happens when Lead is converted in Salesforce ?

1. Once a lead is converted, lead detail is no longer visible from Lead page payout. If you note the Lead Id before conversion, paste the Lead Id into URL, it will show information of:

• When the lead is converted
• Account and Contact created or existing Account and Contact attached
• Opportunity created

2. User still can access lead detail from report using report type Leads with converted lead information.

3. Lead is frozen, you even cannot update the lead record, even using API.

4. Converted field is ticked and Converted Date field will be populated on the date of conversion happened.

5. Chatter feed posts associated with the lead are not migrated anywhere and the lead posts are no longer available in the Chatter feed, unless access from API.

6. When you convert a lead into an existing account, you not automatically follow that account. However, when you convert the lead into a new account, you automatically follow the new account, unless you disabled Automatically follow records that I create in your Chatter setting.

7. If the Lead associated with campaigns, all Campaign History copy into Contact created. For opportunity, it will create Campaign Influence if fit into criteria and one will be selected as Primary Campaign Source based on latest campaign attached to the Lead.

8. All notes and attachments from the lead are converted and attached to the new account and contact (not to opportunity).

9. All open activities and activities history from the lead are converted and attached to the new account, contact, and opportunity.

10. If an existing Account have the same Company name as those specified on the lead, you can choose to attach to existing Account, so this will not create new Account. 

11. Lead with same First and Last Name, but different Company with existing Account will always be created as new Contact. 

12. When user select to attach to an existing Account, user will give option to attach to existing Contact for the same Account only if First or Last Name is similar.

13. When you attach to existing Account and Contact, information from the lead is inserted only into blank fields; Salesforce does not overwrite existing account and contact data. A checkbox has 2 possible values: 0 or 1, so it will never be considered as blank field, so checkbox won't be overwritten.

14. Update Last Modified Date and Last Modified By fields on converted leads when picklist values included on converted leads are changed.

15. You can’t convert a lead that’s associated with an active approval process or has pending workflow actions.

16. By default, Lead owner will be record owner of the created Account, Contact and Opportunity.

17. If you have Record Type in Account, Contact or Opportunity, created record will follow Default Record Type of the record owner as in the Profile.

18. Contact will be added in Opportunity Contact Role as Primary

Reference:

• Converting Leads
• Understanding Lead Conversion
• What happens when I convert a lead?
• Why the Checkbox field is not being updated in Lead Conversion?

Query Date Field in Salesforce using SOQL

Looking record for Blank Date
In report, we can easily add filter date equals <blank> , leave the value empty

In validation rule or workflow rule, we can use ISBLANK() formula to check if the date field is blank, example: ISBLANK( default_date__c )

To run SOQL query for records with blank date
example: SELECT Id, Name FROM Account WHERE default_date__c = NULL


Compare with a date
example: SELECT Id, Name, default_date__c From Account WHERE default_date__c > 2014-01-15 ORDER BY default_date__c 

Note: it have to be in YYYY-MM-DD (need to be 4-2-2 characters, so 2014-1-15 will not works)


Reference:

• Date Formats and Date Literals
• Formula Operators and Functions I – Z

Salesforce: Global Search Result on Custom Object

This is continuation of previous blog Salesforce Search on Custom Object and Chatter.

We have an issue in office today that some users cannot see search result from a custom object, while some other users can see the result using same keywords.

Analysis:

• Object permission: user have read permission in profile and user able to open the record URL
• Tab: should be not because other user able to search and result shown

Finally we get the caused, it is because the Tab Setting for that User Profile is Tab Hidden, while other user able to search have setting with Default Off.

Conclusion, "Tab Hidden" in Tab Setting for a profile has effect to search result, it is the same as the tab is never created for that Profile.

Since Winter '16 release (Oct 2015), Salesforce introduce "Allow Search" option for custom object, if this is not enabled, the object will be not included in search result.

SUMMARY: for user able to search result from a custom object, they should satisfy ALL items below:

• Tab must be created for that custom object, user need to have visibility permission for that tab from Profile or Permission Set.
• Allow Search must be enabled for that custom object.

Reference: Guidelines for Making Search Faster

Last update: 8 May 2016

Salesforce: Customize mouse Hover Lookup

This will be gonna a simple blog post. Many users like this, when user hover mouse over a lookup field, Salesforce will show some information related to the lookup record in that little popup window. So, instead of click the field, just hover it and user will not lose that page. Users can quickly view information about a record before clicking View for the record's detail page or Edit for the edit page.

The same information will be shown when user hover a record in the Recent Items list on the sidebar.

Can we modify fields show in the popup? YES, example we want to customize field in Account hover when user hover it from Contact page layout:

1. Don't be confuse that we are not change anything in Contact Page Layout, although hover happened in Contact page
2. Go to Account object and edit Account Page Layout assigned to particular profile and record type (if any)
3. Click Mini Page Layout at the top of the editor
4. Select fields you want it show when user hover over the object

Note:

• There are field that you cannot remove, such as: object name, Parent Account for account object (to hide this, you can use Field Level Security, but of course field will be not visible anywhere, from: page layout, report, and etc)
• There are field that always shown, example in User object, it will always show: Title, Email, Phone, Manager

Reference:

• Customizing User Interface Settings
• Customizing Page Layouts with the Enhanced Page Layout Editor

Salesforce: Edit Locked Record

In Approval Process, when a user click "Submit for Approval" or auto submit by Process Builder, record will be locked by default. This will prevent user to edit any fields in the record from page layout, notice a pad lock icon at the left of the buttons, even when you try to update via API tool, you will get error "The Entity Is Locked For Editing".

So, is there any user able to edit the record? YES, they are:
1. User with System Administrator profile
2. User with Modify All Data permission
3. User with Modify All permission in the object of record locked
4. If the Approval Process configuration in Record Editability Properties = Administrators OR the currently assigned approver can edit records during the approval process. This will allow current approver ability to edit the record.

Note: user does not fall into any of categorization above will NOT able to edit the record, including:

• Record owner
• User in higher role hierarchy of record owner
• User in higher role hierarchy of approver
• Delegated approver of the approver

Salesforce: Formula with CONTAINS() function

You can use CONTAINS() function in Salesforce from formula field, validation rule, workflow rule and etc. But it commonly used in validation and workflow rules to search for a character or string in a text field.

If you are reading Salesforce documentation, it said the functions is to compare two arguments of text and returns TRUE if the first argument contains the second argument. If not, returns FALSE. Be aware that comparison using Contains() is case sensitive.

This function is only for Text field. For Picklist, add TEXT() function to convert the picklist value to text. For Picklist (Multi-Select), use INCLUDES() to see if a multi-select picklist has a specific value.

Usage: CONTAINS(text, compare_text)
CONTAINS will return TRUE if "compare_text" is found in "text" and FALSE if not.

 CONTAINS("abc", "abcd") --> FALSE  
 CONTAINS("abcd", "abc") --> TRUE  

Example: IF(CONTAINS(Product_Type__c, "part"), "Parts", "Service")
In this example, we compare if part is exist in a custom field Product_Type__c

Some Use Case:
1. Searching for Text
Example: CONTAINS(Comments__c, "BadWord")
Returns TRUE if "BadWord" is found anywhere in Comments__c

2. Check if an unknown string or character matches a defined set of strings or characters.
Example: CONTAINS("0123456789", Address)
Return TRUE if Address values such as 1, 2, 9, 01, 789, or any other substring of "0123456789"

Example: CONTAINS("CA:NV:FL:NY", BillingState)
Return TRUE if BillingState is CA or NV or V:F or L or FL:NY or any exact match of "CA:NV:FL:NY".
NOTE: when using contains with the multiple operator (:) contains then becomes equals.

You can enhance above formula to be
AND(LEN(BillingState)=2,CONTAINS("CA:NV:FL:NY",BillingState))
this formula will ensure that BillingState is 2 characters in length. But, it still return TRUE for :N or V: and other any 2 character EXACT match of each state "CA:NV:FL:NY"

Of course you can still enhance the formula above to check if the 2 characters do not contain :

For a picklist field, use TEXT(picklist_field_name) to convert a picklist value into a text value so that you can work with the value in functions that support text value.

Reference:

• Formula Operators and Functions A – H
• How do I use the CONTAINS function?
• Tips for Working with Picklist and Multi-Select Picklist Formula Fields

Salesforce: Revenue Forecast error, duplicate value found: unknown duplicates value on record with id: unknown

This blog is a continuation of a blog I wrote earlier batch upload Quota in Forecasts. Some people may see following error duplicate value found: <unknown> duplicates value on record with id: <unknown> in some of the record after insert into RevenueForecast object.

Here is my finding:

1. Each user only allow to have 1 Quota in 1 Forecast Period
2. The key here is OWNERID + STARTDATE
3. Salesforce will reject if we create new record of Revenue Forecast with the duplicate key (OWNERID + STARTDATE) and throw error message duplicate value found: <unknown> duplicates value on record with id: <unknown>

Example: your company set the forecast period quarterly. But, when you want to load quota for next quarter and you believe no one has load it before, but you get duplicate value found: <unknown> duplicates value on record with id: <unknown> error for a few users. What does it mean? It mean a record of quota has been created for those users in the quarter.

It doesn't mean someone has load it before you, although it maybe. Here is the permission related to load quota:

• Edit Personal Quota, user with this permission able to set their own quota from: My Settings | Personal | Advanced User Details, then scroll down to Quota related list.
• If user also have permission Manage Users, this user able to set quota for other user, include using Data Loader or other API tool if the have API Enabled permission.

If user go to a quota related list in user detail page, then change Range Length, example: by default it only show 3 periods and user click the dropdown and select 4 periods, Salesforce will create new quota record with 0 value.

Here is the illustration, initially:

User select 4 periods in Range Length:

Salesforce will create new record for FQ4 2014 with 0 value in the background. This is the most caused admin fail to load quota for new period because they already exist.

Solution:

1. Before you load quota using Data Loader, which is insert to RevenueForecast, query to the RevenueForecast on particular period you are working on, filter by StartDate, example: Select r.Id, r.OwnerId, r.StartDate, r.CurrencyIsoCode, r.Quota from RevenueForecast r where r.StartDate = 2014-01-01
2. If you find any result, use it to split data in CSV file you will use to insert into 2 CSV file: one for insert and one for update.
3. Only insert to RevenueForecast for data you not found in query above.
4. For data found in query above, update the RevenueForecast based on Id in above query.

Reference:

• Setting Quotas for Customizable Forecasting

Salesforce: How to hide Probability field in Opportunity page layout?

Probability is one of the standard Opportunity fields in Salesforce. Based on the sales process, some organizations do not use it.

But, when we edit the page layout, there is no option to remove Probability from page layout. Hide mouse over the field in edit page layout, you will not see minus icon next to it.

The same for the following fields in Opportunity, you cannot hide them from the page layout:

• Opportunity Name
• Account Name
• Stage
• Close Date
• Probability (%)

When you edit page layout, there is a blue dot before the field name. You cannot hide all fields with a blue dot in page layout.

Solution:
You can hide the Probability (%) field from Field-Level Security:

• Setup | Customize | Opportunity
• Look for Probability (%) and click the field name
• Click 'Set Field-Level Security' button
• Untick Visibility checkbox for profile do not want to see this field
• Save and done

Cons:

Since we are hiding the field in the field-level, the user will not able to see it anywhere, including in report, or in the scenario, you have many record types and page layout, the user will not able to see it in the other page layouts as well.

Salesforce: How to export Attachments ?

In previous blog, we discuss on how to mass upload files to Salesforce as attachment? But, can we export attachment using Data Loader? Yes, but the files exported will be in binary format in a CSV file, it is not in the original filename and format, so how?

One of the solution using File Exporter, it is a free app develop by Salesforce Lab. But, this application actually is pretty complicated, it only work with some version of Data Loader only. I cannot make it works, although has follow the steps in document.

Finally, we find a free and easy tool to export files back to the original format. It is http://dataloader.io, a product of Mulesoft, the free version already support our need. With paid version, you can: schedule more task, SFTP support, number of connections, task expiration and support.

How it works?
1. Login to http://dataloader.io with your login to Salesforce, it use SSO
2. Click "New Task" and select "Export" button
3. Select Attachment object, click Next
4. You can select just the Body field, or with some fields, or all fields
5. Click "Save & Run"
6. Wait the process

Once the task done, you can download the attachments by clicking on the task manager on the link next to the export task. This will download all of the attachments compressed into a .zip file, example: Attachment Export-02_13_2014-13_43_08.zip

If you select other fields on top of Body field, you will find a CSV file with the same name, example: Attachment Export-02_13_2014-13_43_08.csv, all fields selected when you in  export in dataloader.io will be available in this CSV file.


Note: Dataloader.io is not Salesforce product, so your data actually will stay or pass through someone else database, please seek your IT policy.


Reference: Dealing With Attachments

Salesforce: How to report Service Cloud users?

For Salesforce customers using Service Cloud, the Service Cloud user feature license entitles users to additional Salesforce features, such as the Salesforce console.

To assign the license to a user:
1. From Setup, click Manage Users | Users.
2. Click Edit next to a user's name.
3. Select Service Cloud User.
4. Click Save.

But, until Winter '14 release, we cannot run report on which users has this license enabled. It is too difficult if have to check each users. The workaround is to use tool with SOQL, such as Data Loader.

The field on the user object which holds the "Service Cloud User" checkbox value is "USERPERMISSIONSSUPPORTUSER". Using the data loader, we can export a list of users with service cloud user checkbox enabled by adding a filter USERPERMISSIONSSUPPORTUSER=TRUE

Example:
Select Id, Username from User where USERPERMISSIONSSUPPORTUSER = True

This field does not show up when you are using the Data Export Services.  So tool with SOQL call is the only workaround as of now.


Reference:
Assigning the Service Cloud Feature License to Users
How to get a list of service cloud console users?

Idea:
Make the Service Cloud User field available for User List Views

Salesforce: Opportunity owner added to Opportunity Team

Recently, we notice that Opportunity owner added to Opportunity Team member by default. When an opportunity created, Opportunity Team will always have Opportunity owner with member role = Opportunity Owner

Not aware where this coming from, few rough guess:

1. Check 'Default Opportunity Team' in user profile
2. It is enabled by Salesforce by default when Salesforce have new release
3. External consultants / developer write trigger without our awareness

In every user detail, user can add default opportunity team 'Automatically add my default opportunity team to opportunities that I create or open opportunities that are transferred to me'. But, we do not see any value in 'Default Opportunity Team' for user owned the opportunity. Assumption (1) is not correct.

We test this in a pure new Developer edition, this thing is not happen, so assumption (2) is not correct, Salesforce do not do this.

Search and check for all triggers in Opportunity, but do not see auto add Opportunity owner to Opportunity Team member, assumption (3) maybe not correct, reason it maybe in other trigger.

After do some research, we find this document Enabling Opportunity Splits. Enabling Opportunity Splits changes existing opportunity records in your organization. 

• The opportunity owner is included on each opportunity team. Because the Opportunity Owner role is essential for splits, you can’t remove it.
• If you enable split types that total 100%, new opportunity split records include a default entry for the owner, who initially receives 100% of the split amount. (These additional records increase storage requirements for your organization.)

A script applies these changes after you enable Opportunity Splits. The script temporarily disables triggers, workflows, and validation rules for opportunity team records, mass reassignment of opportunity team members, and mass transfer of accounts and opportunities. If your organization contains a lot of opportunities, the script may take several hours. While it’s running, you can still perform other tasks in Salesforce. Automated email notifications keep you up to date on the script’s status.

So, this is clear, it is effect on enabling Opportunity Split, all existing opportunity will be populated with member role = Opportunity Owner.

Note: one user will only have one role of Opportunity Team for one Opportunity, if you add an existing user of Opportunity Team with a new role, it will overwrite existing role. 

Reference: Enabling Opportunity Splits

Salesforce: How to add or remove button in List View?

Have you ever wonder how to remove standard button in Salesforce, such as New button?

Or to remove other standard button such as 'Forward to Connection' button ?

Note: 'Forward to Connection' button is a Standard button if you implement Salesforce to Salesforce. 

Or you want to add your own custom button?

To do this actually is very simple, but since Salesforce has tons of features, we sometimes just forget this. In this blog, I'll use Contact as sample, but it is the same for all Standard and Custom objects. Here we go:

1. Go to Setup - Customize - Contacts - Search Layouts
2. Yeah, some of us maybe wondering why go to Search Layouts to add / remove button? But, that is how is arranged by Salesforce
3. Click Edit link next to Contacts List View 

Just check the checkbox to make Standard button visible and uncheck to hide it, while for custom button, add it to the right column to show it. Easy?

Next, how to create your own custom button?

1. Go to Setup - Customize - Contacts - Buttons, Links, and Actions
2. Click New Buttons or Link button
3. Select Display Type to List Button

You can create new button to pre-populate data in some fields, but I'll not discuss it in this blog.

Reference: Customizing Search Layouts

Salesforce: Formula for Owner

In Summer '13 release, Salesforce open Owner lookup field and made it available for cross-object formula. Owner field is special compare to other fields, because if the object is available for Queue (e.g. Case, Lead, and custom object), it can be linked to User object or to Group object (owner is a Queue).

You can access things like the record owner email address or owner manager name through formula field or workflow rule.

For example, if you need to get owner email address and it is not for queue, your formula would be Owner:User.Email, this formula field will return blank, if the object is available for Queue and record owned by a Queue.

From above sample, if the object supported by Queue, you can enhance the formula to:
IF( ISBLANK(Owner:User.Id), Owner:Queue.QueueEmail, Owner:User.Email )

To get User name or Queue name:
IF( ISBLANK(Owner:User.Id), Owner:Queue.Name, Owner:User.FirstName & " " & Owner:User.LastName )

User and Queue objects can be referenced on the Owner lookup, but must be explicitly selected, see screenshot below:

Reference: Cross-Object Owner Fields: A Powerful New Formula Option

Salesforce: How to change field Label created by Managed Packages?

When you installed a managed package app into your Salesforce instance, you cannot edit the label of the fields. If you click Edit button, both Field Label and Field Name is disabled.

So, how to change the label? Here we go:

1. Navigate to Setup - Translation Workbench - Override
2. Select the right Package, Language, Setup Component = Custom Field, Object, and Aspect = Field Label
3. It will show all custom fields for that object
4. Double click in column 'Field Label Overwrite'
5. Press TAB to move to next editable field or SHIFT-TAB to go to the previous editable field. 
6. Click Save button and done.

View in object page layout, before:

After:

With the same approach, we also can do for the following components:

• Action
• Apex Sharing Reason
• Button and Link Label
• Custom Field
• Custom Report Type
• Lookup Filter
• Record Type
• Validation Error Message
• Web Tab
• Workflow Task

Note: you do NOT need to enable Translation Settings to do this change.

Reference: 

• Entering Overrides to Translations in Managed Packages
• Component Behavior in Packages

Salesforce: Simple Test Method

This blog is continuation from previous blog about simple trigger. As I mentioned earlier, you need to write Test Method to deploy apex code (including trigger) from sandbox or developer instance to production instance. Remember all activities below only in Developer or Sandbox instance.

Let us create a simple test method related to trigger to act as workflow with field update blog written earlier. Without further ado:

You can right click the image above and select 'Open image in new tab' if you are using Google Chrome to get better image resolution or get the text version from this URL.

Let me give a quick explanation on the code above:

• We start with creation of record in reference object Iso_Country_Mapping__c and populate Country Code and Country Name
• Continue with Account creation, then update Billing Country of Account
• The first 2 test methods are Positive test case
• The last one for Negative test case, USA is not valid country, only US or United States

Once the code save successfully (Salesforce will check the code for any syntax error, it will not allow you to save if any error), you can click button 'Run Test' to execute the test method and see the result.

Now, go back to your the trigger and look for Code Coverage, if nothing wrong it should be updated, remember we need at least 75%, if you can achieve 100% is perfect. Code above cover for 91% as screenshot below  

The test method show here is very simple using single record of positive and negative test case, but in many scenarios, you can enhance with multiple records test case and testing as other users. 

Reference: Testing Example

Salesforce: Dated Exchange Rates versus Conversion Rate

Last month, we discussed on How to edit Dated Exchange Rates, today we'll share more on Dated Exchange Rates.

Remember, dated exchange rates are not currently used in:
- forecasting
- currency fields in other objects
- currency fields in other types of reports

Dated exchange rates are used for:
- opportunities
- opportunity products
- opportunity product schedules
- campaign opportunity fields
- opportunity splits, and reports related to these objects and fields.

See this reference and vote this in IdeaExchange for Multi-Currency support for All Currency Fields .

Advanced currency management allows you to manage dated exchange rates within opportunities using Salesforce. Dated exchange rates allow you to map a conversion rate to a specific date range. For example, the exchange rate on January 1 was 1 USD to 1.39 AUD, but on February 1, it changed to 1 USD to 1.42 AUD. Your opportunities that closed between January 1 and February 1 use the first exchange rate (1 = 1.39), while opportunities that closed after February 1 used the second exchange rate (1 = 1.42).

Dated exchange rates are defined using a start date and a conversion rate. Each rate is in effect until either the end of time or the day before the next start date for that currency. The time between on start date and the next start date is called the exchange rate date range. These ranges can be as small as a day and as large as all of time.

Today we get a real scenario when dated exchange rates is not align with conversion rate.

This is screenshot from Opportunity report:

and this from Forecast report:

From screenshot above, opportunity report show USD 48.04, while from forecast report USD 48.02, this is not much different, but when the numbers getting bigger, the difference will be bigger.

Let us analyse:
This is screenshot from dated exchange rate

This is screenshot from conversion rate

Let's calculate this using Ms Excel

So, this is clearly show that Conversion Rate is used in forecast, even you already used Dated Exchange Rate. It is best to keep Conversion Rate in sync with Dated Exchange Rate.


Advanced Currency Management Considerations:

• Dated exchange rates are used for opportunities, opportunity products, opportunity product schedules, campaign opportunity fields, opportunity splits, and reports related to these objects and fields. Dated exchange rates are not used in forecasting, currency fields in other objects, or currency fields in other types of reports.
• Organizations with advanced currency management support roll-up summary fields between two advanced currency management objects. For example, roll-up summary fields are supported from an opportunity line object to its opportunity object, because both are advanced currency management enabled. However, if you enable advanced currency management, you can’t create roll-up summary fields that calculate currency on the opportunity object rolling up to the account object, and you can’t filter on the opportunity currency field on the account object. All existing currency-related roll-up summary fields on the opportunity object are disabled and their values are no longer calculated. If your organization enables advanced currency management, you should delete any currency roll-up summary fields using opportunities and accounts or opportunities and custom objects.
• Campaign opportunity fields use dated exchange rates when calculating the amount in the campaign currency, but are not used when converting those amounts to the user currency.
• Cross-object formulas always use the static conversion rate for currency conversion.
• If advanced currency management is enabled, you can't bind Visualforce pages that use <apex:inputField> or <apex:outputField> components to currency fields that support advanced currency management.

Reference: About Advanced Currency Management

Salesforce: Simple Trigger to Act as Workflow with Field Update

In previous blog, I discuss about how to write a simple trigger to act as validation rule. In this blog, I'll continue to use trigger to act as workflow with field update.

Scenario: it is good that we manage user only able to enter Singapore or SG, so no more SGP or Spore for the Billing Country. But, even we have only Singapore and SG, it would be best we only have SG, this will make the report even easier and better.

We can enhance existing trigger and add few lines. I'll not write about custom object and how to start write trigger. Here you go:

You can right click the image above and select 'Open image in new tab' if you are using Google Chrome to get better image resolution or get the text version from this URL.

Let me give a quick explanation line added in the code:

• Line 3, add new list for Account object, will use it to update Account.
• Line 20 & 21 and 39 & 40, assign Country Code from reference table into Billing Country. You may ask why we need line 39 & 40 since user already enter 2 characters only, and it match to the Country Code. Because without that 2 lines, trigger will not update Account with Country Code from reference table, so when user enter sg or Sg, system will accept it, this is not so tidy as we want all be SG, get it? 
• Line 54 - 56 to update Account before insert or before update.

Salesforce: Simple Trigger to Act as Validation Rule

After active for more than 18 months in Salesforce Answer Success Community and answered for more than 5000 posts (currently stay at 5,271), I see many members asking for help or advise to fix validation rules, to make sure the validation rule will block invalid data based on some criteria.

Use Case: user only allowed to enter Billing Country in Account with US only or "US or Unites States" only, but not USA or U.S.A or U.S. or etc. I concur this is a good practice to standardize data entry, so user or admin can easily run a report and group them altogether with correct result.

But, what happen if you want to validate 200 countries name in the world? Using validation rule, it will be difficult to maintain. Furthermore, if the values need to be maintained by users, not by system admin, using validation rule is not possible.

Let me introduce a simple trigger to act as validation rule. The idea is using a custom object as reference table. I'll not discuss how to create custom object and assume you are familiar with it. See this screenshot:

User can maintain the values from page layout:

Extra Tips: You can have above View with object page layout without have to create new Tab (remember you have maximum Tab limitation, except for Unlimited or Performance edition). Just enter 3 letters key prefix of the object into URL, example: https://na3.salesforce.com/a0m

Change na3 with your Salesforce instance; a0m is the key prefix for this custom object.

You can use Developer Workbench to easily get the object prefix

Back to the trigger and follow steps below to create trigger:

- Go to Setup | Customize | Accounts | Triggers

- Click New button

- Copy and paste code below into editor

- Click Save button

You can right click the image above and select 'Open image in new tab' if you are using Google Chrome to get better image resolution or get the text version from this URL.

Let me give a quick explanation for code above:

• First, check if Billing Country is not blank, if blank than go through.
• Second, check if Billing Country is only 2 characters, if YES check Country Code, if No check Country Name.
• If not found, use addError() method to stop system to save and show error message.

Please note that you need to have Test Method apex class with at least 75% coverage of above trigger to enable it deploy to Salesforce Production instance.

From above quick explanation, it check both Country Code and Country Code based on length of Billing Country, using the same way, you can expand the code to check more things with more advance logic.

More samples here:
trigger DuplicateAccountNameCountryCheck1 on Account (before insert, before update)
{
    For(Account a:Trigger.New)
    {
        List<Account> acc=[SELECT Id, Name FROM Account WHERE Name=:a.Name AND BillingCountry=:a.BillingCountry];
        if(acc.size()>0)
        {
            a.addError('You are not allow to create duplicate Account Name with the same Country');
        } 
    }
}


trigger DuplicateAccountNameCountryCheck2 on Account (before insert, before update)
{
    For(Account a:Trigger.New)
    {
        Integer acc=[SELECT count() FROM Account WHERE Name=:a.Name AND BillingCountry=:a.BillingCountry];
        if( acc>0 )
        {
            a.addError('You are not allow to create duplicate Account Name with the same Country');
        } 
    }
}