AnsweredAssumed Answered

Create list of contacts with shared last names?

Question asked by openspace on Feb 5, 2015
Latest reply on Feb 13, 2015 by openspace

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.

Outcomes