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

    Create list of contacts with shared last names?

    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.

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

          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?
            matthew_odell

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

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

              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?
                matthew_odell

                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?
                  openspace

                  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?
                    matthew_odell

                    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?
                      openspace

                      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.

                       

                      so intead of:

                       

                      Trimpin, Jo & Jill Brown

                       

                      I get:

                       

                      Trimpin , Jo & Jill Brown

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

                        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?
                          matthew_odell

                          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?
                            openspace

                            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?
                              openspace

                              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?
                                matthew_odell

                                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?
                                  openspace

                                  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 ) )

                                   

                                  )