6 Replies Latest reply on Aug 13, 2012 9:35 AM by philmodjunk

    Simple dealing with Hyphens

    DRR

      Title

      Simple dealing with Hyphens

      Post

      I have searched and read about dealing with hyphens, but I am missing a hopefully simple detail.

      Using FM 12 Pro:

      I have a FullName field and would like to extract last names. However, using the Rightwords calculation, it will only pull or extract the last part of the hyphenated name. (example "Smith-Jones" will return only "Jones"). I tried using the count function, but that doesn't seem to work because FM sees hyphens between names as a unifying unit, so Ann Smith-Jones will contain two names as would Ann Jones. 

      I have read somewhere that one could use a substitute function where you replace "-" with " " or a space, then do the calculation, but I am not sure how to put the entire calculation together and then get the hyphen back in afterward.

      Thanks in advance.

      DRR

        • 1. Re: Simple dealing with Hyphens
          philmodjunk

          Try this approach (assumes a single space between first and last names:

          Let ( L = Substitute ( FullNameField ; " " ; ¶ ) ;
                 GetValue ( L ; ValueCount ( L ) ) ; // works with and without middle names/initials
                )
                   

          • 2. Re: Simple dealing with Hyphens
            DRR

            Thanks. Does that do the whole thing? In other words, will this calculation pull out "Smith-Jones" and "Johnson" etc., or is this just the function that gives me the correct count? (Sorry, I don't do calculations often). Also, when I put that in, FM highlights the ";" after ValueCount (L)) and says there are too many functions in this calculation.

            • 3. Re: Simple dealing with Hyphens
              DRR

              Here is my simple calculation:

              If ( Count ( Patient Name_c ) = 3 ; RightWords(Patient Name_c; 2) ; RightWords(Patient Name_c; 1) )

              • 4. Re: Simple dealing with Hyphens
                philmodjunk

                Try it and see.

                It should return either Smith-Jones, Johnson or whatever makes up the last name. It will fail if a space is inserted between two parts of a last name or if there's a space after the last name, but should work with most other name variations as it finds the last chunk of text after the last space character and returns that.

                You may want to use Trim in the first Let term to eliminate any trailing spaces so they don't mess up how this works.

                • 5. Re: Simple dealing with Hyphens
                  DRR

                  It works! Thanks much

                  (I ended up pulling out the end part to make it work. Sorry, if that should have been obvious)

                  Let( L = Substitute ( FullNameField ; " " ; ¶ ) ;
                         GetValue ( L ; ValueCount ( L ) )
                  • 6. Re: Simple dealing with Hyphens
                    philmodjunk

                    By "end part" do you mean the comment?

                    It's part of why I put the last ) on it's own line. anyting to the right of // in a calculation is treated as a comment (ignored).

                    Using your format in place of mine, you could have used:

                    Let( L = Substitute ( FullNameField ; " " ; ¶ ) ;
                    GetValue ( L ; ValueCount ( L ) ) // works with and without middle names/initials

                    But this is no big deal either way as it was primarily a comment to you about how it should work.

                    Note that I would probably use it this way in my own DB:

                    Let( L = Substitute ( Trim ( FullNameField ) ; " " ; ¶ ) ;
                          GetValue ( L ; ValueCount ( L )
                         )

                    And just to point out the obvious: If you put first and last names in separate fields, you don't have this issue in the first place. Given the many odd things people do with their names, it's much easier to use calculations to combine them into a single field when needed than it is to consistently separate them.