Text Validation using List Subtraction and an EnumList of Allowed Values

Text validation in AppSheet can be a little challenging. :weary:


There’s no RegEx, and you have to nest 26 SUBSTITUTE()'s just to validate for letters only. And you can nest more SUBSTITUTE()'s for numbers and special characters, but not apostrophes.

This sample app uses a different approach;

An EnumList table that stores the list of allowable characters, to be reused for validating multiple columns throughout the app-- or for generating lists for buttons and dropdowns.

Text Validation Sample App

The [List] is SPLIT() into a real list, and then subtracted from the user input-- which has been converted to a list of single characters.

Screen Shot 2021-01-23 at 5.39.13 PM

This gets around the editor limitation of typing apostrophes in formulas, and cleans up the expressions by referencing the list, instead of listing every item directly in the expression.

7 Likes
  • to remove a single quote:
    substitute([YourText], left("' ", 1), "")

  • to remove a double quote:
    substitute([YourText], left('" ', 1), "")


Love the approach - just proves the adage that “in AppSheet there’s always another away”

4 Likes

Take out the space, and suddenly the same expression is invalid! It’s like one editor bug being used to work around another! :upside_down_face:

Can we keep this bug, please? :rofl:

Screen Shot 2021-01-23 at 6.38.15 PM

2 Likes

The reason it works is because app sheet interprets the contents between two double quotes as a string… Even if it includes a single quote (ecause many times in a long text you’ll have a single quote as an apostrophe).

So when you include the single quote with a space, app sheet then interprets it as a string; but when you don’t have anything but the single quote, then the string is an unterminated single quote - which we all know doesn’t work.

Best
Matt

4 Likes

As usual, an excellent tip @GreenFlux . Thank you. Very nicely documented and the sample app is very useful.

Just to throw in another flavor, I believe for simpler validation such as single word pure alphanumeric text, we may leverage the power of EXTRACTHASHTAGS() or EXTRACTEMAILS() functions. These functions appear to be invalidating most special characters.

So a column requiring a single string word [TextColumn] for example could be validated to eliminate special characters and allow only alphanumeric characters with valid_if expression of

The above is inspired by the following posts and tips by @Steve

4 Likes

Would love to see EXTRACTWORDS() that pulls out strings matching:

\b\w+\b
4 Likes