May 29, 2019 12:47 PM
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!
May 29, 2019 12:56 PM
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')
May 29, 2019 01:00 PM
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
May 29, 2019 01:44 PM
Good catch - I should have tested
May 29, 2019 02:21 PM
Oh! Learn something new every day. @JonathanBowen and @Jeremy_Oglesby, thanks for your help! This is going to come in handy.