13 Replies Latest reply on Feb 13, 2015 12:19 AM by openspace

# Create list of contacts with shared last names?

I have a list of contacts and a list of memberships. Each member can have one membership and each membership can be shared by multiple contacts. In other words, we have two types of memberships:

a)individual

b)couple/family

How do I create a calculation that will look at a list of member names and list them with commas and an & while also recognizing if members share last names? The following code that I've written only works for one or two contacts sharing a membership. If there are two contacts it will check if they share the same last name or not and list them with an & or comma.

If ( Member Count  = 2 ;

If ( (Customers::Last & " & " & Customers::Last) = (Substitute(List (Customers::Last); ¶ ; " & "));

Substitute ( List ( Customers::First ) ; ¶ ; " & " ) & " " & Customers::Last;

Substitute ( List ( Customers::Customer Name FL ) ; ¶ ; " & " )

) ;

Substitute ( List ( Customers::Customer Name FL ) ; ¶ ; ", " )

)

Here are some examples of the input/output I'd like:

Example 1: Rick Raxlen, Susy Raxlen, and Jesse Raxlen share a family membership

Ouput: Rick, Susy, & Jesse Raxlen

Example 2: Rick Raxlen, Susy Raxlen, and Tamara Bond

Output: Rick & Susy Raxlen, & Tamara Bond

Example 3: Rick Raxlen, Tamara Bond, and Chris Reiche

Output: Rick Raxlen, Tamara Bond, & Chris Reiche

Note, there can be 1-10 contacts sharing a membership.

• ###### 1. Re: Create list of contacts with shared last names?

This seems like a really fun place for something like a recursive custom function if it has to be a calculation and can't be a looping script. If I were to build something like this, this is how I'd do it:

First off, get a list of first names and last names, and have them separated by something so you can compare the last name to the record before or after it. Something like this:

Rick,Raxlen

Susy,Raxlen

Tamara,Bond

Then I would write a custom function that takes that list as text, and recursively loop through each line. Each time I would check if the last name of the line I'm working on is equal to the last name of the line after (if it is, don't display it), and also check if the line after is the last line (if it is, pass a " & ", if it isn't, pass a ", ")

You know what, this sounds like fun, give me a few minutes...

• ###### 2. Re: Create list of contacts with shared last names?

Alright, example file attached. You caught me on a good day...

• ###### 3. Re: Create list of contacts with shared last names?

You're incredible! You didn't even break a sweat. That would have taken me hours...

I haven't dabbled in custom functions yet... I'm trying to understand what the function means but I'm getting about half way until I'm lost. I would love to try and adapt this so that when there are two contacts there is no comma in other words it displays as "Joe & Jill Jo" rather than "Joe, & Jill Jo" If you don't mind explaining how the function is defining the different values so I can try to figure this out? Apologies for the newbness... I dropped out of computer science years ago and I have no idea how I fell back into coding again...

• ###### 4. Re: Create list of contacts with shared last names?

Hmmm, it shouldn't be outputting "Joe, & Jill Jo" if the data you are feeding it looks like this:

Joe,Jo

Jill,Jo

The only way it would look like that is if the data was like

Joe

Jill,Jo

Let's see if we can get this answered, then I can get a little into how the thing works. I don't want to muddy the waters with too much information if we can fix this issue pretty easily.

• ###### 5. Re: Create list of contacts with shared last names?

Ahh, this is exactly the issue at hand. I'm actually working with a list of artists, some of which have a performing name that consists of one name rather than two.

• ###### 6. Re: Create list of contacts with shared last names?

So in that case, the best way to deal with this is to throw away all cases of using the oxford comma. The way to do that is to change this one line to this:

Separator = Case( NoOfNames = 1 ; "" ; Last = LastAfter and Last ≠ Last2After ; " & " ; NoOfNames = 2 ; " & " ; ", " );

Basically, all I did was remove one of the commas where it's bold here:

Separator = Case( NoOfNames = 1 ; "" ; Last = LastAfter and Last ≠ Last2After ; " & " ; NoOfNames = 2 ; ", & " ; ", " );

• ###### 7. Re: Create list of contacts with shared last names?

Okay I see what you did there. I still notice that an extra space is inserted after the first name if the next two names share a last name. All other instances of the function work as desired.

Trimpin, Jo & Jill Brown

I get:

Trimpin , Jo & Jill Brown

• ###### 8. Re: Create list of contacts with shared last names?

One possibility you should eliminate is that there is actually a space at the end of the first name, or even a single space instead of an empty field in surname.

• ###### 9. Re: Create list of contacts with shared last names?

Yep, change the line above it to this:

LastNameDisplay = Case( Last = LastAfter or IsEmpty(Last) ; "" ; " " & Last );

• ###### 10. Re: Create list of contacts with shared last names?

I definitely checked this one before posting! Not uncommon for me to slip an extra space in... although I think I have the Trim() function on all of my contact names for that very reason.

• ###### 11. Re: Create list of contacts with shared last names?

Incredible, thank you. When I have a second to breath I'll have to look into this in more detail. I hate not fully understanding code, it will drive me nuts! Thanks again @Mathew O'Dell

• ###### 12. Re: Create list of contacts with shared last names?

Here's a better description with comments:

Custom function name: CreateNameList( Names )

Let( [

// Figure out how many names you have to work with

NoOfNames = ValueCount( Names );

// Grab the first row of names, then separate first and last into two rows to grab the first and last name

Name = Substitute( GetValue( Names ; 1 ) ; "," ; ¶ );

First = GetValue( Name ; 1 );

Last = GetValue( Name ; 2 );

// Do the same thing to figure out the last name of the row after this

NameAfter = Substitute( GetValue( Names ; 2) ; "," ; ¶ );

LastAfter = GetValue( NameAfter  ; 2 );

// And again for the row after that

Name2After = Substitute( GetValue( Names ; 3) ; "," ; ¶ );

Last2After = GetValue( Name2After  ; 2 );

// Figure out whether or not to display the last name

LastNameDisplay = Case( Last = LastAfter or IsEmpty(Last) ; "" ; " " & Last );

// Figure out how I should separate this name from the name after me ( no separator if there is one name, " & " in certain cases, and ", " in other cases

Separator = Case( NoOfNames = 1 ; "" ; Last = LastAfter and Last ≠ Last2After ; " & " ; NoOfNames = 2 ; " & " ; ", " );

// For recursion, create a new list of names without the first row

NextSetOfNames = RightValues ( Names ; NoOfNames - 1 )

] ;

// Put them all together, and if there is is more than 1 row, run this again without the top row

First & LastNameDisplay & Separator & Case( NoOfNames > 1 ; CreateNameList ( NextSetOfNames ) )

)

• ###### 13. Re: Create list of contacts with shared last names?

Ahh, now I get it. Thank you for spending the time to write this out is layman's terms! I hope you don't mind, but now that I understand the function I was able to adapt it to work for first names. Merging the contact names in Microsoft Word will be a complete dream now.

Let( [

// Figure out how many names you have to work with

NoOfNames = ValueCount( Names );

// Grab the first row of names, then separate first and last into two rows to grab the first and last name

Name = Substitute( GetValue( Names ; 1 ) ; ¶ ; ¶ );

First = GetValue( Names ; 1 );

// Do the same thing to figure out the last name of the row after this

NameAfter = Substitute( GetValue( Names ; 2) ; ¶ ; ¶ );

// And again for the row after that

Name2After = Substitute( GetValue( Names ; 3) ; ¶ ; ¶ );

// Figure out how I should separate this name from the name after me ( no separator if there is one name, " & " in certain cases, and ", " in other cases

Separator = Case( NoOfNames = 1 ; "" ; NoOfNames = 2 ; " & " ; ", " );

// For recursion, create a new list of names without the first row

NextSetOfNames = RightValues ( Names ; NoOfNames - 1 )

] ;

// Put them all together, and if there is is more than 1 row, run this again without the top row

First & Separator & Case( NoOfNames > 1 ; CreateNameList ( NextSetOfNames ) )

)