10 Replies Latest reply on Feb 23, 2012 11:42 PM by comment

    Add an 'and' between 2 names

    noushie

      I have joined the primary and secondary contact names. For example, if the primary contacts is called John Smith and his wife Anna Smith, I have joined these into one field and get John Smith Anna Smith. I have 2 questions:

      How do I add an 'and' between the two contacts? eg. so I get John Smith and Anna Smith.

      Also, how do I exclude the surname from appearing twice, eg, reduce it to John and Anna Smith?

        • 1. Re: Add an 'and' between 2 names
          innodat

          Based on the original fields, try this new calculated field:

           

          Case (

           

          /*If the last names are equal*/

          RightWords (First Person; 1) = RightWords (Second Person; 1);

           

          /*then only use first name of person 1, add second person joined by " and " */

          LeftWords (First Person; 1) & " and " & Second Person;

           

          /*Else - use full names joined by " and "*/

          First Person & " and " & Second Person

           

          )

           

           

          I've created a sample file here: http://www.training.artvaultsoftware.com/samples/MergeNames.fp7.zip

          • 2. Re: Add an 'and' between 2 names
            comment

            noushie wrote:

             

            Also, how do I exclude the surname from appearing twice, eg, reduce it to John and Anna Smith?

             

            It would be best to have separate fields for given name and surname. Otherwise you'll run into trouble with people like "Anna Nicole Smith" or "Anna Smith-Jones".

            • 3. Re: Add an 'and' between 2 names
              Stephen Huston

              Both of the posts ahead of me offered good advice. My question is how you are currently joining them?

               

              If you give us that detail, we can suggest solutions which best fit your approach, unless we find something worrisome about the method.

               

              It could be done with a calc field, an Evaluate on one or more text fields, or via scripting. You haven't told us how you have done what you have so far.

              • 4. Re: Add an 'and' between 2 names
                noushie

                At the moment for each contact I have  fields that separate each element ie. one field for the primary contacts Title, another for thier first name, another for their surname, and I also have separate fields for the secondary contacts title, 1st and 2nd.

                 

                To join these fields together I currently have a field that provides the Full name of the primary contact

                Name_Full_Primary

                Evaluate ( "Name_First_Primary_Contact & \" \" & Name_Last_Primary_Contact" ; [Name_First_Primary_Contact ; Name_Last_Primary_Contact] )

                And the same for the secondary and then join the two in the following:

                Name_Full_PrimaryandSecondaryName

                Evaluate ( "Name_Full_Primary & \" \" & Name_Full_Secondary" ; [Name_Full_Primary ; Name_Full_Secondary] )

                 

                Is there a way to add an 'and' between them if there is both a primary and secondary contact?

                • 5. Re: Add an 'and' between 2 names
                  BruceHerbach

                  Hi,

                   

                  You may be making this a bit more complicated then it has to be, and you probably don't need the evaluate command.   Instead you can use a let statement to pull the pieces you need.   Try this:

                   

                  Let [(

                  FPrimary = Name_first_primary_contact;

                  LPrmary = Name_Last_Primary_Contact;

                  FSecondary = Name_first_secondary_contact ];

                   

                  Fprimary & " and " & Fsecondary & " " & Lprimary )

                   

                  If the Primary is Harry Smith and the secondary is Charlot Smith then the result should be

                  Harry and Charlot Smith.

                   

                  I suggest dropping the evaluate comand because it is a very capable command,  but because of it's power can be somewhat slow. 

                  HTH

                  Bruce

                  • 6. Re: Add an 'and' between 2 names
                    DavidZakary

                    I usually do something like this. Also, it makes sense to split first and last names. There is a custom function that will split names apart into separate fields - so it could be possible to retrofit your database if you wanted. it has a lot of options for things like hyphenated names.

                     

                    Substitute (List (first_name; " and " &amp; last_name); "<cr>"; "")

                     

                    The <cr> is the carriage return/pilcrow - can't type that on an iPad. This is a very simplified example but I find simpler than the Evaluate version.

                    • 7. Re: Add an 'and' between 2 names
                      DavidZakary

                      The ampersand was converted to the HTML entity

                      • 8. Re: Add an 'and' between 2 names
                        comment

                        noushie wrote:

                         

                        Is there a way to add an 'and' between them if there is both a primary and secondary contact?

                         

                        Try =

                         

                        Substitute ( List ( Name_Full_Primary ; Name_Full_Secondary ) ; ¶ ; " and " )

                         

                         

                         

                        As others have noted, you are using Evaluate() for no good reason:

                         

                        Evaluate ( "Name_First_Primary_Contact & \" \" & Name_Last_Primary_Contact" ; [Name_First_Primary_Contact ; Name_Last_Primary_Contact] )

                         

                        can be written simply as =

                         

                        Name_First_Primary_Contact & " " & Name_Last_Primary_Contact

                        • 9. Re: Add an 'and' between 2 names
                          noushie

                          This calculation works a treat when you have 2 contacts, ie Mr John Smith and Ms Kelly Clark. However, if you only have 1 contact it leaves an ‘and’ after the name ie. “Mr John Smith and”. Is there a way to script this using the substitute calc so an ‘and’ wont be included if there isn’t a second contact?

                          • 10. Re: Add an 'and' between 2 names
                            comment

                            noushie wrote:

                             

                            if you only have 1 contact it leaves an ‘and’ after the name

                             

                            I believe the problem is with your full name calculation returning a space instead of being empty. Try instead =

                             

                            Substitute ( List (

                            Trim ( Name_First_Primary_Contact & " " & Name_Last_Primary_Contact ) ;

                            Trim ( Name_First_Secondary_Contact & " " & Name_Last_Secondary_Contact )

                            ) ; ¶ ; " and " )

                             

                             

                            or, to return to your original question =

                             

                            Case (

                            Name_Last_Primary_Contact = Name_Last_Secondary_Contact ;

                             

                            Substitute ( List ( Name_First_Primary_Contact ; Name_First_Secondary_Contact ) ; ¶ ; " and " )

                            & " " &

                            Name_Last_Primary_Contact

                            ;


                            Substitute ( List (

                            Trim ( Name_First_Primary_Contact & " " & Name_Last_Primary_Contact ) ;

                            Trim ( Name_First_Secondary_Contact & " " & Name_Last_Secondary_Contact )

                            ) ; ¶ ; " and " )

                            )