+ Start a Discussion

Cool new validation rule features in Spring '08 release

In case you haven't heard, we've added some cool new functions in the Spring '08 release to help you write more powerful validation rules.
  • VLOOKUP Function: Similar to its Excel counterpart, the VLOOKUP function lets you use data in another object as a basis for validation.  For example, to validate the zip code in a contact address, you could use the VLOOKUP function in a contact validation rule to search for a match in a custom object containing a list of valid zip codes.
  • ISNUMBER Function: The ISNUMBER function provides a simple way to tell if data entered in a text field only contains numbers, which is required for many types of identification numbers like credit cards and account numbers. 
  • REGEX Function: Regular expressions are a powerful tool used by programmers to find patterns within bodies of text.  The REGEX function gives you access to regular expressions in your validation rules.  This may be useful to validate complex number formats, like IP addresses, country-specific postal codes or phone numbers, tax IDs, and so on.
Check out the Advanced Validation Rules page on successforce for more information.
For your reference, here are links to the function documentation in the online help.
VLOOKUP function
ISNUMBER function
REGEX function

Eric Bezar
Force.com product management

Jeff TalbotJeff Talbot
I think the new VLOOKUP function will offer some great functionality for data validation, but it should be named something else. VLOOKUP is used in Excel to GET data from another table. As described, VLOOKUP in SF can only be used to VALIDATE data. You have described VLOOKUP for SF as "similar to its Excel counterpart" -- but until we can use it in Workflow field updates (i.e. auto-populate the city/state from a zip entry), that's not a very accurate description. Thank you for the feature though!
Actually, our VLOOKUP function does let you get data from another object.  The catch is that we only allow the VLOOKUP function to be used in validation rules in the Spring '08 release.  We are planning to allow VLOOKUP in more places (workflow field updates, formula fields, etc.) in a future release.

In the meantime, think of the "V" in VLOOKUP as standing for "Validation".  :smileywink:

I too was planning to use the vlookup functionality in Spring '08 to get data, instead of just validate data.  Glad to hear that this is at least on the roadmap.
Thanks for adding these three new features to validation rules.  Cant wait to see more happen to salesforce.com in the upcoming releases. 
Is there anything else in the meantime that I can use to get data from another object just like I would be able to in a VLOOKUP in the future?  I am trying to get quarterly amounts based on start date for a streamline report and haven't found anything in Salesforce to do this with the exception of case or if statements which may be a little ugly.