2 Replies Latest reply on May 2, 2012 6:01 AM by philmodjunk

    Trim Phone Number and wildcard search

    JoeMintz

      Title

      Trim Phone Number and wildcard search

      Post

      So I am trying to trim and wildcard search a phone number, so that it will find it by looking at the numbers in that specific order.

      For example, whether someone enters (123) 456 7890, 123-456-7890, (123)-456-7890 or 123 456 7890, I want to be able to first:

      a) Eliminate all special characters ), (, -, etc

      b) Trim(All) (Which I already know how to do)

      To make the result: 1234567890

      c) Then I want to insert * before and after every digit so it results in this: *1*2*3*4*5*6*7*8*9*0

      I've tested the result, and this searches for the correct number regardless of how it was input in the system.

      Can anyone help me with steps a) and c)?

      Thank you.

        • 1. Re: Trim Phone Number and wildcard search
          bumper

          a and b use the same function (you don't need TrimAll as filter removes everything but the numbers, c is multiple substitutions :

           

          Substitute ( Filter ( Self ; "1234567890" ) ;

          [ 1 ; "*1" ] ; [ 2 ; "*2" ] ;  [ 3 ; "*3" ] ;  [ 4 ; "*4" ] ;  [ 5 ; "*5" ] ;  [ 6 ; "*6" ] ; [ 7 ; "*7" ] ;  [ 8 ; "*8" ] ;  [ 9 ; "*9" ] ; [ 0 ; "*0" ] )

           

          put this in the data entry field (text) auto enter, Calculated value, uncheck Do not replace existing value of field (if any)

          Note: this is all one calcuation, just copy and paste it into your data entry field

          • 2. Re: Trim Phone Number and wildcard search
            philmodjunk

            Or perhaps as a script:

            Enter Find Mode [] //clear pause check box
            Set Field [YourTable::PhoneField ; Substitute ( Filter ( YourTable::GlobalField ; "1234567890" ) ;
                            [ 1 ; "*1" ] ; [ 2 ; "*2" ] ;  [ 3 ; "*3" ] ;  [ 4 ; "*4" ] ;  [ 5 ; "*5" ] ;  [ 6 ; "*6" ] ; [ 7 ; "*7" ] ;  [ 8 ; "*8" ] ;  [ 9 ; "*9" ] ; [ 0 ; "*0" ] ) ]
            Set Error Capture [on]
            Perform Find []

            You'd enter a phone number into the global field and then perform this script to find records.

            There are also ways to set up auto-enter calculations for phone numbers such that each number entered is auto-matically formatted with spaces, (), - etc. in exactly the same format. A Replace Fields calculation can use the same calculation to "clean up" existing records.

            PS. You can also use the expression as an auto-enter calc as Bumper suggested on the global field and then the set field script step need only copy the contents of the global field into the phone number field.