How to add additional options to CONTAINS

WillA108
Participant IV

Hello!
I am trying to figure out how to add more than one option for CONTAINS.
Here is what I currently have, I definitely have a very low understanding of the logic for formulas:
CONTAINS([Day Notes], “crock” ) CONTAINS([Day Notes], “usb”) CONTAINS ([Day Notes], “trade”)

It doesn’t need to contain all phrases, just one or the other or multiple. I’m using this for a format rule.

Thanks in advance

Solved Solved
0 17 263
1 ACCEPTED SOLUTION

Lynn
Participant V

@WillA108
Do you want
OR(CONTAINS([Day Notes], “crock” ),([Day Notes] = “usb”), ([Day Notes]= “cooler”))

or this

OR(CONTAINS([Day Notes], “crock” ),CONTAINS([Day Notes], “usb”),CONTAINS([Day Notes],“cooler”))

NOT TESTED
I removed the extra parenthisis before the second CONTAINS and a space. Does that still work?

View solution in original post

17 REPLIES 17

Lynn
Participant V

Hi @WillA108
Check out OR()

Steve
Participant V

For reference:

Yah I took a look at this, I still can’t figure out how to combine OR with CONTAINS.
Can you please give an example of how to give multiple options in a CONTAINS expression?
Thanks ❤️

I’ve tried CONTAINS([Day Notes], “crock” ) OR (“usb”) OR (“cooler”)
and i have tried CONTAINS([Day Notes], “crock” ) OR CONTAINS([Day Notes], “usb")
Also CONTAINS([Day Notes], “crock” ) OR ([Day Notes], “usb”)
and also CONTAINS([Day Notes], “crock” OR “usb” OR “cooler”)

Lynn
Participant V

@WillA108
OR(([Color] = "Red"), ([Color] = "Yellow"), ([Color] = "Green")) : TRUE if the Color column value is any of Red , Yellow , or Green . Equivalent to IN([Color], {"Red" "Yellow", "Green"}) . See also IN() .

I appreciate the responses but this is really all greek to me, as it doesn’t show me how to combine it with CONTAINS.
For example I tried CONTAINS([Day Notes], “crock” )OR([Day Notes] = “usb”), ([Day Notes]= “cooler”)) and it will only light up daynotes which contain “crock” still when I made the formatting to turn the sentence green.

WillA108
Participant IV

Lynn
Participant V

@WillA108
Do you want
OR(CONTAINS([Day Notes], “crock” ),([Day Notes] = “usb”), ([Day Notes]= “cooler”))

or this

OR(CONTAINS([Day Notes], “crock” ),CONTAINS([Day Notes], “usb”),CONTAINS([Day Notes],“cooler”))

NOT TESTED
I removed the extra parenthisis before the second CONTAINS and a space. Does that still work?

Once I added the appropriate amount of parenthesis the second formula worked.

OR(CONTAINS([Day Notes], “crock” ),(CONTAINS([Day Notes], “usb”), (CONTAINS([Day Notes],“cooler”))))

Thanks Lynn!

It is now working for crock and usb but not cooler or anything else.

OR(CONTAINS([Day Notes], “crock” ),(CONTAINS([Day Notes], “usb”), (CONTAINS([Day Notes],“Cooler”), (CONTAINS([Day Notes],“trade”), (CONTAINS([Day Notes],“broken”), (CONTAINS([Day Notes],“room and cold”), (CONTAINS([Day Notes],“room”), (CONTAINS([Day Notes],“cold”), (CONTAINS([Day Notes],“r+c”), (CONTAINS([Day Notes],“h+c”), (CONTAINS([Day Notes],“hot”), (CONTAINS([Day Notes],“replace”), (CONTAINS([Day Notes],“pump”), (CONTAINS([Day Notes],“new customer”), (CONTAINS([Day Notes],“tap”),(CONTAINS([Day Notes],“bring”) ))))))))))))))))

Hi Lynn,

In your edited second expression, is it possible to do the same with NOT(CONTAINS()? The error I get is the following:

ERROR: Condition NOT(CONTAINS([Comment],"crock")) has an invalid structure: no more than 1 subexpression(s) allowed

Was just wondering if there was an expression in Appsheet that would allow for multiple needles in one haystack. 

 

The problem is not with your NOT(CONTAINS(...)) expression, it's where you're using it. Please post a screenshot of the entire expression and error message.

Hi, Thanks for the reply but I was able to get an answer and figured it out:

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

It would be nice though to have a separate table with the restricted words and just reference them.

There is another expression offered by a friend, that works with a separate table, but there's some limitation where punctuation affects the outcome because it's not using Contains()

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

If you understand it and could offer feedback, it would be appreciated! 


Lynn
Participant V

Hi @WillA108
I didnt test but i dont think you need all of those parenthisis. I have edited my second expression.

It kept adding them until it stopped coming up with an error. I thought it was odd too haha

It seems like it works, thank you!

Top Labels in this Space