Help

Combine Names with the same and Different Last Names

Topic Labels: Data Formulas
Solved
Jump to Solution
2067 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Yvia
6 - Interface Innovator
6 - Interface Innovator

Hi, everyone! I need help in combining names from two Rollup fields with the result below:

First Name                        Last name                        Formula Field (Result I want)

Mary                                  A.                                       Mary A

Mary & John                     A & A                                Mary & John A

Mary & John                    A & B                                 Mary A and John B

 

Yvia_0-1679952419084.png

Thank you in advance!

1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

Okay. I'll admit that this one stumped me.
Out of full transparency, I'm not a fan of this method, but I'll hold my tongue.

Here's some context:

I created two tables. In the first one, we have our people records. In order for to work, we need the first and last name in separate fields. You also need a field that brings the first and last name together to form a full name string.

Snag_2a7642cd.png

On our "target" table, we have the relationship field to the people table. We also have rollup fields for the first and last names.

Here's the table and the rollup configurations for each field:

Snag_2a7a19d6.png

Snag_2a7a4b17.png

Snag_2a7a719a.png

With our fields we can now create our final field.
This field is going to be a rollup field. It should point to the field on your people table that contains the full name of your contacts.

From there, I'll use the following formula:

 

IF(
  values,
  IF(
    {Last Names},
    IF(
      REGEX_MATCH({Last Names}, ","),
      ARRAYJOIN(values, " & "),
      {First Names} & " " & {Last Names}
    )
  )
)

 

Here's the field configuration and the final result:

Snag_2a7d83dc.png

Snag_2a7dbeb2.png

Okay, there's a few things you need to know if you're going to use this design pattern.
The last names must be completely identical in order for this to work. This includes whitespace.

As an example I added a trailing space to the end of the Mary Bradley record's last name field value.

Snag_2a82b8de.png

Snag_2a83583b.png

Because of this added whitespace, the formula determines that the last names of these two records are different and not unique, therefore the last names and returned for each person contact.

The second important thing to note is that this pattern doesn't hold up well if you intend to link three or more people to a record. It quickly becomes bloated and the formula is no longer able to keep up the façade.

Here's what happens if I add three people to a record with two of the people sharing a last name and the third record having a unique last name.

Snag_2a86b446.png

See Solution in Thread

2 Replies 2
Ben_Young1
11 - Venus
11 - Venus

Okay. I'll admit that this one stumped me.
Out of full transparency, I'm not a fan of this method, but I'll hold my tongue.

Here's some context:

I created two tables. In the first one, we have our people records. In order for to work, we need the first and last name in separate fields. You also need a field that brings the first and last name together to form a full name string.

Snag_2a7642cd.png

On our "target" table, we have the relationship field to the people table. We also have rollup fields for the first and last names.

Here's the table and the rollup configurations for each field:

Snag_2a7a19d6.png

Snag_2a7a4b17.png

Snag_2a7a719a.png

With our fields we can now create our final field.
This field is going to be a rollup field. It should point to the field on your people table that contains the full name of your contacts.

From there, I'll use the following formula:

 

IF(
  values,
  IF(
    {Last Names},
    IF(
      REGEX_MATCH({Last Names}, ","),
      ARRAYJOIN(values, " & "),
      {First Names} & " " & {Last Names}
    )
  )
)

 

Here's the field configuration and the final result:

Snag_2a7d83dc.png

Snag_2a7dbeb2.png

Okay, there's a few things you need to know if you're going to use this design pattern.
The last names must be completely identical in order for this to work. This includes whitespace.

As an example I added a trailing space to the end of the Mary Bradley record's last name field value.

Snag_2a82b8de.png

Snag_2a83583b.png

Because of this added whitespace, the formula determines that the last names of these two records are different and not unique, therefore the last names and returned for each person contact.

The second important thing to note is that this pattern doesn't hold up well if you intend to link three or more people to a record. It quickly becomes bloated and the formula is no longer able to keep up the façade.

Here's what happens if I add three people to a record with two of the people sharing a last name and the third record having a unique last name.

Snag_2a86b446.png

Sorry for the trouble but thank you so much this works!!!