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:
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:
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:
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:
