5 Replies Latest reply on Jun 30, 2011 5:44 AM by ninja

    Word functions - Hyphens and other word seperators



      Word functions - Hyphens and other word seperators


      I've read through many posts trying to find an answer or work around.  I'm trying to extract the middle word(s) out of a field and hyphens and/or word count is messing my calculation.  Below are example (FNAME - PO_FILE) of what I'm working with, the underlined portion is what I am trying to retrieve and place into another field (FNAME - PO_NO)

      ABC DEF-HIJ PO 123456-00.PDF

      ABC DEF-HIJ PO 123456.PDF

      ABCDEF GHI-JKL PO 123456.PDF       

      ABCDEF GHI-JKL PO 123456-00.PDF

      ABCDEF GHI-JKL PO 123456-00 ABCD.PDF

      Any help will be appreciated.  This maybe impossible, but I figured one of the kind people here may know of a solution. 

      Using Filemaker Pro 5, don't laugh it still works great.

        • 1. Re: Word functions - Hyphens and other word seperators

          It's possible, but maybe not in FMP5...

          The filtervalues function would come in quite handy, followed by a cutoff of the resultant leading hyphen.

          Don't quite know how I would do it in FMP5 though...never used it.  FilterValues came into play in FMP7.

          Anyone else's thoughts?

          • 2. Re: Word functions - Hyphens and other word seperators

            FilterValues? Perhaps just Filter.... works in newer FileMaker versions, but isn't an option in Filemaker 5.

            Filter ( text field ; -1234567890 ) would filter the contents of your original text down to just the hyphen and numeric digits. Which would just leave checking for and removing a leading hyphen to produce the results you've specified. Simple enough to set up as a calculation field and no script required.

            In FileMaker 5, it can be done, but I'd use a script that looped through the text one character at a time. you can use pattern count to identify the occurrence of the first numeric digit to find the begginning of the text you want to extract: Patterncount ( "1234567890" , Middle ( TextField , gCharPos , 1 ) ) > 0 would detect the first numeric digit. A similar expression with the hyphen included could then allow a second loop to find theend of this text and then Middle could extract the actual text.

            Put all of that inside a third loop that moves from record to record and you have something that should extract the desired hyphenated numeric text.

            • 3. Re: Word functions - Hyphens and other word seperators

              Good call on filter vs filtervalues...but why use a simple function when you can make it more complex! :)

              Either way, it looks like it's time to upgrade to FMP7+ or begin the long manual data entry process.

              • 4. Re: Word functions - Hyphens and other word seperators

                No need to upgrade to a fancy-smancy version of FM….one line of code and 10 minutes testing.

                 If( WordCount(PO_FILE) = 6, MiddleWords( PO_FILE, 4 , 2 ) ,If( WordCount(PO_FILE) = 5, MiddleWords( PO_FILE, 4 , 1 ) , " " ) )

                 I can’t find the help file, but both the “-” and “.” act as separators within numerical values (example 123-456 or AB1.PDF) within a text field, making them 2 words.  However when used with text (abc-def or abc.pdf) it is defined as 1 word.  Once this was know the problem was easy to resolve using WordCount.

                 IF statements are great and have resolved a lot of short comings within FM.  Nested IF statements are even better.

                 I choose not to upgrade some 15+ years ago because of a rebuild requirement to upgrade.  Old school wins…from a “user” that has the ability to program…