5 Replies Latest reply on Nov 13, 2012 1:55 AM by Sorbsbuster

    Hyphenated Surname as One Word



      Hyphenated Surname as One Word


           I have a field called Individual, which has the full name of the person, e.g. Smith, John Henry. I made a filter field for the comma and the calculation to get the first names. Below is the right words calculation for the first name or names, which works perfectly. However, for the surname field, which is done by this calculation: LeftWords (Saint John Residents::Individual ; 1 ), I can't get a hyphenated surname, e.g. Smith-Jones. What appears is Smith.

           Right words calculation:


           If (comma filter = ","   ; Let ( [
           t =Individual;
           L = Length (t) ;
           p = Position ( t ; "," ; 1 ; 1)] ; Right ( t ; L - p )); "")

        • 1. Re: Hyphenated Surname as One Word

               You could try:

               Substitute ( LeftWords (Substitute ( Saint John Residents::Individual ; "-" ; "xxxx" ) ; 1 ) ; "xxxx" ; "-" )

          • 2. Re: Hyphenated Surname as One Word

                 Bingo -- it worked perfectly! Not being a programmer, I don't understand what the "xxxx" means, so if you have a minute or two, I'd love a simple explanation of the whole calculation so that I could get a fuller understanding for the next time I run into a similar issue. Thanks again!

            • 3. Re: Hyphenated Surname as One Word

                   Sorbsbuster is very inventive.

                   A nested substitution - the inner one changed - to xxx (only if - existed), found the lastxxxname, and substituted it back.

              • 4. Re: Hyphenated Surname as One Word

                     I agree: Sorbsbuster is indeed inventive. Thank you for the explanation -- much appreciated!

                • 5. Re: Hyphenated Surname as One Word

                       That was what you asked for, but I think you will prefer something more robust.  That will correctly handle:

                       My Left Foot's Day-Lewis, Daniel

                       But fail with his pornstar sister, Day-Lewxxxxd, Debbie

                       It will also fail with the Sound Of Music's von Trapp, Maria

                       If you can rely on the comma being where it is (as you seem to be for the first name), then it would be more reliable to use:

                       Left ( Saint John Residents::Individual  ; Position ( Saint John Residents::Individual ; ","; 1 ; 1 ) - 1 )

                       If you prefer the format of your original calculation for could use (inside your If statement):

                       Let ( [
                       t =Saint John Residents::Individual ;
                       p = Position ( t ; "," ; 1 ; 1)]

                       ; Left ( t ; p - 1 ))

                       (BTW: you seemed to have built in the flexibility in your IF statment to alter what the name seperator is (although you call it 'comma filter') but the in the rest of the statement you hard-code the comma, so removing the flexibility.  I think it would be a better exploitation if you renamed the 'comma filter' field as 'FilterCharacter', and then made it, for example:

                       Let ( [

                       t =Individual;
                       L = Length (t) ;
                       p = Position ( t ; FilterCharacter ; 1 ; 1)] ; Right ( t ; L - p ))