Validating Data Entry by Using Regular Expressions in Acumatica

We all strive for good data entry in any ERP system.  An important aspect is that we make critical business decisions off this data, which means we need reliable, clean data.  Input validation helps to keep your data clean as it is getting keyed into the system versus having to waste time by manually cleaning it.  Acumatica can help in improving the quality of the data as it is being entered in to the system.

Here’s a tip on how to ensure good data entry in Acumatica

When setting up a custom attribute, there is no choice for a number control type in Acumatica. By default, a user can type in any character (letter or number), even when we only want to use numbers in that field.

To validate that only numbers are being entered to a text field, you need to set up a Regular Expression in the Reg. Exp. Field.

Regular Expressions

Regular expressions are a generalized way to match patterns with character sequences. Here are two examples to meet our number requirement.

Regular Expression to validate decimal numbers ^(\d*\.)?\d+$
Regular Expression to validate integer numbers ^[0-9]*$

The first one will ensure that the user enters a valid decimal number for a Weight field.

If the user enters in a character that is not a number, Acumatica has an error message that will display when they move out of the field.

Examples below will be valid entries in this field:

  • 1
  • 23425
  • 234
  • .6454
  • 45

Note:  Because the control type is text, the number will be stored in the database as a string, so if you are going to use the value in formulas, you will need to use a conversion function (i.e. CDec). 

Other Validation Rules

If you need other validation rules, you can find a lot of Regular Expressions on Google.  There are a variety of sites that provide examples, such as Phone Numbers, Zip Codes, and more.

Here’s one I find helpful.  It checks for valid web site URLs:

^(http:\/\/www\.|https:\/\/www\.|http:\/\/|https:\/\/)?[a-z0-9]+([\-\.]{1}[a-z0-9]+)*\.[a-z]{2,5}(:[0-9]{1,5})?(\/.*)?$

For example, this entry is valid: https://www.google.com, but this, htt://www.google.com will be rejected.

Don’t miss out on other Acumatica tricks and tips.  Subscribe to our blog.