#validation rule

When you go to the supermarket, do you acquire everything that is in the store? Likely this is not the case, as with each trip, you take only what you need, and leave what you don’t. This methodology can also be used in the non-physical space with technology, and when it comes to Salesforce, a perfect use-case is with formula fields (and their derivatives such as Validations, Flows, etc.) If we take for example this sample field that produces the following output (made up of the Record Name and Product) , there may be a case where you would want to strip out part of this to use on its own, or to be used as a portion within another field.

Example Field Name: Object_Reference__c

Output: Company Name - Green Apples

Perhaps you would want to extract the Company Name only (or a portion of this), or instead utilize the whole (or portion) of the Product field. Two ways to accomplish this can be done via a Formula/Text field using the (i) TRIM or (ii) LEFT/RIGHT functions. Looking at this in more detail, should the requirement be to extract only the Company Name for example, this can be accomplished by using the following statement:

LEFT(Object_Reference__c, FIND("-",Object_Reference__c))

In using this as the formula field, the output will now be “Company Name “, as only the information to the left of the ‘dash’ separator (-) will be the result. If you take a closer look at  the specifics of the output, you’ll notice that there is a space to the right of ‘Name ‘. To eliminate this extra space so you're left with only the Company Name, just add the number of characters you wish to be removed. In this case, the following statement will produce the output as “Company Name” (without the space).

LEFT(Object_Reference__c, FIND("-",Object_Reference__c) -1)

If you wanted to instead extract the Product Name, then you would just need to  slightly modify your statement to use the RIGHT function, which would produce the value “Green Apples’. Like the prior statement, be sure to account for the spaces via the -1 suffix. You can also accomplish a similar formatting change using the TRIM function. In this example, let’s say you wanted to remove the first 4 characters from the Object_Reference__c field’s output, the following statement would return “any Name - Green Apples”.

TRIM(RIGHT(Object_Reference__c , (LEN( Object_Reference__c) - 4)))

For extracting the end of the statement, the ‘TRIM(LEFT’ option will remove a set limit of characters to the end of the statement. We would just need to replace ‘RIGHT’ with ‘LEFT’ on the prior statement, and set the total number of characters to the end of the statement. So, if we used ‘-7’ to the end of the formula, the result would be “Company Name - Green”. The TRIM function can also be used in many other ways in connection with other basic formula statements in producing automated values. One such use-case is in the proliferation of email formats. Let’s consider that your Company has standardized its email addresses by using the First Initial of the ‘First Name’ and the entire Last Name for the naming convention of the email address (before the ‘@’ symbol). In this case, you could write the following statement using the TRIM function to accomplish this:

TRIM(LEFT(First_Name__c,1)) & (Last_Name__c) & "@company.com"

These represent a few examples of ‘trimming the fat’ from your fields to use in other areas of your application(s) to further eliminate manual actions. There are many uses of the TRIM function for a myriad of situations when you need to replace  (or remove) spaces (i.e. Email), combine fields to form a specific string of text (Custom Field Definition) or extract only the characters needed for use in reports (i.e. first few characters of a Postal or Zip Code). While there may not currently be a requirement for this function, there will no doubt come a time when you will want to enhance your analytics or automate certain parts of your Salesforce applications for which the TRIM function can deliver the results you need... when you need it.

Creating validation rules in Salesforce can be done in a number of ways, such as using a formula or simple decision. The outcome is always the same, which is pinpointing the user of an impending error based on the information in the field not being completed correctly, such as a Phone Number, Email Address or a more custom rule involving system fields such as $Profile or $User. There are only 2 criteria to a validation rule… the input (condition) and the output (error message), and while the output is always the same format (plain text), there is an alternative to using formulas for the input rule. This is where a Regular Expression (or RegEx) option can be utilized, allowing you to opt for widely-adopted and tested snippets (or simple scripts) that can save you time as opposed to writing your own. Regular Expressions are represented by strings of text to match other strings, using a specific pattern, which is then compiled to represent the expression. Often used in Search / Replace functions, the same methods can also work very well for validation rules, and are fully supported by Apex, Salesforce’s native programming language. For those who have a working knowledge of Java or Perl, the integration of Regex should be a fairly straight-forward process. Salesforce uses the Java flavor of Regular Expressions, and while adopted as an accepted form of validating information within Salesforce, the history goes back to the early 1950’s where it was used for notating mathematical equations (then called ‘Regular Events’). For those who have not yet used Regular Expressions, it may seem a bit foreign, but once the fundamentals are understood, the advantages and convenience will become apparent.  To illustrate the use of a regular expression, consider the following example of a RegEx validation for a North American Phone Number:

NOT(REGEX(Phone__c, "\\D*?(\\d\\D*?){10}")) Let’s break this down to further highlight the syntax of this expression: - “\\D”: Matching of a non-digit (0-9) - “?”: Matching of the preceding statement 0 or 1 times - “\\d”: Matching a digit (0-9) - “*?”: Matching the preceding statement defining ‘zero’ (or more) times - “{10}”: The number within the curly-brackets equates to the number of times occurred

This expression will allow a phone number to be entered as “111-222-3333″ or without the dashes (1112223333) but if any variant presents itself that is out of this sequence, a resulting validation error will appear. Notice that RegEx variables are case-sensitive, as evident in the upper-case ‘D’ and lower-case ‘d’ which mean different things (in this case ‘non-digits’ vs. ‘digits’). Also of note is that when using Regular Expressions from other websites for use in your validation rules, the start / end characters are not required, whereas utilization of the expressions in some 3rd-party apps (such as DocuSign) will use the native Java-based nomenclature / formatting with the '^' as the beginning of the sequence and the '$' symbol to terminate the sequence. For a reference of RegEx definitions, check out Python.org’s repository. So, the next time you are confronted with a data validation conundrum, why not perform an online search for the expression you are looking for, and try it out in your sandbox? Odds are with the vast array of existing options, there is one that is tailor-made to your requirements, be it a Post / Zip Code, SIN/SSN Number, Password Strength or other variable. For additional ideas for commonly-used validations, review Digital Fortress’ site on the top 15 options for RegEx.