Appian wherecontains()

The shape

wherecontains() takes a value and a list, and returns the positions where that value appears.

wherecontains("b", {"a", "b", "c"})   →   {2}

The value comes first and the list second, which is the opposite order to most functions that search. Getting it backwards returns nothing rather than failing.

Every occurrence, not just the first

wherecontains("b", {"a", "b", "b"})   →   {2, 3}

A value that is not there gives an empty list, which is what you feed to index() or remove() without needing a special case.

Several values at once

The first argument can be a list. You then get the positions of anything matching any of those values:

wherecontains({"a", "c"}, {"a", "b", "c"})   →   {1, 3}

That makes it the natural way to work against a block list or a set of allowed values.

How it differs from its neighbours

• wherecontains(value, list) — where a value is, as positions
• where(condition) — where a condition holds, as positions
• contains(list, value) — whether it is there at all, as true or false. Note this one takes the list first

Use wherecontains() for matching exact values and where() for anything with a comparison in it.

It finds nulls, and where() cannot

wherecontains(null, {1, null, 3})   →   {2}

Worth remembering, because the obvious alternative does not work: isnull() judges a whole list as one thing, so where(isnull(list)) silently finds nothing.

The trap: matching is case-sensitive

wherecontains("B", {"a", "b"})   →   {}

Nothing found, and nothing to tell you why. Data entered by people rarely agrees on capitalisation, so put both sides through upper() or lower() before searching when the case is not guaranteed.

Removing what you found

remove(list, positions) takes positions out, so the two compose directly:

remove(items, wherecontains("b", items))   →   every "b" gone