How do I write the formula to look for more than one value in a text string

[sonseeahray]

Not finding this answer in the discussions...

How do I write the formula to use in the formula tool to look for more than one value in a string with in an IF AND statement?

This is what I have:

[MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && CONTAINS([DESC],"TIME")
then ...

What I need is to look for "TIME" OR "DISTANCE" in the [DESC] field...

[Jonathan-Sherman]

You'd need to change your formula to:

IF [MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && CONTAINS([DESC],"TIME")

OR [MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && CONTAINS([DESC],"DISTANCE")

THEN X

ELSE X

ENDIF

If this solves your issue please mark the answer as correct, if not let me know!

Regards,

Jonathan

[LukeG]

@sonseeahray 

Good question. The formula below will identify whether or not the field contains both "TIME" and "DISTANCE".

If contains([DESC], "TIME") AND contains([DESC], "DISTANCE")
Then "True"
Else "False"
Endif

If you would like to determine whether or not it has at least one of the two, the following formula will work.

If contains([DESC], "TIME") OR contains([DESC], "DISTANCE")
Then "True"
Else "False"
Endif

Let me know if there are any questions. If I misunderstood the task, please let me know and I will provide additional assistance. 

[danilang · Accepted solution]

Hi @sonseeahray 

Shorter than @Jonathan-Sherman's version, but logically equivalent.  Note the extra brackets in bold.  

IF [MP]="HC" && [ADJ]!=0 && [PrevA]=0 && [PAYS]!=0 && ( CONTAINS([DESC],"TIME") OR CONTAINS([DESC],"DISTANCE") )

THEN X

ELSE X

ENDIF

Dan

[Jonathan-Sherman]

I did wonder about brackets but have never seen them used! Thanks @danilang, learn something new everyday!

[sonseeahray]

Thank you @danilang! That is what I needed!