3 Replies Latest reply on Nov 21, 2010 5:44 PM by RickWhitelaw

    how to make 14 digit (xxx) xxx-xxxx phone # fields when entering phone #'s in a field



      how to make 14 digit (xxx) xxx-xxxx phone # fields when entering phone #'s in a field


      I need to know how to create the formula, value, or calculation for the 14 digit (xxx) xxx-xxxx phone # fields in my contacts table of my database. I want to be able to just type in the 10 digits of the phone numbers (including area code) by entering only the 10 digits. I just created a database and inputted all the contact phone numbers as straight numbers (xxxxxxxxxx) until I can get a response that will allow the data to display properly. Thanks for the help. 

      Also I would need to know in what particular area of FileMaker Pro 11 to place this information into and very specifically how to write out the formula or calculation or ??? (from A-Z). In effect I need a walk through.  I'm a newbie to File Maker. I would appreciate any help.



        • 1. Re: how to make 14 digit (xxx) xxx-xxxx phone # fields when entering phone #'s in a field

          I'll give you a basic calculation with each part explained, then discuss your options for implementing it.

          First you need to make sure that the data is strictly numeric, that no helpful person has input their own formattin along with the digits.

          Filter ( PhoneField ; "0123456789") will strip out all non numeric input.

          Now we should be sure that the correct number of digits has been input. (You can also set a validation rule for this). If it is, we'll add the formatting.

          And since we don't want to keep typing in the above expression more than once, we'll also use the let function:

          Let ( P = Filter ( PhoneField ; "0123456789" ) ; 
                  If ( Length ( P ) = 10 ; "(" & Left ( P ; 3 ) & ") " & Middle ( P ; 4 ; 3 ) & "-" & Right ( P ; 4 ) ; P )

          The Left, Middle and Right functions are text functions that can be used to extract specified sections of text from text in a field or variable. All these functions can be looked up in FileMaker help. (Looking up Functions in FileMaker help and spendig a few hours reading through the functions that are available can be a good way to increase your knowledge of what can be done with functions in FileMaker.)

          Now you have two options here for how you implement this expression. You can define a separate field of type calculation, set it to return text and you then input your phone number into PhoneField and your calculation field returns the formatted version. You can also define your phone field to be of type text and then specify the above calculation as an auto-entered calculation. If you also clear the the "Do not replace existing value..." option, you can type in an unformatted phone number and the system will automatically convert it to the formatted version in the same field.

          It's also possible to create a more sophisticated version of the above calculation to permit users to enter phone numbers both with and without area codes.

          • 2. Re: how to make 14 digit (xxx) xxx-xxxx phone # fields when entering phone #'s in a field

            Create a field called phone number and define it as text.  Under options auto enter a calculated value below:


            Let (
            cleanphone=Filter ( PhoneNumber;"0123456789"


            Case (
            Length(PhoneNumber) = 10;

            "(" & Left ( cleanphone ;3) & ")" &
            Middle ( cleanphone ; 4; 3 ) & "-" &
            Right ( cleanphone ;4 ) ;

            this will work when the user enters a 10 diget phone number.  You could set up some controls that dispaly a message when the user does not enter a 10 digit number.

            • 3. Re: how to make 14 digit (xxx) xxx-xxxx phone # fields when entering phone #'s in a field

              Here's another one that works for me: store it as a custom function. "Left (input; 1 ) ≠ "+"" checks for international country codes. Use this function as auto-entered on the phone number field (text) as Phone Format(self).

              Phone Format(input)

              Case(  Length ( Filter (input ; "0123456789" ) )   < 10  and Left (input; 1 ) ≠ "+"; "Enter 10 digits!";Length ( Filter (input; "0123456789" ) )   = 10; "(" &  Left ( Filter ( input; "0123456789" ) ; 3 ) & ") " & Middle ( Filter ( input; "0123456789" ) ; 4 ; 3 ) &"-" & Middle ( Filter ( input;"0123456789" ) ; 7 ; 4 );input )