Using REGEX on a lookup field

David_Valentine · ‎Mar 01, 2021

I was receiving an error on all fields when trying to use REGEX on a lookup field. Using CONCATENATE () solved this issue. It doesn’t seem far-fetched to me to default or at least allow a formatting option or auto format lookup fields so this isn’t necessary.

James-Trory · ‎Mar 07, 2021

You could try using a Rollup field instead of a Lookup. Rollups work similarly to Lookups, but in a Rollup at the bottom of the field configuration, you are able to add formulas (some of which it will give you examples of) in order to manipulate what the Rollup does with the values it is referencing.

In your case, you could use CONCATENTATE(values) in the formula of the Rollup to do this in one field, instead of - as you described - using a Lookup and then needing a second formula to concatenate it.

Justin_Barrett · ‎Mar 07, 2021

Welcome to the community, @David_Valentine! :grinning_face_with_big_eyes: The problem is that lookup fields return arrays the vast majority of the time, not single values. Many functions that operate on strings, including the new Regex functions, require single strings to be passed as arguments, and won’t work when fed an array of strings. The rollup suggestion that @James-Trory mentioned is one way of working around this. Another is to force the array to become a string by concatenating it with an empty string. While the CONCATENATE() function will do this, a more concise method is to use the concatenate operator, which is the & character. For example, if I have a lookup field named {Lookup}, turning its output into a string could be done like this:

Lookup & ""

Used inside another function, it would look like this:

REGEX_REPLACE(Lookup & "", ".*?\\s", "")

More details on the values returned by various field types—including when those fields are referenced in a lookup—can be seen in this table that I built: