We are going to replace Match() function in TeamDesk formulas with pattern validation for text and phone columns.

Mainly, the reason for the change is performance.  Match() is slow, that’s why its usage is limited to validation rules only. But even with limited use, we have to execute on database side and in worst case it can slow down other data processing requests. But we can not just drop functionality in use without providing you with an alternative.

 

These are all bad news for today.

Now, to good news. After analysis of Match() function’s usage we found out that mostly it is used alone using the scheme:

Filter: not Match([column], “regular expression”)
Location: column
Message: bad format.

Moreover, in most cases Match() is used to check for simple fixed-length, fixed-format text.

So, as a replacement here comes…

Pattern validation for text columns

Here are new options:

Pattern validation for text columns

Instead of relying on non-trivial regular expression language, we now allow simple “pattern-by-example”, with only a few characters having special meaning:

Character Meaning
0 or # Any digit: 0 to 9
A Uppercase latin letter: A, B… to Z
a Lowercase latin letter a, b to z
Z or z Any latin letter
? Latin letter or digit
* Any character
\ Suppresses any special meaning of the character that follows, e.g. \* means “star”, not “any character”
any other Treated literally

If user input does not match the pattern, the message specified in Message property is displayed.

Here are couple of samples:

  • 0000 0000 0000 0000 – credit card number (4 groups by 4 digits)
  • (000) 000-0000 – US phone number
  • 000-00-0000 – SSN
  • 00000 – Five-digit postal code
  • A0A 0A0 – Canadian postal code
  • ?????-?????-?????-?????-????? – 25 characters product key (5 groups by 5 letters or digits)

Also text input was modified to let it handle format on the fly. When focus leaves input field and the input’s value does not contain invalid characters, we reformat it according to the pattern: remove extra spaces, insert required punctuation, convert lowercase letters to uppercase and vice versa. For example, following Canadian postal code pattern, k1 a0 b1 transforms to K1A 0B1

If simple pattern is not enough you can still use regular expressions. Start pattern with regex: and put expression afterwards, e.g. regex:\d{3,5} means 3 to 5 digits are allowed.

New phone column’s options

Phone columns are also extended with the same pattern validation options. In addition we’ve added few more.

Now we allow phone formatting – using the same Google’s phone formatting library that powers Android-based phones.

Pattern validation and other phone formatting options

None turns off validation and formatting.
On View keeps user’s input as-is, formats phone on the fly prior to display.
On Edit attempts to validate and format user’s input prior to saving it to the database.
By Pattern uses “pattern-by-example” validation.

Also, in phone formulas Format Phone option is a checkbox. Formulas either do nothing (Format Phone unchecked) or format phones on the fly (checked).

Enjoy!

Author
Kirill Bondar
Date
Share