Trying to create an if-formula with a multi-select field.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

[AllMeasures] is a multi-select field. This was my best guess for how to handle in an if statment and it's not working: 

If(includes([AllMeasures],"ROB") ...

Photo of Taylor

Taylor

  • 52 Points

Posted 3 years ago

  • 0
  • 1
Photo of Mira Shnier

Mira Shnier

  • 0 Points
You were close

Try this


If(Contains(ToText([AllMeasures]),"ROB"), ....

The Includes function is only used with List-User fields.  The Contains function is used with Text fields.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,904 Points 20k badge 2x thumb
This isn't obvious but here is what I found. If you apply the function ToText() to a multi-select text field you get a string value with all the individual values concatenated together with the three characters " ; " (space, semicolon, space). So you can detect if a particular value is within the multi select text field with a Checkbox formula such as this:

UPDATED:

Contains(
  " ; " & ToText([AllMeasures]) & " ; ",
  " ; " & "ROB" & " ; "
)
Photo of Taylor

Taylor

  • 52 Points
This worked! Thanks so much for your time!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,904 Points 20k badge 2x thumb
You need to include the semicolon delimiters or you can have some false matches. My updated formula should be correct. For example if [AllMeasures]="ROBERT" Mira's formula will falsely match because "ROB" is a substring of "ROBERT". Adding the delimiters insures the match includes all characters.