AnsweredAssumed Answered

Searching for Phone Numbers with a Script

Question asked by BMyers on Sep 30, 2017
Latest reply on Oct 1, 2017 by beverly

I need to figure out how to write a script to do a search on multiple fields.  I’ve looked at the FMP manual and it’s not working.


This is a phone number search.  Let’s assume that I’m searching on the last four digits.  (Searching on a whole phone number is a different kettle of fish because of formatting, so let’s leave that out.)  Assume I have a table of Contacts and there are fields for different types of numbers: BusinessMain, BusinessDirect, Mobile, and Home.


1.  First, I understand that FM recommends that a phone number should be set up in File-->Manage Database as a text field rather than a number field.  FM doesn’t state why.  Anyone know?


2.  OK, on to the question.  I’m trying to use Quick Find but I’ve never been able to enter the last four digits into the Quick Find box and get FMP to locate the records.  This seems to be related to the formatting of the phone number.  If the formatting is (206) 555-1234 then FMP will not locate the records because it gets tripped up by the leading character before the last four digits.  Oddly, FMP will locate the records using Quick Find on 206 despite the leading character.  Does anyone know why?


3.  I saw a tip on the web that said to put the input text into a variable, then use that variable plus a leading and trailing wildcard asterisk to get FMP to ignore other characters.  So I did this:


  Create a search field.  Let’s call it Contacts::PhoneSearch


  Define a variable.  Set Variable [ $VarPhoneNum ; Value: Contacts::PhoneSearch ]


  Perform Quick Find [  “*” & $VarPhoneNum & “*” ]


Yet this doesn’t work.


I’ve also tried to do a Find and then use the Extend Found Set script step, but that doesn’t work either.  I’ve also tried the Find Matching Field script step.   I’ve spent waaay too much time looking on the web to do something that should be simple.  Can anyone help?