Salesforce VLOOKUP Validation Example

VLOOKUP Validation Example

As a Salesforce ISV partner I get leads dropped into my Salesforce org whenever someone on the AppExchange installs one of my applications. That automated process also creates a license record, which is a custom object (sfLma__License__c) that was installed via Salesforce's License Management Application. Anyway, the fact that Salesforce does this for my ISV is great but the only downside to this is that the standard workflow rule and email notification functionality does not fire when license records are updated or created via this AppExchange integration.

As a workaround to this limitation I built a trigger that would automate the sending of email messages to those people that install a trial version of one of my applications whenever a license record is created or updated in my org. At the time this worked great because it freed me up from having to monitor new trial licenses and manually sending emails to those that installed the application about the pricing, trial expiration, how to purchase, where to get help, etc.

Over time I would change the messaging that I wanted to go out based upon the software that was being tried out or I would have to update email messages based on software changes or product offerings. Any of these changes would always require that I make the necessary changes to my code in a developer org or sandbox and then deploy the changes using Eclipse, Ant or Change Sets. This was fine for a while but still more manual work than I really wanted to do for something as seemingly simple as altering a message to a prospective buyer.

I still need to have code running in the background in order to identify Leads that come into my org and Licenses that get created for each Lead but I wanted to simplify the way that I pick and choose the email templates that go out and I didn't want to have to alter any code anymore.

So I built a custom object (AutoResponder__c) that would allow me to link a specific email template to certain actions that were happening on both License and Lead records. The key to making this whole thing work was ensuring that I had one and only one AutoResponder record for any particular email interaction that I wanted to take place. For example, I want to make sure that I have one AutoResponder defined for new installations of my Birthday Reminder application.

At first glance, I was under the impression that I would have to write yet another trigger in order to prevent duplicate AutoResponder entries. However, I found that this is a great situation to use the Salesforce VLOOKUP function.

Now that you have all of the backstory I can get to the point of this post, which is how to use the VLOOKUP function in a validation rule so that you prevent creation of duplicate records on a custom object.

The actual formula defined for the validation rule is below:

AND( 
	OR( 
		ISNEW(), 
		ISCHANGED( Name )
	), 
	NOT( 
		ISBLANK ( 
			VLOOKUP( $ObjectType.AutoResponder__c.Fields.Name, $ObjectType.AutoResponder__c.Fields.Name,  Name )
		)
	)
)

The formula is loosely translated as follows:

• If a record is created or edited and the Name of the record has been altered
• Check to see if there is another record in the table with the same Name
• If so, display an error message

I thought that this practical use case would be a good read for other Salesforce administrators.

Thanks for reading.