1 Reply Latest reply on Jun 30, 2011 9:09 AM by philmodjunk

    Extracting a text string on pattern match

    DavidLake

      Title

      Extracting a text string on pattern match

      Post

      I have a comments field with a PO number occassionally entered within the data.  The PO Pattern is always ##-####.  I want to extract just the PO number from the comment and put it in it's own field.

        • 1. Re: Extracting a text string on pattern match
          philmodjunk

          If you could guaranteee that the comment field never had any other numeric digits, this would be easy to extract:

          Filter ( CommentsField ; -1234567890 )

          Would likely filter out all but that value. (though you might also need to remove leading and trailing - ) Since you might have additional numeric digits in your comment field, however, you'll need a more sophisticated approach to find this pattern.

          Set Variable [$Length ; value: Length ( yourTable::Comment ) ]
          Set Variable [$Comment ; Value : YourTable::comment ]
          Loop
             Set Variable [$I ; Value: $I + 1]
             Exit Loop If [$I > $Length ]
             Exit Loop If [ Let ( [ $PO1 = Middle ( $Comment ; $I ; 2 ) ; $PO2 = Middle ( $Comment ; $I + 3 ; 4 ) ]  ; 
                                ( Filter ( $PO1 ; 1234567890 ) = $PO1 ) AND ( Middle ( $Comment ; $I + 2 ) = "-" ) AND 
                                ( Filter ( $PO2 ; 1234567890 ) = $PO2 ) And ( Length ( $PO2 ) = 2 ) ) ]
          End Loop
          If [$I < $Length // PO exists and was found ]
             Set Field [YourTable::PO ; $PO1 & "-" & $PO2 ]
          End If

          This script can be performed by an OnObjectSave script trigger set up on the comments field.

          It's also possible to convert the above algorithm into a recursive custom function to eliminate script and trigger, defining PO as either a calculation field or a text field with an auto-enter calculation that uses this custom function instead.