Using Regular Expressions to standardize Vista data

Regular Expressions, or Regex, can be used to help validate and standardize data entry fields in Vista. If your business rules require upper case text for names and addresses or perhaps only allow certain characters in data fields. Regex can help keep things tidy. Regex has been around since the 1950’s and was designed for text processing on Unix systems. Vista can also leverage Regex to “process text” into a desired format.

This quick tip will show you how to access Regex in Vista.

• Access Vista's Field Properties from a field, in this case the Employee Last Name field, by pressing F3 or the Checkbox Icon.

• From the Field Properties window, select the desired level of validation (ie, allow save with a warning, or don’t allow saves at all).  Then enter an expression and an error message if the text entered does not match the expression.  This rule will only allow uppercase characters, periods, and spaces.  Click OK or Apply

• Test and Validate that undesired lowercase characters aren't accepted.

• Test and Validate that only uppercase, periods, and spaces were not accepted.

This very simple example will help enforce data formatting for users that conform to an established business rule.  Regular Expressions can also be used for very sophisticated situations such as Social Security Numbers. Here is a more sophisticated expression for validating social security data before saving in Vista.  (Google is your friend here).

A valid SSN (Social Security Number) must satisfy the following conditions: 

• It should have 9 digits.
• It should be divided into 3 parts by hyphen (-).
• The first part should have 3 digits and should not be 000, 666, or between 900 and 999.
• The second part should have 2 digits and it should be from 01 to 99.
• The third part should have 4 digits and it should be from 0001 to 9999.

And the Regex to enforce that rule:

• ^(?!666|000|9d{2})d{3}-(?!00)d{2}-(?!0{4})d{4}$

(Click here for the source website when I googled "regular expression social security")If you consume information by video, this 100 second YouTube video quickly explains regex. A search on Youtube for Regular Expression will yield many results.

While this may look very complicated, it become easy to understand with just a little bit of study.  Believe me, it will be easier to create a regular expression than it will be to train each employee on the proper way to enter data.

For more information, you can press F1 while in the Expression field to reach Vista's online help manual or click here to see the same help screen.  You may need to scroll down and find the section titled System Overrides: Expression

The online manual also has additional helpful links to learn more about regular expressions.

Regular Expressions are a great tool to help standardize your data formats so it can be more easily analyzed later.  Clean data is essential when analyzing it to determine if your business is meeting goals or to help find trends.

Feel free to contact us on our home page if you would like assistance or more information on regular expressions and ideas on how it can benefit you and your business data.