Trouble applying formula to lookup field

[Susan_Lanier]

I have a couple of fields that are populating a page designer block to generate booking confirmations for clients. I have a projects table and a client contacts table.

In the projects table I have a field that links to client contacts, and then a looks up field that populates contacts first name only for the purpose of addressing them in my page designer block. My problem is that sometimes I have multiple contacts on a project. See:

I need to populate my page designer block with “Kelly and Chris”.

I’ve tried this substitution formula:

SUBSTITUTE({Contact first name look up}, “,”, " and")

It appears that you can’t run such a formula on a lookup field.

Help would be appreciated.

Thank you!

[Jeremy_Oglesby]

The problem here is just that the Lookup field is outputting an array, not a string, and you are trying to perform a string operation on it (SUBSTITUTE()).

You can get around this by coercing the array into a string - this is done by simply concatenating the array with an empty string using the concatenation operator (&). So here is your solution:

SUBSTITUTE({Contact first name look up} & '', ',', ' and')

[JonathanBowen]

Hi @Jeremy_Oglesby, @Susan_Lanier - I came up with the same formula as @Jeremy_Oglesby, but it didn’t work for me. Stringifying the array removed the comma, so I couldn’t then substitute this with “and”. With a bit of trial and error, this worked:

SUBSTITUTE(ARRAYJOIN({Contact first name look up}, ';'), ';', ' and ')

i.e. forcing the ";" in and then substituting this.

Edit: changed the field name to original example

JB

[Jeremy_Oglesby]

Good catch - I should have tested

[Susan_Lanier]

Oh! Learn something new every day. @JonathanBowen and @Jeremy_Oglesby, thanks for your help! This is going to come in handy.