Text Validation using List Subtraction and an EnumList of Allowed Values

GreenFlux
Participant V

Text validation in AppSheet can be a little challenging.

3X_6_1_61783bc59d9b38ccb919ae78cdd3943223433d14.gif
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.

3X_2_2_22b224cb9bae9c23a81a55daec7778cf6a8683a4.png

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.

8 8 1,323
8 REPLIES 8

MultiTech
Participant V
  • 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”

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?

3X_d_d_dd7dbe6af0fdf072fe25317c02d5d0ed795f1fd8.png

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

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

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

\b\w+\b

Just a small addition/ extension to this thread based on previous useful discussions and guidance in this thread.

The following expression in valid_if of a text column will allow only alphanumeric characters in a text string as well. Needs more rigorous testing but should do the job.

Constructed the above expression, while responding to the following question

Thank you @Suvrutt_Gurjar may you please explain the structure of this expression, particularly the use of EXTRACTHASHTAGS()?

Hi @Fabian ,

Good that you posted the query. While responding , realized need to exchange between left side and right side of the expression because of the AppSheet quirk of evaluating blank as true.

The revised suggested expression is as below

The basic construction is SUBSTITUTE() removes any spaces in the string and constructs one continuous text for EXTARCTHASHTAGS() function to process. A “#” is added on both sides because EXTARCTHASHTAGS() processes text preceded by “#” . On the right side, it is required for comparison to go through.

Now testing shows EXTARCTHASHTAGS() does not process any string or half processes the string that has special characters and this RHS and LHS comparison fails for strings having special characters, so we can use it for validating special characters.

The two NOT (CONTAINS() …) arguments are added because testing showed the EXTRACTHASHTAGS() does not validate ("-" and “_”) so the expression fails on these two and conditions need to be separately added.

The below screenshot shows the testing done. The LHS and RHS of the expression was assigned to two different columns to show how EXTRACTHASHTAGS() processes various strings on LHS. RHS is basically same text just appended with “#” for comparison. The validation is the result with the entire expression and the right =most column mentiones why the expression passed or failed.

Hope this helps.

Top Labels in this Space