VLOOKUP

The VLOOKUP function is similar to the Excel function with the same name, and it is used to look up a value inside a specific object’s record set.

Let’s say we have a custom object that handles all country calling codes.

The object is quite simple:

• Object’s name: Call_Prefix__c
• Country’s full name: Country_Name__c (custom text field)
• Country’s calling code: Name (standard name field)

We want users to manually input the country calling code when creating a new Contact (where we are creating the new validation rule) on the Contact.Phone_Country_Code__c custom field and check that Contact.MailingCountry is equal to the country name found on the Call_Prefix__c object with the same code (on its Name field).

Using VLOOKUP, we can do this easily:

VLOOKUP($ObjectType.Call_Prefix__c.Fields.Country_Name__c,

  $ObjectType.Call_Prefix__c.Fields.Name,

  Phone_Country_Code__c) <> MailingCountry

That is, if the country name related to the phone country code differs from the Mailing Country, an error is triggered.

The first parameter referenced is the field that should be returned, the second parameter is the field to be looked up, and the third parameter is the value to check against (which comes from the validation rule’s object). The function returns a value (if more values are found, it returns the first one) that can then be checked against other fields (in our scenario, the Contact.MailingCountry field).

We can only use custom objects and the lookup field; the returning field must match their type, and the lookup field must be the Record Name field of the custom object.

The $ObjectType.Object_API_Name.Fields.Field_API_Name notation is used to reference object fields with no chance of making mistakes (the formula engine will compile against this expression and give an error when saving the formula if something is wrong).

When building formulas, remember that the total number of allowed characters is 3,900, including spaces, line breaks/return characters, and comments. You can chain more formulas (that is, call a child formula within another formula), but you cannot exceed the 5,000 bytes compile size (which includes all children formulas (this limit differs from the characters’ size and, if exceeded, the administrator will be notified when saving the rule).

REGEX

The REGEX function is used to trigger special checks on the format of a text value, using regular expressions. Discussing regular expressions is outside the scope of this book (refer to https://www.regular-expressions.info/quickstart.html for a quick start on the grammar of regular expressions), but let’s summarize them by saying that you can use a sequence of characters to define a rule that a string should match.

A basic regular expression could be the format of an email address, which can be (simply) written as follows:

[a-zA-Z0-9._%-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,6}

This matches the example email address, [email protected] (letters, numbers and some special characters before the @ sign, letters, and numbers, and some special characters before the last . (dot), and finally only letters for two to six characters).

Email address validation can be way more complex depending on your needs, but regex should cover the majority of scenarios.

Let’s say we have the need to validate a European VAT code in the Account.VAT_Code__c custom field. We don’t need what this code represents, but we need to know its structure.

We have been told that we should only validate Italian codes and that the regular expression of this kind of VAT code is (IT)?[0-9]{11} (starts with IT and ends with 11 digits).

The final regular expression will be the following:

AND(

  LEFT(VAT_Code__c, 2) = ‘IT’,

  NOT( REGEX(VAT_Code__c, “(IT)?[0-9]{11}” ) )

)

If the VAT_Code__c field starts with IT (the first 2 characters on the left-hand side) and it doesn’t match the regex provided, an error should be triggered. This way, if we input another kind of VAT code, (a German one), no error will be thrown.

You are generally not required to compile the regular expressions, but it’s likely you’ll be given regex to insert a validation rule using the REGEX formula.