Multiple Restricted Values

[dreamerdan]

In Appsheet's DATA VALIDITY (Valid If) expression, is there a way to put a restriction on multiple words (needle) from a row (haystack) that is of type "text"? I know the following works:

NOT(CONTAINS([Comment],"value"))

However, is there an expression that would allow for the restriction of more than just one value? Like: "value1", "value2", "value3", "etc..." So, if the comment contains any of those values it would flag an "invalid value error".

The following works with Contains(), but it doesn't with NOT(Contains())

OR(CONTAINS([Comment], “value1” ),CONTAINS([Comment], “value2”),CONTAINS([Comment],“value3”))

Appreciate any feedback.

[WillowMobileSys · Accepted solution]

Actually,  it should work as :

NOT( OR(
        CONTAINS([Comment], “value1”),
        CONTAINS([Comment], “value2”),
        CONTAINS([Comment],“value3”)
      )
)

However, I can imagine that you will want to adjust this "not accepted" word list over time and it would be nice to do it without needing to re-deploy the app.

To make the management easier, I would place these "not accepted" words in their own table.  Additionally, to make checking for the words easier, I would not allow punctuation in the text or at least limit it.

Then you can create an expression like this:

COUNT(INTERSECT(SPLIT([Comment], " "),  Not Accepted[Word])) = 0

If you do need to allow for some punctuation, then you will want to remove them from the text before splitting it by spaces.

I hope this helps!

[dreamerdan]

I like the separate table idea because it also allows for more values. The first expression seems to limit it to 3 values only.

I also see your point about the punctuation, as each value has to stand alone. But is there a way to limit punctuation in the comment? It doesn't appear that adding them to the list of values will solve the problem. Also is there a wildcat way, like an (*), of adding to a value that would work? For example *gmail.com would restrict an entire email ending in "gmail.com".  Thanks!    

[dreamerdan]

Actually, this seems to work well without limits on the number of values or punctuation:

NOT(OR(CONTAINS([Comment], "value"), contains([Comment],"value2"), contains([Comment],"value3"), contains([Comment],"value4"), contains([Comment],"value5")))

Not sure why before it seemed to stop at 3 values. Maybe I had a syntax error. 
 
Now it would be nice to use this with a separate table for the values as you suggested. Plz let me know if anything comes to mind. 🙏