1 2 Previous Next 25 Replies Latest reply on Jul 15, 2009 12:45 PM by comment_1

    Auto Format a Phone Number Field Upon Entry



      Auto Format a Phone Number Field Upon Entry




      I'd like to be able to enter in the 10 numbers in my "Phone Number" field like 3124809927 and it auto format to (312) 480-9927. I tried follow Answer ID 5839 but I can't seem to get it to work. Is there a way to achieve this? I would assume this would be similar to entering in numbers 489382727 into a social security field and it return 489-38-2727.



        • 1. Re: Auto Format a Phone Number Field Upon Entry

          I got this off the FM Support web page



          FileMaker Pro 6, FileMaker Pro 5.x, FileMaker Pro 4.x, FileMaker Pro

          FileMaker Pro does not have a built in number format for telephone numbers, but you can use text calculations to create custom formatting. The examples below will all yield numbers in the format (408) 727-9004 (and/ or 727-9004 when applicable); choose the best solution for you based on the way your existing telephone numbers have been entered.


          Important Notes
          a. All calculations in this article use functions in FileMaker Pro 3.0 and later versions unless noted.

          b. For clarity, a carat character (^) is used wherever there should be a space in a formula.

          c. All of the calculations should be set to return a Text result, not a Number result. Setting them to a number will strip out the parentheses, spaces and dashes.

          d. All of the examples in this article assume that you have the following field:
          PhoneNumber (text)

          e. This article does not address international numbers that include the country code.

          f. In FileMaker Pro 3.0 or later, you can either create a new calculation called PhoneNumber2 or use the Replace function and replace the contents of PhoneNumber with the results of your desired calculation. ***Always make a backup of your database before using the Replace function, as it is NOT undoable. The calculations are:




          If all phone numbers are currently in the format 408 727 9004:

          For FileMaker Pro 3.0 or later versions:

          "(" & LeftWords(PhoneNumber,1) & ")^" & MiddleWords(PhoneNumber, 2, 1) & "-" & RightWords(PhoneNumber, 1)


          For all versions of FileMaker Pro:

          "(" & Left(PhoneNumber, 3) & ")^" & Middle(PhoneNumber, 5, 3) & "-" & Right(PhoneNumber, 4)


          If some records have been entered as 408 727 9004 and others as 727 9004 (i.e., without the Area Code):

          If(Length(PhoneNumber) < 9, Left(PhoneNumber,3) & "-" & Right(PhoneNumber,4),
          "(" & Left(PhoneNumber,3) & ")^" & Middle(PhoneNumber,5,3) & "-" & Right(PhoneNumber,4))


          This will format 408 727 9004 as (408) 727-9004 and 727 9004 as 727-9004.

          This calculation checks the number of characters in the PhoneNumber field, and if there are more than 8 it assumes that there is an area code.

          If data has not been entered in a consistent format:
          If numbers have been entered many different ways, the calculation will be complicated and you should use it only as a separate field, not in a Replace action.


          You may not be able to automatically format all phone numbers, but the calculation below accounts for some of the most likely variations and flags many others for easy identification and repair.

          This approach uses a calculation that counts the number of characters and returns a result based on the number of characters in the phone number field, including spaces, dashes, and parentheses. Thus,

          (408) 727-9004 (14 characters)
          408-727-9004 (12 characters)
          4087279004 (10 characters)
          727-9004 (8 characters)
          727 9004 (8 characters)

          will be correctly formatted; entries with a different character count will result in the word 'error', and performing a Find for this word will bring up any records that will need special attention.


          The calculation is:

          If(Length(PhoneNumber) < 9, Left(PhoneNumber,3) & "-" & Right(PhoneNumber,4), If(Length(PhoneNumber)=10, "(" & Left(PhoneNumber,3) & ")^" & Middle(PhoneNumber, 4, 3) & "-" & Right(PhoneNumber,4),

          If(Length(PhoneNumber)=12, "(" & Left(PhoneNumber,3) & ")^" & Middle(PhoneNumber, 5, 3) & "-" & Right(PhoneNumber,4),

          If(Length(PhoneNumber)=14, PhoneNumber, "error"))))

          If you are using FileMaker Pro 5.0 or later, you can use the Case function for this solution. The Case function generally allows you to write calculations a little more clearly when you need to select one of several different outcomes depending upon input values.

          For example, the full calculation written above can be rewritten as:

          Case(Length(PhoneNumber) = 7;Left(PhoneNumber;3) & "-" & Right(PhoneNumber;4);

          (Length(PhoneNumber) = 10); "(" & Left(PhoneNumber;3) & ") " & Middle(PhoneNumber; 4; 3) & "-" & Right(PhoneNumber;4);


          Note: You can put returns in your calculations as above to make the calculation easier to read. These returns will not effect the outcome of the calculation.


          • 2. Re: Auto Format a Phone Number Field Upon Entry

            This is now working great but now I realize I need more formatting options. More-so then typing the numbers in, it seems that we're copying and pasting them from emails. These are usually formated with -'s. This formula doesn't like that obviously. Is there anything we can add to it that will allow us to past this format "123-456-7890" and it will do this auto formatting?



            • 3. Re: Auto Format a Phone Number Field Upon Entry

              This article goes back aways. Try the following as an auto-entered calculated value with the "do not replace existing value..." option cleared:


              Let (ph = GetasNumber(Phonenumber);

                     TextFormatRemove(Case (Length(ph) = 7; Left (ph; 3) & "-" & right(ph;4);

                                                        Length(ph) = 10; left(ph; 3) & "-" & middle (ph; 4; 3) & "-" Right (ph; 4);



              PhoneNumber is the name of your text field (If you are using a recent version of FMP, you can replace it with "Self") to which you've set up this auto-enter option.

              GetasNumber strips out any non numeric characters that get entered.

              TextFormatRemove eliminates any undesirable text styles that might otherwise get pasted into the text field along with the number.

              If the number of digits in your phone number isn't 7 or 10, the expression will simply return the original text, but with any pasted text styles removed.

              • 4. Re: Auto Format a Phone Number Field Upon Entry

                How do I get rid of the ^ symbol for the space? The following is the result of the calculation:


                result: (888)^999-5555 




                If(Length(PhoneNumber)=14; "(" & Left(PhoneNumber;3)& ")"& "  "&Middle(PhoneNumber; 3;4) & "-" & Right(PhoneNumber;4))


                Thank you in advance for your help.


                • 5. Re: Auto Format a Phone Number Field Upon Entry

                  1. Filemaker has no symbol for a space.



                  2. Your calculation =

                  If(Length(PhoneNumber)=14; "(" & Left(PhoneNumber;3)& ")"& "  "&Middle(PhoneNumber; 3;4) & "-" & Right(PhoneNumber;4))


                  cannot return a result of "(888)^999-5555" under any circumstances.

                  • 6. Re: Auto Format a Phone Number Field Upon Entry

                    I am puzzled.  I created a test file with 3 fields containing 1 record. 


                    1. PhoneNameFormat

                    2. Phone Number (999)^999-9999 

                    3. Notes


                    Would you give me a clue what to look for regarding the unwelcome carat?  

                    I Created PhoneNameFormat as a calculation field; result of calculation as a text field.  

                    PhoneNumber as a text field. 


                    If(Length(PhoneNumber)=10; "(" & Left(PhoneNumber;3)& ")"& "  "&Middle(PhoneNumber; 3;4) & "-" & Right(PhoneNumber;4)) 


                    • 7. Re: Auto Format a Phone Number Field Upon Entry

                      I believe the carat is there because you have entered it into Phone Number, and your formula doesn't take it out.


                      You haven't said what exactly you want as the result, but I am guessing i's something like:



                      Let ( [
                      num = Filter ( PhoneNumber ; "0123456789" )
                      ] ;
                      Case (
                      Length ( num ) = 10 ;
                      "(" & Left ( num ; 3 ) & ") " & Middle ( num ; 4 ; 3 ) & "-" & Right ( num ; 4 ) ;




                      • 8. Re: Auto Format a Phone Number Field Upon Entry

                        You are correct.  I found the carart in the PhoneNumber calculation and have removed it.
                        The format I want is (xxx) xxx-xxxx
                        It is working fine since I removed the carart, but I have another question.
                        ()- appears in the PhoneNumber field when I add a new record. I need to delete the ()- then type in the phone# which appears as I want it to e.g. (203) 535-6000.
                        However, in the  the PhoneNameFormat field the number looks as follows:((20)  03) -6000

                        Unsure if that is ok. I appreciate your patience.  This is work related. I have over 3 thousand phone numbers that I entered as (xxx) xxx-xxxx

                        without any mapping so this will be a time saver when entering data.  I have FileMaker Pro 10 if that makes any difference.

                        • 9. Re: Auto Format a Phone Number Field Upon Entry
                             Have you tried using the formula I have suggested?
                          • 10. Re: Auto Format a Phone Number Field Upon Entry

                            Yes, but the field num wasn't recognized.  I changed it to PhoneNumber still didn't work.  Remember I am new to Mac coming from windows environment.

                            But I am pleased to let you know that I it is working fine now.  

                            I wished I understood the theory/language it would make it easier for me to spot my mistakes.  Took almost the whole day I am ashamed to say.

                            Now that it is working in my test file do what do I do when I am entering a new phone# so this calculation doesn't wipe out the existing phone numbers that are manually formated as (xxx) xxx-xxxx?

                            Again, thanks for all your help.



                            • 11. Re: Auto Format a Phone Number Field Upon Entry

                              joan wrote:

                              Took almost the whole day I am ashamed to say.



                              No shame there, Joan.  That's what it takes to succeed in this business - the ability to stick to it until it gets figured out.  Some people call it bullheadedness but it is actually tenacity and it's something to be proud of. :smileywink:

                              • 12. Re: Auto Format a Phone Number Field Upon Entry

                                I am still not sure what you want to accomplish. If you have a file with existing phone numbers, you have two choices: (1) keep your data as is and define a calculation field for displaying the formatted phone numbers or (2) format the numbers within the existing field by modifying your existing data.


                                The same formula can accomplish both, only the procedure is a bit different. The formula I have posted will re-format previously formatted numbers - it only looks at the digits of the input and ignores all other characters (if you have a number like "(012) 345-6789 ABC", it WILL remove the "ABC" extension!).

                                • 13. Re: Auto Format a Phone Number Field Upon Entry

                                  Try to clarify:

                                  I have a Cases Form that, with the exception of fields that are unique to each record)e.g., File ID#; Claimant Name;Plaintiff Attorney;Date of Loss), I have look-up fields that contain the phone and fax numbers of the parties (plaintiff firm, defense attorney firm, adjuster at insurance co.) that I have to date already entered manually without any calculation or mapping.

                                  So, I need to retain those phone numbers I have already entered and

                                  Use your great calculation format for all the new records I shall be entering.

                                  Reason: I am attempting to speed-up data entry. I have arthritis so am always looking ways to held help make data entry easier.


                                  • 14. Re: Auto Format a Phone Number Field Upon Entry

                                    The format I used when entering exsisting data was:

                                    (203) 876-60000 

                                    1 2 Previous Next