Skip to main content

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!

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')

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')

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

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


Good catch - I should have tested

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

Reply


Terms & Conditions