FileMaker Pro Custom Functions

/* fnNameParser™ v1.9 Copyright © 2009 Drewsol GNU Lesser General Public License: This function is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. You should have received a copy of the GNU Lesser General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. This CF parses specified name parts (first, last, middle) from a single name field for the flexibility of sorting and/or display. fnNameStraight now handles Spanish names, and some middle-eastern names. NameParser™ ( name ; type ) - name = any name field - type = "First", "Middle", "Last", "FirstLast", "FirstMLast", "FirstMiddleLast", "FirstL", "FLast", "LastFirst", "LastFirstM", "initials", "initialslong", "namestraight" ChangeLog: v1.9 - Changed name from fnNameStraight™ to fnNameParser™. v1.8 - "Miss" prefix included. v1.7 - Better handling of single-word names. Examples: Martin Luther King, Jr.: Francisco de la Hoya: Andrew Smith-Jones: ------------------------------------------------------------------------------------------------------------------------- namestraight Martin Luther King Francisco de la Hoya Andrew Smith-Jones first Martin Francisco Andrew middle Luther last King de la Hoya Smith-Jones firstlast Martin King Francisco de la Hoya Andrew Smith-Jones firstmlast Martin L. King Francisco de la Hoya Andrew Smith-Jones firstmiddlelast Martin Luther King Francisco de la Hoya Andrew Smith-Jones firstl Martin K. Francisco D. Andrew S. flast M. King F. de la Hoya A. Smith-Jones lastfirst King, Martin de la Hoya, Francisco Smith-Jones, Andrew lastfirstm King, Martin L. de la Hoya, Francisco Smith-Jones, Andrew initials MK FD AS initialslong MLK FD AS */ Let ( [ // ------- FILTER 1: Filter Normal Characters, Spaces, and Commas Only (No Periods) ------- // Substitute Hyphen with String. z = "zzzzzzz" ; filter1 = Substitute ( Filter ( name ; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ,'-" ) ; [ "-" ; z ] ) ; wordcount_v1 = WordCount (filter1) ; // ------- FILTER 2: Exclude Personal and Professional Prefixes and Suffixes (1st Sweep) ------- filter2 = Case ( LeftWords ( filter1 ; 1 ) = "Mr" or LeftWords ( filter1 ; 1 ) = "Miss" or LeftWords ( filter1 ; 1 ) = "Mrs" or LeftWords ( filter1 ; 1 ) = "Ms" or LeftWords ( filter1 ; 1 ) = "Dr" or LeftWords ( filter1 ; 1 ) = "PhD" or LeftWords ( filter1 ; 1 ) = "MD" or LeftWords ( filter1 ; 1 ) = "JD" or LeftWords ( filter1 ; 1 ) = "DDS" or LeftWords ( filter1 ; 1 ) = "Jr" and wordcount_v1 ≥ 2 or LeftWords ( filter1 ; 1 ) = "Sr" or LeftWords ( filter1 ; 1 ) = "Sra" or LeftWords ( filter1 ; 1 ) = "Srta" or LeftWords ( filter1 ; 1 ) = "Lic" or LeftWords ( filter1 ; 1 ) = "Sir"; MiddleWords ( filter1 ; 2 ; wordcount_v1 - 1 ) ; filter1 ) ; wordcount_v1 = WordCount ( filter2 ) ; // ------- FILTER 3: Exclude Personal and Professional Prefixes and Suffixes (2nd Sweep) ------- filter3 = Case ( RightWords ( filter2 ; 1 ) = "Jr" and wordcount_v1 ≥ 2 or RightWords ( filter2 ; 1 ) = "Sr" or RightWords ( filter2 ; 1 ) = "PhD" or RightWords ( filter2 ; 1 ) = "MD" or RightWords ( filter2 ; 1 ) = "JD" or RightWords ( filter2 ; 1 ) = "DDS" or RightWords ( filter2 ; 1 ) = "MBA" or RightWords ( filter2 ; 1 ) = "CPA" or RightWords ( filter2 ; 1 ) = "CEO" or RightWords ( filter2 ; 1 ) = "CFO" or RightWords ( filter2 ; 1 ) = "CTO" or RightWords ( filter2 ; 1 ) = "CTO" or RightWords ( filter2 ; 1 ) = "COO"; MiddleWords ( filter2 ; 1 ; wordcount_v1 - 1 ) ; filter2 ) ; wordcount_v1 = WordCount ( filter3 ) ; firstcomma = Position ( filter3 ; "," ; 1 ; 1 ) ; lastword = Right ( name ; Length ( name ) - Position ( name ; " " ; Length ( name ) ; -1 ) ) ; romannumeral = Case ( RightWords ( filter3 ; 1 ) = "I" and PatternCount ( lastword ; "." ) = 0 or RightWords ( filter3 ; 1 ) = "II" or RightWords ( filter3 ; 1 ) = "III" or RightWords ( filter3 ; 1 ) = "IV" or RightWords ( filter3 ; 1 ) = "V" and PatternCount ( lastword ; "." ) = 0 or RightWords ( filter3 ; 1 ) = "VI" or RightWords ( filter3 ; 1 ) = "VII" or RightWords ( filter3 ; 1 ) = "VIII" or RightWords ( filter3 ; 1 ) = "IX" or RightWords ( filter3 ; 1 ) = "X"and PatternCount ( lastword ; "." ) = 0 ; "yes" ; "no" ) ; //Starting Word After Comma swac = WordCount ( Left ( filter3 ; Position ( filter3 ; "," ; 1 ; 1) ) ) + 1 ; // ------- FILTER 4: Exclude Roman Numeral Suffixes and Place Name In Sequential Order (First Middle Last) ------- filter4 = Case ( PatternCount ( filter3 ; "," ) > 0 and romannumeral = "no" ; MiddleWords ( filter3 ; swac ; wordcount_v1 - 1 ) & " " & Middle ( filter3 ; 1 ; firstcomma - 1 ) ; PatternCount ( filter3 ; "," ) > 0 and romannumeral = "yes" ; MiddleWords ( filter3 ; swac ; wordcount_v1 - swac ) & " " & Middle ( filter3 ; 1 ; firstcomma - 1 ) ; PatternCount ( filter3 ; "," ) = 0 and romannumeral = "yes" ; LeftWords ( filter3 ; 1) & " " & MiddleWords (filter3 ; 2 ; wordcount_v1 - 2 ) ; filter3 ) ; wordcount_v1 = WordCount ( filter4 ) ; // ------- Disect Name Parts ------- w1 = Case ( Length ( MiddleWords ( filter4 ; 1 ; 1 ) ) = 1 ; Replace ( MiddleWords ( filter4 ; 1 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 1 ; 1 ) ) ; MiddleWords ( filter4 ; 1 ; 1 ) & "." ) ; MiddleWords ( filter4 ; 1 ; 1 ) ) ; w2 = Case ( Length ( MiddleWords ( filter4 ; 2 ; 1 ) ) = 1 ; Replace ( MiddleWords ( filter4 ; 2 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 2 ; 1 ) ) ; MiddleWords ( filter4 ; 2 ; 1 ) & "." ) ; MiddleWords ( filter4 ; 2 ; 1 ) ) ; w3 = Case ( Length ( MiddleWords ( filter4 ; 3 ; 1 ) ) = 1 ; Replace ( MiddleWords ( filter4 ; 3 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 3 ; 1 ) ) ; MiddleWords ( filter4 ; 3 ; 1 ) & "." ) ; MiddleWords ( filter4 ; 3 ; 1 ) ) ; w4 = Case ( Length ( MiddleWords ( filter4 ; 4 ; 1 ) ) = 1 ; Replace ( MiddleWords ( filter4 ; 4 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 4 ; 1 ) ) ; MiddleWords ( filter4 ; 4 ; 1 ) & "." ) ; MiddleWords ( filter4 ; 4 ; 1 ) ) ; w5 = Case ( Length ( MiddleWords ( filter4 ; 5 ; 1 ) ) = 1 ; Replace ( MiddleWords ( filter4 ; 5 ; 1 ) ; 1 ; Length ( MiddleWords ( filter4 ; 5 ; 1 ) ) ; MiddleWords ( filter4 ; 5 ; 1 ) & "." ) ; MiddleWords ( filter4 ; 5 ; 1 ) ) ; // ------- FILTER 5: Reconstruct Name Parts ------- filter5 = Case ( wordcount_v1 = 5 ; w1 & " " & w2 & " " & w3 & " " & w4 & " " & w5 ; wordcount_v1 = 4 ; w1 & " " & w2 & " " & w3 & " " & w4 ; wordcount_v1 = 3 ; w1 & " " & w2 & " " & w3 ; wordcount_v1 = 2 ; w1 & " " & w2 ; wordcount_v1 = 1 ; w1 ; filter4 ) ; // ------- FILTER 6: Exclude Personal and Professional Prefixes and Suffixes (3rd Sweep) ------- filter6 = Case ( RightWords ( filter5 ; 1 ) = "Jr" and wordcount_v1 ≥ 2 or RightWords ( filter5 ; 1 ) = "Sr" or RightWords ( filter5 ; 1 ) = "PhD" or RightWords ( filter5 ; 1 ) = "DDS"; MiddleWords ( filter5 ; 1 ; wordcount_v1 - 1 ) ; filter5 ) ; wordcount_v1 = WordCount ( filter6 ) ; // ------- VARIATIONS: ------- // Function Title (Straight Name) namestraight = Substitute ( filter6 ; [ z ; "-" ] ) ; // Single Name Parts (first, middle, last) first = Substitute ( LeftWords (filter6 ; 1) ; [ z ; "-" ] ) ; last = Case ( MiddleWords ( filter6 ; wordcount_v1 - 2; 1 ) = "de" and MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "la" ; Substitute ( RightWords ( filter6 ; 3 ) ; [ z ; "-" ] ) ; MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "de" or MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "del" or MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "le" or MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "la" or MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "di" or MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "don" or MiddleWords ( filter6 ; wordcount_v1 - 1; 1 ) = "al"; MiddleWords ( filter6 ; wordcount_v1 - 1; 2 ) ; Substitute ( RightWords ( filter6 ; 1 ) ; [ z ; "-" ] ) ) ; middle = Let ( [ lastraw = Substitute ( last ; [ "-" ; z ] ) ; wordcount_middle = wordcount_v1 - ( WordCount ( first ) + WordCount ( lastraw ) ) ] ; Case ( Length ( MiddleWords ( filter6 ; 2 ; wordcount_middle ) ) = 1 ; MiddleWords ( filter6 ; 2 ; wordcount_middle ) & "." ; wordcount_v1 ≥ 3 ; MiddleWords ( filter6 ; 2 ; wordcount_middle ) ; "" ) ) ; // Recompilations firstlast = Case ( WordCount ( filter6 ) = 1 ; filter6 ; first & " " & last ) ; firstmlast = Case ( WordCount ( filter6 ) = 1 ; filter6 ; middle <> "" ; first & " " & Left (middle ; 1 ) & "." & " " & last ; first & " " & last ) ; firstmiddlelast = Case ( WordCount ( filter6 ) = 1 ; filter6 ; Substitute ( filter6 ; [ z ; "-" ] ) ) ; firstl = Case ( WordCount ( filter6 ) = 1 ; filter6 ; first & " " & Upper ( Left ( last; 1 ) ) & "." ) ; flast = Case ( WordCount ( filter6 ) = 1 ; filter6 ; Left ( first; 1 ) & "." & " " & last ) ; lastfirst = Case ( WordCount ( filter6 ) = 1 ; filter6 ; last & "," & " " & first ) ; lastfirstm = Case ( WordCount ( filter6 ) = 1 ; filter6 ; middle <> "" ; last & "," & " " & first & " " & Left (middle ; 1 ) & "." ; last & "," & " " & first ) ; // Initials initials = Upper ( Case ( WordCount ( filter6 ) = 1 ; Left ( filter6 ; 1 ) ; wordcount_v1 = 1 ; Left ( filter6 ; 1 ) ; Left ( first ; 1 ) & Left ( last ; 1 ) ) ) ; initialslong = Upper ( Case ( WordCount ( filter6 ) = 1 ; Left ( filter6 ; 1 ) ; wordcount_v1 = 1 ; Left ( filter6 ; 1 ) ; Left ( first ; 1 ) & Left ( middle ; 1 ) & Left ( last ; 1 ) ) ) ] ; Case ( // Parts type = "First"; first ; type = "Middle" ; middle ; type = "Last" ; last ; // Recompilations type = "FirstLast" ; firstlast ; type = "FirstMLast" ; firstmlast ; type = "FirstMiddleLast" ; namestraight ; type = "FirstL" ; firstl ; type = "FLast" ; flast ; type = "LastFirst" ; lastfirst ; type = "LastFirstM" ; lastfirstm ; type = "NameStraight" ; namestraight ; // Initials type = "initials" ; initials ; type = "initialslong" ; initialslong ) ) /*End Let*/
 Then copy & paste into FileMaker Advanced's Edit Custom Function window.