Text Validation using List Subtraction and an EnumList of Allowed Values

[GreenFlux (GreenFluxLLC)]

Text validation in AppSheet can be a little challenging.

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.

AppSheet Text Validation Sample App

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.

[MultiTech_Visions]

@GreenFlux

And you can nest more SUBSTITUTE()'s for numbers and special characters, but not apostrophes.

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

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

AppSheet Expression Library

---

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

[GreenFlux (GreenFluxLLC)]

@MultiTech_Visions

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

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

Can we keep this bug, please?

[MultiTech_Visions]

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

[Suvrutt_Gurjar]

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

Checking if entered value in EnumList is email

I have an EnumList that suggests email addresses as it’s value taken from another column. The EnumList allows other values, too. Is there some way to check the user’s entry for email address? In my “Valid If” I have taken the following snippet from User Email authentication error: However, if a “correct” (containing “@” and “.”) email address is already selected, the check for the individual entry will fail. Screenshot of EnumList Type Details: [image] My Valid If expression: [image]

Illegal characters allowed in email field?

Yep, I’d call that a bug. Will escalate internally. A work-around is to use this in the column’s Valid If expression: ISBLANK(EXTRACTEMAILS([_THIS]) - LIST([_THIS]))

[Steve (Steve Coile)]

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

\b\w+\b