If Concatenate Formula

[John_Dlouhy]

I have a field named Concatenated Attendee Name(s) with this formula:

CONCATENATE(First&" “&Last&” “&{With First}&” "&{With Last})

I have another Multiple Select field named Guest Code. One of the many choices is ‘Scholar’.

How would I edit the formula above to only concatenate names that have the Guest Code of ‘Scholar’?

Thanks very much!

[JonathanBowen]

Hi @John_Dlouhy - A multi select field returns an array of values (even if only one value is selected), so you need to turn this into a string, then find the value you are looking for (in this case “Scholar”). If this returns a value > 0, then do your CONCATENATE. So, putting that all together you get:

IF(
  FIND('Scholar', {Guest Code} & '') > 0,
  CONCATENATE(Your formula here)
)

JB

[John_Dlouhy]

Thanks so much, Jonahtan! I apologize for taking so long to express my appreciation for this!

All best, and hope you have a great weekend.

John

[Justin_Barrett · Accepted solution]

On a side note, your use of CONCATENATE() wrapped around that other formula is a little redundant.

The formula inside that:

First & " " & Last & " " & {With First} & " " & {With Last}

…outputs exactly what you want, without using CONCATENATE(). That’s because the & operator is designed essentially as a shortcut alternative for CONCATENATE().

The CONCATENATE() function is designed to take a comma-separated series of items—the function arguments—and return a single string. To reformat your formula using CONCATENATE() properly, it would look like this:

CONCATENATE(First, " ", Last, " ", {With First}, " ", {With Last})

In other words, you would probably pick either & or CONCATENATE() when building a string from multiple pieces, but not both. :slightly_smiling_face:

[John_Dlouhy]

Thank you for the clarification, Justin!

I am searching for another solution. I have a look up field called “Scholars for Program.” It is returning the right results, namely a comma separated sting of student’s first/last names for a given scholarship (e.g. scholarship is Dr. Lina Zeine and Family Scholarship and student recipients are Hokulani Rivera, Brielle Lamphere, Sylvia Gray, Kaitlyn Freitas).

However, I am hoping to have the sting of student names be in alphabetical order by last name.

In the example above you can see it’s out of alpha order for last name. Is there a way to include a sort command in my formula?

IF({Guest Code}=‘SCHOLAR’,First&" "&Last)

Thanks very much in advance for any assistance on this!

John

[Justin_Barrett]

John_Dlouhy:

Is there a way to include a sort command in my formula?

Sadly not. There are no sorting functions available for use in formulas, nor is there any way to sort the output of a lookup or rollup field. The output of both of those field types is driven by the order that links were created, and that order isn’t affected by the record order in any particular view (although your problem would be easily solved if that were an option).

That said, there’s still a way to get what you want if your base is part of a Pro account. Make a new Page Designer block, and add a new item from your field that links to these student records. In the design options, set the Mode to “Inline,” which will format them as a comma-separated list. To sort them, click “Add Sort” option under Sort records, choosing the {Last} field as your primary sort criteria. You can add as many other sort options as you want. Once you exit editing mode, the text of the sorted list can be highlighted and copied from the block.