I’ve recently written a couple Salesforce validation rules that I wanted to document and share for those trying to do something similar. First, let’s start with how to create a validation rule in Salesforce.
It is actually very easy to create a validation rule. Here are the steps…
- Click on Your Name -> Setup
- Under App Setup, click Customize
- Select at what level you want to apply a validation rule: Lead, Contact, Opportunity, etc
- Click Validation Rules
- Click New
Then to create a validation rule, you need to write a formula that if it does not validate to true, will return an error message that will then require the sales person to complete an action in order to make your rule validate to true.
In this first example, we wanted to start collecting data on opportunities that were older than 100 days. Why were the deals slipping? Budget? Infrastructure delays? Still testing our product? Just not responding? To do this, we decided to create a new field called “Deal Slip Reason”. If the “Deal Slip Reason” was blank and a sales person tried to edit/save an opportunity that had a “Close Date” of greater than 100 days after the “Create Date”, we would display a message alerting the sales person to complete the “Deal Slip Reason” field.
Here’s the validation rule that we used:
CloseDate – DATEVALUE(CreatedDate) > 100,
ISBLANK(TEXT( Deal_Slip_Reason__c ))
Another piece of data that we wanted to collect was the “Demo VDIO Phase” of any prospective leads that were being converted to a contact/opportunity . This meant that we needed to first validate that the “Partner” field was either blank or “N/A” as we didn’t want to ask this question if reseller partners or strategic partners were being converted this this demo phase is irrelevant to them. Next, we only wanted to apply this rule to those that were being converted and the “Demo VDI Phase” field was blank. So, here’s the complete rule we used:
OR (ISBLANK(TEXT(Partner__c)) && IsConverted && ISBLANK(TEXT(Demo_VDI_Phase__c)), TEXT(Partner__c) = “N/A”) && IsConverted && ISBLANK(TEXT(Demo_VDI_Phase__c))
Hopefully these examples of a couple of the Salesforce validation rules that I have written will help you to write some of yours. As I write more rules, I will be sure to update this post.
I like to post useful scripts that I write/modify/find online that I use in my everyday work. This is an Microsoft Excel macro that compares two lists of data and deletes duplicate items. I mainly us this Excel macro when I am compiling email lists for campaigns. A lot of times I have to creates lists with criteria like “Send to all unconverted leads in Salesforce, but exclude those who received emails from us in our prior two campaigns.” Querying the original list is easy, but a lot of times it is difficult to query any exceptions that are needed. Most times it is just easier to grab the lists that I know i want to exclude and compare my two lists and delete duplicate items. In this post I will include the Excel macro that you should use as well as give you a little background on how it works.
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through (list that will be deleted).
iListCount = Sheets("Sheet1").Range("A1:A10").Rows.Count
' Loop through the "master" list.
For Each x In Sheets("Sheet2").Range("A1:A3")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
Application.ScreenUpdating = True
The script is very simple to use. Just open up Microsoft Excel and put your “Master” list in the first column of Sheet1. Then put the values that you want to delete from your “Master” list in the first column of Sheet2. Next modify the ranges of each of your lists (to make this easy I have highlighted these values in red). Then simply go to “View” -> “Macros” -> “Edit” -> and copy and paste the macro script. To run the macro, go to “Run” -> “Run Sub/UserForm” or just click the green run button. That’s is! It is that simple…
As you guys know I am all about working efficiently and using automation in my day-to-day tasks. Anything that is slightly repetitive or tedious I like to automate. You should make the iMacros FireFox plugin your best friend when automating tasks within your browser. iMacros is an AWESOME plugin and the best part is that it is free. All you have to do is install the plugin and then simply record your actions within your browser. Then to automate that process the next time you simply play back those tasks.
You are probably thinking that in your day-to-day activities that you don’t have many repetitive tasks, and I thought the same thing when I first discovered iMacros. But after learning different ways to use the iMacros plugin and being a little creative, I have been able to discover several tasks that I can save several hours each day with iMacros. iMacros has a great wiki and by spending a little time learning and experimenting I have been able to learn how to run loops from Microsoft Excel files and also to execute copy and pastes by saving what I copy as a variable and then using the variable later in the iMacro script. When you start to get your feet wet with iMacros you really start to see how powerful this plugin really is.
Your probably thinking that from my enthusiasm for iMacros that I am going to insert an affiliate link at the end of this blog post, but it is completely free and I have nothing to gain. I just think that iMacros should be part of everyone’s arsenal of tools when working online.