14 Replies Latest reply on Dec 5, 2012 4:26 AM by beverly

    Word Count for Parsing

    samjs

      I have a global field where the First and Last Name are entered, and I want to parse it out to the First and Last Name fields. If there are 3 words in the global field, I want to grab the 1st 2 words and place them in the First Name Field, and the Last word in the Last Name field.

      I've created a script using WORD COUNT, but it does not seem to be working for me. Does WORD COUNT not work on a global field?

       

      Thanks for your help!

        • 1. Re: Word Count for Parsing
          comment

          samjs wrote:

           

          Does WORD COUNT not work on a global field? 

           

          If you mean the WordCount() function, it will work on any text - including text in a global text field.

           

          samjs wrote:

           

          If there are 3 words in the global field, I want to grab the 1st 2 words and place them in the First Name Field, and the Last word in the Last Name field.

           

          What if the three words are "Mary Smith Jones"? I believe you'll get much better results if you ask your users to enter the name parts directly into the fields dedicated for them.

          • 2. Re: Word Count for Parsing
            eshoshin

            Michael Horak wrote:

             

            I believe you'll get much better results if you ask your users to enter the name parts directly into the fields dedicated for them.

             

            If only it were that easy!  LOL!  Yes, teaching best practices for data entry will save you many headaches down the road.

             

            However, if you already have data you need to split, then you can do this with a loop script and grabbing first word and filling FirstName field with it and so on.  The good news: it's automated and saves you all the time it takes to manually fix.  The bad news: you will need to audit the list to make sure it split correctly for the above mentioned issues (i.e. Mary Smith Jones, Mary Beth Smith, Tom Jones, Jr. and so on).  Still, auditing is much faster than retyping.

             

            I recommend using this custom function:

             

            http://www.briandunning.com/cf/1020

             

            Hope this helps!

             

            Ethan

            • 3. Re: Word Count for Parsing
              samjs

              Thanks for the great solutions!

              • 4. Re: Word Count for Parsing
                eshoshin

                In case you aren't able to use custom functions, you should be able to use this:

                 

                 

                Separate First Name:

                 

                Case(

                PatternCount ( Name ; " " ) = 2 ; Trim( Left ( Name ; Position ( Name ; " " ; 1 ; 2 ) )) ;

                PatternCount ( Name ; " " ) = 3 ; Trim( Left ( Name ; Position ( Name ; " " ; 1 ; 3 ) )) ;

                Trim( Left ( Name ; Position ( Name ; " " ; 1 ; 1 ) ))

                )

                 

                Separate Last Name:

                 

                Rightwords ( Name ; 1 )

                 

                 

                Well that should at least get you started.  Again, audit to make sure.

                 

                Ethan

                • 5. Re: Word Count for Parsing
                  comment

                  Chris Evert-Lloyd.

                  • 6. Re: Word Count for Parsing
                    mbraendle

                    Jürg von Allmen.
                    Jesus de Loyola.

                    • 7. Re: Word Count for Parsing
                      eshoshin

                      That one will fill correctly based on my above script.

                      • 8. Re: Word Count for Parsing
                        eshoshin

                        Hence why I said to audit. You can't script for everything in this case.

                         

                        Here's an idea: rather than trying to find an outlier that the above script will error on, you could actually contribute something helpful to the OP.

                        • 9. Re: Word Count for Parsing

                          Eshoshin said, "Here's an idea: rather than trying to find an outlier that the above script will error on, you could actually contribute something helpful to the OP."

                           

                          Wow, quite rude actually.  BTW, identifying ways a calculation or process might break IS contributing ... you just mean you don't like Martin's contribution and the reason why is obvious to everyone.  I suppose you won't like mine either!

                          • 10. Re: Word Count for Parsing
                            eshoshin

                            Identifying way a calculation or process might break is helpful, but without suggesting a way to account for it isn't.  Problem with a proposed solution is contributing.

                             

                            I mentioned several times prior that auditing is required.  I assumed this statement would suffice for situations like Martin mentioned.

                             

                            From my initial comment: "...you will need to audit the list to make sure it split correctly..."

                             

                            Even one of my suggestions would fill incorrectly (i.e. Tom Jones, Jr.).  Hence why I wrote "Well that should at least get you started.  Again, audit to make sure."

                            • 11. Re: Word Count for Parsing
                              eshoshin

                              On a second read, you're right: that last part was rude and not needed.  Apologies to Martin.  This is what I get for staying up too late and reading forums. Anyone know the emoticon for facepalm?

                              • 12. Re: Word Count for Parsing
                                mbraendle

                                Ethan, sorry for having put additional fuel into the fire. I was just a little astonished that after your excellent suggestion of Andrew Butt's, Drewsol custom function you made a step backwards (for just 2- and 3-word names. Especially Spanish and Dutch names may have more than 3 words).

                                 

                                Since Andrew's custom function is not recursive, it may also be implemented as a simple calculation field.

                                • 13. Re: Word Count for Parsing
                                  eshoshin

                                  MartinBraendle wrote:

                                   

                                  Since Andrew's custom function is not recursive, it may also be implemented as a simple calculation field.

                                   

                                  I was not aware of this.  I use the custom function and hadn't tried to implement another way.  I sometimes forget those not on FM Advanced can't add custom functions.  I was just trying to give a second option in case the OP wasn't able to use it.

                                   

                                  No need to apologize to me, it is I who needs to apologize -- I'm sorry.  I added nothing to the discussion with that last part.  -2 internets for me.

                                  • 14. Re: Word Count for Parsing
                                    beverly

                                    This is an old thread, and link to an old database by a dear departed FM developer. Lynn Bradford's database for name parsing covers just about all. That it's flexible for you to add "exceptions" makes it really well done.

                                     

                                    <http://attic.fmforums.com/forum/showpost.php?post/264788/>

                                     

                                    Beverly