Expression Assistance - Text Formatting through Expression Possible?

[mykrobinson]

I am hoping to be able to selectively format certain words in a virtual column that creates a concatenation of a patient's room number and certain medical flags that may be enabled for that patient. The current expression is:

IFS(
ISBLANK([Flags]),[Room],
ISNOTBLANK([Flags]),CONCATENATE([Room], " - ", [Flags])
)

I discovered the IFS() expression today 😂 and was happy to use it in a simple manner as above to trim the unnecessary hyphen if there were no flags for a patient.

What I am wondering now is if this expression can further be formatted to say something like 

If [ Flags] contains HFrEF, HFpEF, or a handful of other heart failure related conditions, add these to the concatenated value but place them in parenthesis.

So, for example, if our fake patient Demi Lovato were in room 8675309 and was flagged for HFrEF, Gout, and Diabetes, the column value would show

8675309 - Diabetes, Gout, (HFrEF)

[dbaum]

Is this what you mean?

Replace the current [Flags] argument within your CONCATENATE function with as many nested SUBSTITUTE functions as necessary:

SUBSTITUTE(SUBSTITUTE([Flags], "HFrEF", "(HFrEF)"), "HFpEF", "(HFpEF)")

[mykrobinson]

This could work, i was not aware of the SUBSTITUTE function and will look up the guide for it.

Thank you!

[Marc_Dillon]

Can [Flags] contain more than one of the tags that you want to display in parentheses?

Assuming yes, I would do something like this:

& " (" & INTERSECT( [Flags] , LIST( "HFrEF", "HFpEF",...) ) & ")" 

And perhaps wrap it all in a SUBSTITUTE( ... , " ()" , "" ) to get rid of the empty parentheses set if there aren't any flags to display.

[Steve]

CONCATENATE(
  [Room],
  (
    LIST(
      CONCATENATE(
        " - ",
        SUBSTITUTE(
          CONCATENATE(
            INTERSECT(
              ([Flags] - LIST("HFrEF", "HFpEF", ...)),
              [Flags]
            )
            + LIST(
              CONCATENATE(
                "(",
                INTERSECT(
                  LIST("HFrEF", "HFpEF", ...),
                  [Flags]
                ),
                ")"
              )
            )
            - LIST("()")
          ),
          " , ",
          ", "
        )
      )
    )
    - LIST(" - ")
  )
)

[mykrobinson]

Would you mind explaining the pluses and minuses that exist outside of quotations in that expression?

[Steve]

INTERSECT(
  LIST("HFrEF", "HFpEF", ...),
  [Flags]
)

creates a list of the heart failure flags present in [Flags].

CONCATENATE(
  "(",
  ...,
  ")"
)

converts that list to flt text and wraps it in parentheses.

LIST(
  ...
)
- LIST("()")

puts that perentheses-wrapped text into a one-item list, then removes that one item if it is exactly (), which occurs only if there were no heart failure flags.

INTERSECT(
  ([Flags] - LIST("HFrEF", "HFpEF", ...)),
  [Flags]
)

creates a list of flags that excludes heart failure flags.

INTERSECT(
  ...
)
+ LIST(
  ...
)
- LIST("()")

combines the list of non-heart failure flags and the heart failure flags (if any).

SUBSTITUTE(
  CONCATENATE(
    ...
  ),
  " , ",
  ", "
)

converts that list of flags to text, then replaces the default list separator (space-comma-space) with the proper English punctuation (comma-space).

LIST(
  CONCATENATE(
    " - ",
    ...
  )
)
- LIST(" - ")

Prepends - (space-hyphen-space) to the list of flags text, puts that result into a one-item list, then removes from that list the value that would occur only if there were no flags.

CONCATENATE(
  [Room],
  ...
)

Finally, combine the room identifier with the constructed list of flags (if any).

[mykrobinson]

here's what i have:

CONCATENATE(
[Room],
(
LIST(
CONCATENATE(
" - ",
SUBSTITUTE(
CONCATENATE(
INTERSECT(
([Flags] - LIST("HFrEF","HFpEF","HFmrEF","Normal EF","Unknown EF")),
[Flags]
)
+ LIST(
CONCATENATE(
"(",
INTERSECT(
LIST("HFrEF", "HFpEF", "HRmrEF", "Normal EF", "Unknown EF"),
[Flags]
),
")"
)
- LIST("()")
),
" ,",
", "
)
)
)
- LIST(" - ")
)
)
)

but am getting this error message:

Arithmetic expression '(CONCATENATE("(",INTERSECT(LIST("HFrEF","HFpEF","HRmrEF","Normal EF","Unknown EF"),[Flags]),")")-LIST("()"))' has inputs of an invalid type 'Unknown'

documenting so I can read over this a bit further and try to figure out where i went wrong.

[Steve]

Flags is EnumList, yes?

[mykrobinson]

yessir

[Steve]

Please post a screenshot of the expression.

[mykrobinson]

sorry for the delay, was out for a while yesterday. Here's a screenshot

[Steve]

I don't see a problem with the expression. You're going to need to pull parts out until you find the part that causes the error.