3 Replies Latest reply on Dec 6, 2012 6:53 AM by JimMac

    How do I count the words to a coma?

    illbe

      Title

      How do I count the words to a coma?

      Post

           OK, once again I am in word count hell. I have an external db that has names all in one field (God I hate that!). I have a set of simple scripts that successfully pull out the last name, first name, and suffux (JR., SR, etc.). This seems to work fine until I am face with a business name like: BULLS EYE MARKSMAN, LLC, The, or worse yet: DOBSON II, AC NMN.

           I'm not sure how the heck to parse this weirdness.

           Here is my first name script:

           Case (
           Position ( name ; ","; 1; 1) ≠ 0  and MiddleWords(name; 2;1)="JR"; MiddleWords(name; 3;1);
           Position ( name ; ","; 1; 1) ≠ 0  and MiddleWords(name; 2;1)="SR"; MiddleWords(name; 3;1);
           Position ( name ; ","; 1; 1) ≠ 0  and MiddleWords(name; 2;1)="II"; MiddleWords(name; 3;1);
           Position ( name ; ","; 1; 1) ≠ 0  and MiddleWords(name; 2;1)="III"; MiddleWords(name; 3;1);
           Position ( name ; ","; 1; 1) ≠ 0; MiddleWords(name; 2;1)
           )

           Which works fine except for the above

           Here is my lanst name script:

            

           If ( Position ( name ; ","; 1; 1) = 0; LeftWords ( name ; 10 ) ;   LeftWords ( name ; 1 ))

           Which also works fine except for the above

           Ultimately I need to have a result like: Last Name:BULLS EYE MARKSMAN First Name:LLC not Last Name:BULLS First Name:EYE.

           My aplologies for picking so many brains lately but even though my FM knowledge is getting stonger my head may explode :-)

            

           Thank you in advance

            

           Tom

            

        • 1. Re: How do I count the words to a coma?
          JimMac

               You are fighting a "no win" battle.  The possibilities are nearly limitless plus add the human typing error.  The way most people do it is parse the input , use value lists or return validation error messages till you train the inputters,

               But....

               One way i have handled the Last Name, First Name, Company name issue is by dual use fields.

               Example....

          First Name  [blank for Company Names]

          Last Name/Company Name [one field dual purpose]

               Then i add a "show example" button that lets a user see...[ say a layout mailing address from his entry to verify that this is what he will see]

               This has solve about 99% of my data entry problems.

               Jim....

                

          • 2. Re: How do I count the words to a coma?
            illbe

                 Thank you Jim, I will certainly take your advice in the future.

                 The issue I have (and it's a big one :-)) is that the data I am given is in a format I can't change, which may explain why I'm going bald.

                  

                 All the best

                  

                 Tom

            • 3. Re: How do I count the words to a coma?
              JimMac

                   Tom, 

                   One way to minimize  your hair loss is to create a separate FMP database or table to "learn" the exceptions that your inputters nomally do.  You have listed a few exceptions to your script.  I can give you a list of many more.  The term "leaning program" has fallen out of popular use.

                   The idea....

                   You use your scripts to get most properly separted and then when an exception is found, add that exception to a new record in you new DB. Pass the input data a second time through your parseing scipts that now have the newly "learned" exception.  Then "Rinse and Repeat".

                   Thus....

                   You will "little by little" build a list of exceptions that you can program a general script using that "learned" list.

                   List...

                   JR SR II III IV Dr.  INC.  LLC. PPC. Rev,  Mr. Miss. Ms. Ing. PA, PE.    [ever growing]

                   You will still have to manually add the newly found exception to the list, but hair loss will go down from not having to reprogam your script.

                   Jim...