Using a Lookup field in a formula

I am putting task items into a schedule and assigning People who will work together to get the task done. The task is one line in the Schedule table. I have linked to the People table (fk_People) and then linked multiple people, depending on who is going to be working on the task.

I made a lookup field from the People table so only their first names show in the field (Name_F). Thus - if I have assigned Trevor and Matt to the task, their names show up as

Trevor, Matt

in the lookup field. This is what I want to show up in the formula field.

I have made a formula field to bring together the name of the task and the first names of the people assigned to work together:
Name_Task & " - " & Name_F

My problem is that the formula returns the Name_F lookup so that it looks like this

TrevorMatt

instead of

Trevor, Matt

I must be missing something - but I cannot figure out where to look to get the answer.

Hi Jenny, try changing it to:

Name_Task & " - " & ARRAYJOIN(Name_F)

I think that should solve your problem, and I’ve put a base together here as an example

(You could also change your lookup field to a rollup field)

That is much better - I guess there isn’t anyway to get that space after the commo in the ARRAYJOIN - to improve readability?

Otherwise, I will use this - it is better than setting up a formula to do the work.

Thanks!

Nah you can, try this: Name & " - " & SUBSTITUTE(ARRAYJOIN({Lookup}), ',', ', ')

That fixed the problem. Thank you for your help. Really appreciated.

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.