5 Replies Latest reply on Sep 2, 2012 4:37 PM by davidanders

    Extracting Names

    SusanLazear

      Title

      Extracting Names

      Post

      Hi

       

      I have been trying to extract a single field into the appropriate First, Middle, Last, Title, etc. fields.

      using the help document

      Extracting Title, First, Middle, and Last Name from a Single Field 

      All works well, except for the Middle Name. I cannot get a name like Mary Elizabeth Smith to put the Elizabeth in my middle name field.  I have cut and pasted the information directly from the document above, using the exact same field names.

       

      Case(IsEmpty(Title) = 1 and MiddleWords(FullName; 2; 1) - LastName; MiddleWords(FullName; 2; 1); 
      IsEmpty(Title) = 0 and MiddleWords(FullName; 3; 1) - LastName; MiddleWords(FullName; 3; 1); "")

      Any ideas?

       

      Thanks...

       

      Susan

        • 1. Re: Extracting Names
          davidanders

          Your current field can contain how many words?
          Always 3? sometimes 2? seldom 4?
          What are your field names.

          I do not think that the  IsEmpty test is needed.

          If two words, are they ALWAYS first and last name? Probably.
          If three words, are they ALWAYS first, middle, and last name? Maybe.

          If there are a variable number of words, you would need to use WordCount function with LeftWord, MiddleWords, and Lastword and Case to parse the name correctly.

          WordCount
          http://www.filemaker.com/help/html/func_ref3.33.89.html#1032068
          FORMAT    WordCount(text)

          --from the extract names help page--
          MiddleName (Calculation, text result)=
          Case(IsEmpty(Title) = 1 and MiddleWords(FullName, 2, 1) lastname, MiddleWords(FullName, 2, 1),
          IsEmpty(Title) = 0 and MiddleWords(FullName, 3, 1) lastname, MiddleWords(FullName, 3, 1), "")

          From your post above

          Case(IsEmpty(Title) = 1 and MiddleWords(FullName; 2; 1) - LastName; MiddleWords(FullName; 2; 1);
          IsEmpty(Title) = 0 and MiddleWords(FullName; 3; 1) - LastName; MiddleWords(FullName; 3; 1); "")

           

          MiddleWords
          http://www.filemaker.com/11help/html/func_ref3.33.71.html
          FORMAT   MiddleWords(text;startingWord;numberOfWords)

          Case
          http://www.filemaker.com/help/html/func_ref3.33.2.html
          FORMAT   Case(test1;result1{;test2;result2;...;defaultResult})

          Case(test1;result1

          ;test2;result2

          ;test3;result3

          ;defaultResult)

          EXAMPLE   Case(Score >= 90;“Excellent”;Score > 50;“Satisfactory”;“Needs Improvement”)

          IsEmpty
          http://www.filemaker.com/help/html/func_ref3.33.12.html
          FORMAT   IsEmpty(field)
          EXAMPLE     IsEmpty(OrderNum) returns 1 if the OrderNum field is empty.  
                           If(IsEmpty(LastName);“Invalid record”;““)
          displays Invalid Record if the LastName field is blank, but displays nothing if there is an entry in LastName.
                           IsEmpty(“text”)
          returns 0.

          CASE with IsEmpty
          Case (IsEmpty (Field1 & Field2); Field3
          ; IsEmpty (Field1) ; Field2
          ; Field1)

          • 2. Re: Extracting Names
            schamblee

            Case(IsEmpty(title)=1 and IsEmpty(Suffix)=0 and WordCount(fullname) ≤ 3;"";
                    IsEmpty(title) = 1 and WordCount(fullname)  ≤ 2;"";
                    IsEmpty(title) =1 and WordCount(fullname) >2;MiddleWords(fullname;2;1);
                    IsEmpty(title) = 0 and WordCount(fullname)  ≤ 3;"";
                    IsEmpty(title)=0 and IsEmpty(Suffix)=0 and WordCount(fullname) ≤ 4;"";
                    IsEmpty(title)=0 and IsEmpty(Suffix)=1 and WordCount(fullname) ≤ 3;"";
                    IsEmpty(title) =0 and WordCount(fullname) >3;MiddleWords(fullname;3;1);
                    IsEmpty(title)=0 and IsEmpty(Suffix)=0 and WordCount(fullname) ≤ 4;"";
                   
            )

            • 3. Re: Extracting Names
              SusanLazear

              Thank you to both of you who answered me. You gave me cause to go in and clean up as much as possible before pursuing further on this. Now, I have cleaned up 12,000 names, but it is still not perfect.  There are variable number of words in the current name field, even two names at times (don't ask... too many people maintaining the database, without enough forethought.

               

              So, I think the best thing to do now is to pull the first and last name, which is what I have working, as well as the Title.

              I can go in and hand-fix the middle name when necessary.

               

              My current problem is that now that I have first and last names pulled off, I can't edit those fields nor use them for future entries, as they are calculations. How do I handle this? My goal is to get rid of the single field that contains the long multiple-word name.

               

              Susan

              • 4. Re: Extracting Names
                Sorbsbuster

                If you have the component calculations working correctly (or at least 'acceptably') you could define a text field for each of thiose names, and use the Replace Filed Contents menu function to set that calculated value into each of the new text fields.  Then you have what was a calculation as a manipulatable text field.

                Same result would be achieved by setting its value by Replace Filed Contents (by calculation) to be = the equivalent calculated field.