AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Consulting
  • Courses
  • Register

Acumatica Input Validation and Regular Expressions

June 26, 2014 by Tim Rodman

Acumatica allows you to attach input validation preferences to fields. This is very useful when you can’t use a drop-down list field for your scenario so you need a text field, but you want to put some amount of control on the text field. Good data entry is very important in an ERP system. The idea is that you can use the data in your ERP system to make critical business decisions. However, if the data isn’t very clean, you might waste a lot of time cleaning the data before it can be usable. Input validation helps to keep your data clean as it is getting entered into the system.

This kind of thing is probably best demonstrated by example.

Example #1 – Postal Code Mask

Let’s take a United States postal code, for example. Acumatica will allow you to restrict what a user enters into the postal code field. You can do this in the Countries/States (CS204000) screen like this:
Acumatica Postal Code Mask

The above setup will allow a user to enter up to five numbers in a postal code field, but nothing else. If you try to type a letter, the field won’t allow it. If you try to type more than five numbers, the field won’t allow it. It will, however, allow you to type four numbers or three numbers or two numbers or one number.

Example #2 – Postal Code Regular Expressions

In the United States there are actually two valid postal code formats: one with only five digits and one with an extra four digits:

  • 91941
  • 91941-8018

Our example #1 postal code mask wouldn’t allow the 91941-8018 postal code to be entered. What do we do? We’re going to need something called a regular expression. Regular expressions are not unique to Acumatica. They have been around since the 1950s and they are very useful for data input validation. The downside of using regular expressions in Acumatica is that they only validate when you click the save button whereas an input mask actually prevents a user from entering an invalid character. So, if we only wanted to allow users to enter a 91941 format postal code, we probably shouldn’t use a regular expression. However, sometimes you need a regular expression because they are more flexible than an input mask.

Even though we probably wouldn’t do this in the real world, let’s look at what example #1 would look like as a regular expression:
Acumatica Postal Code Validation Regular Expression

What does it mean?

  • The ^ marks the start of the phrase which means what follows can’t occur anywhere, it must occur at the beginning of the phrase.
  • [0-9] means that a user can enter any number, but it must be a number (no letters or other characters).
  • The {5} means that the the preceding phrase (a number in this case) must be entered five times.
  • The $ marks the end of the phrase which tells the field that a user can’t enter anything after the five numbers.

The ^ and $ are important because, without them, a user would be allowed to enter any one of these:

  • bla91941blah
  • 91941blah
  • blah91941

Actually, I just realized that the regular expression version of example 1 has the advantage in that it requires all 5 numbers to be entered. It won’t allow you to enter less than 5 numbers.

If we want to allow both 91941 and 91941-8018, we’ll need a regular expression that looks a little more complicated like this:
Acumatica Postal Code Validation Regular Expression

The above is the same as our first example except for the extra (-[0-9]{4})?. Let’s break it down:

  • The ( and ) are matching a phrase.
  • The ? says that the phrase can occur zero or one times.
  • The dash is a separator that we want someone to enter if they decide to enter the extra four digits.
  • The [0-9]{4} is basically the same as our first example except now we are only allowing four digits instead of five.

Example #3 – Part Number Validation

Now for one final example. The company that I work for does a lot of engineering. When they engineer something, it becomes a part number in our system. The trouble is that we have a 60 year history that consisted of several company mergers. The result is that our part numbers could be in a variety of different formats. It would be nice to validate the part numbers, but allow a user to enter a part if it matches any of the different valid formats.

Note: As far as I can tell, Acumatica can’t do this yet. You can apply regular expressions to a number of different fields, but a part number (which Acumatica calls “Stock Item”) isn’t one of them. You can’t do it because a “Stock Item” is defined in the Segmented Keys (CS202000) screen and you can’t yet put regular expression validation on segmented keys (as far as I can tell). Hopefully they can add this in the future. I’ll continue though, because it’s a good example.

Let’s say the valid part numbers are of three possible formats:

  • A12345 (“A”, followed by 5 numbers)
  • 01234567 (“0”, followed by 7 numbers)
  • B12345601 (“B”, followed by 8 numbers where the last two numbers must be a 0 or a 1)

You can use the vertical line key on your keyboard  |  to mean “or” in a regular expression. This allows me to make a regular expression that would check if a part number matches any of the three valid formats. Let’s build the regular expression for each valid format separately first. I won’t bother to explain these since they are very similar to Example 2:

  • ^A[0-9]{5}$
  • ^0[0-9]{7}$
  • ^B[0-9]{6}[0-1]{2}$

Now, all we have to do is put them together into one giant regular expression and separate them with the  |  character like this:

  • ^A[0-9]{5}|0[0-9]{7}|B[0-9]{6}[0-1]{2}$

I know it looks ugly, but it’s pretty powerful. We’re only scratching the surface, but this post is already pretty long and getting really boring.

Hopefully this helps to demonstrate the power of regular expressions and how useful they can be. It’s great that Acumatica includes them as part of its data input validation strategy.

If you actually made it to the bottom of this post without being scared off and would like to learn more about regular expressions, there is a lot of information out there. For the ultra nerdy who like to lay in a hammock on a warm Summer afternoon and read about this stuff, there are even entire books devoted to the topic. Here are a couple to start:
Introducing Regular Expressions

Mastering Regular Expressions

Filed Under: Acumatica Learning Tagged With: Acumatica, Acumatica Blog, Acumatica Data Entry, Acumatica Input Validation, Acumatica Regular Expressions, Acumatica Training

By using this website, you agree to our Terms of Use (click here)

Online Members

Recent Blog Posts

  • EP 38: From Welder to Consultant, Nick Savage-Mady on his journey with Acumatica and MYOB Advanced (Podcast) January 21, 2021
  • Acumatica Reporting Tools Consulting January 19, 2021
  • EP 37: Jody Lorincz, IT Manager at MHR Brands, sharing his Acumatica Customer Story (Podcast) January 13, 2021
  • EP 36: What Motivates ERP Veteran Nicole Ronchetti To Participate in Acu-Connect (Podcast) January 8, 2021
  • Acumatica and Scanco WMS – A Cautionary Tale for ISVs January 6, 2021
Acumatica Learning Resources

Recent Forum Posts

  • RE: Popup screen fields not being auto populated

    Are you clicking the SEND button in both scenarios or t...

    By Tim Rodman, 5 hours ago

  • Legal Name?

    I am trying to get the Legal name of a company listed i...

    By Chip Fritsch, 6 hours ago

  • RE: Kensium Shopify versus Acumatica Shopify

    @garenmardirossian I have not but I am interested in hi...

    By Carl Brooks, 23 hours ago

  • RE: Kensium Shopify versus Acumatica Shopify

    Have you also seen Biz-Tech Services Shopify Connector?

    By Gary Mardirossian, 1 day ago

  • RE: ACH postings in Acumatica

    ACH payments are better managed via the batch payments ...

    By Michael.Barker, 1 day ago

  • RE: Adding bill description on check stub

    @jswartz Yes, that does make sense, and yes without tho...

    By Chip Fritsch, 1 day ago

  • RE: Adding bill description on check stub

    @cfritsch Chip thank you for that. I did stumble upon t...

    By Jswartz, 1 day ago

  • RE: Create payment

    Hi @cndr, I think you’ll have better luck with this p...

    By Tim Rodman, 1 day ago

  • RE: Changing how fields are displayed on mobile app

    So I was able to figure this out in case we are not the...

    By jvandonkersgoed, 2 days ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.