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?
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.
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.
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…
Nice one, RW.