8 Replies Latest reply on Aug 28, 2012 9:16 AM by philmodjunk

    Formatting Phone Numbers

    springhouse99

      Title

      Formatting Phone Numbers

      Post

      I am trying to format phone numbers.  I found a process in Filemaker help yet it will not allow me to save it as a calculation as it keeps saying the first ### feild cannot be found. I am a novice at this so maybe i am putting it in the wrong spot?

      Here is what it says to do:

      Step 1: Create the following fields 

      • PhoneNumber (Text) [Set the validation to numeric only, with the max number of characters being set to 10]  Deselect Allow user to override data in this field
      • Masking (Calculation: Text) [Make sure to uncheck the field that states “Do not evaluate if all referenced fields are empty] 

      Set the calculation equal to:

      Choose(Length(PhoneNumber);
      (###)###-####;
      (&PhoneNumber&##)###-####;
      (&PhoneNumber&#)###-####;
      (&PhoneNumber&)###-####;
      (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;1)&##-####;
      (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;2)&#-####;
      (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-####;
      (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;1)&###;
      (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;2)&##;
      (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;3)&#;
      (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;4))

       

        • 1. Re: Formatting Phone Numbers
          philmodjunk

          looks like you are missing quotes around the ## text.

          "(###)###-####" ;
          "(" & PhoneNumber & "##)###-####" ;
          "(" & PhoneNumber & "#)###-####" ;

          and so forth...

          • 2. Re: Formatting Phone Numbers
            springhouse99

            Thanks! that  helped.  I was at least able to get the calculation to accept the entry.  I was able to make the masking field and position it over the other and to get the underlying data to show through. But alas no formatting.  The following is the full article from when I search "phonenumber" in Filemaker Pro 12 help.  Am I doing something wrong or is this example just not possible?

            Creating a Data Input Mask for Formatting Telephone Numbers
            Question

            How do I create a data input mask for formatting telephone numbers?

            Answer

            Review Formatting a Telephone Number answer_id=5247 target=_new> for basic examples.

            You can manage data input by restricting the type of input that is accepted from a user and you can control how that data is displayed by using a mask.

            For example, you might wish to use a telephone mask so that when a user enters 4085551234, the mask will render the input as (408)555-1234.

            Please note that in the following example:

            • Phone numbers will display in the following format: (408)555-1234
            • Users will be required to enter only numbers (no dashes)
            • Users will be required to enter the area code for each phone number in order for the mask to display them correctly

            Step 1: Create the following fields 

            • PhoneNumber (Text) [Set the validation to numeric only, with the max number of characters being set to 10]  Deselect Allow user to override data in this field
            • Masking (Calculation: Text) [Make sure to uncheck the field that states “Do not evaluate if all referenced fields are empty] 

            Set the calculation equal to:

            Choose(Length(PhoneNumber);
            (###)###-####;
            (&PhoneNumber&##)###-####;
            (&PhoneNumber&#)###-####;
            (&PhoneNumber&)###-####;
            (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;1)&##-####;
            (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;2)&#-####;
            (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-####;
            (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;1)&###;
            (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;2)&##;
            (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;3)&#;
            (&Left(PhoneNumber;3)&)&Middle(PhoneNumber;4;3)&-&Middle(PhoneNumber;7;4))

            Step 2: Setting up the fields for visual display
            In Layout mode:

            1. Right click [Mac: hold control and click] on the PhoneNumber field, select Arrange > Send Backward.
            2. Right click [Mac: hold control and click] on the Masking field, then select Arrange > Bring to Front.
            3. Right click [Mac: hold control and click] on the Masking field, then select Fill Pattern and select the second icon from the left at the very top.  (the icon that has a black square cascaded on top of a white square)
            4. Right click [Mac: hold control and click] on the Masking field, then select Fill Color and select white (or whatever your background color is)

            For FileMaker Pro 10 and earlier, do the following:

            1. Right click [Mac: hold control and click] on the Masking field, then select Field/Control > Behavior and uncheck all checkboxes.
            2. Adjust the fields so they are the same size.  By default they should be created at the same size
            3. Select both fields and then right click [Mac: hold control click] and select Arrange > Align > Left Edges.
            4. Select both fields and then right click [Mac: hold control click] and select Arrange > Align > Top Edges.

            For FileMaker Pro 11, do the following:

            1. With the Masking field selected, open the Inspector, select the Data tab and under the Behavior section, uncheck all checkboxes.
            2. Select the Position tab and make sure the PhoneNumber and Masking fields are the same size.
            3. Select both the PhoneNumber and Masking fields, and under Arrange & Align, click the first icon on the left under Align to align the left edges.
            4. With both fields still selected, click the fourth icon from the left under Align to align the top edges.

            EXPLANATION:
            FileMaker Pro does not have a way to do true masking by itself.  In order to do true masking, you would need a plug-in.

            Please keep in mind that this is an example and that you may need to make some modifications in order to format the mask you way you would like.

            The masking will occur when they first see the field, and after they input into the field, but not during.  Only numeric text should be placed in the PhoneNumber field, the Masking will format the phone number.  Numeric text is used to account for phone numbers that for whatever reason start with 0.  Please see attached example.

            With some slight modifications to the instructions, this same instructions can work in FileMaker Pro 6.  Please see sample file.

             
            • 3. Re: Formatting Phone Numbers
              philmodjunk

              This article is not in FileMaker Help. It is in the knowledge base, article #5839 (See link at top of this screen.)

              The article, BTW, shows all the quotes that you left out of your posted examples.

              What DO you see? The numbers but no parenthesis? do you see the dash?

              I can see the code for these in your posts and in the original article, but if you don't properly enclose them in quotes, they won't appear in your field.

              There are simpler ways to format a phone number by using an auto-enter calculation. You can search out examples of that in this forum. This method does not use a data mask, but does auto-format the data when you exit the field.

              • 4. Re: Formatting Phone Numbers
                springhouse99

                Well this tells you how new I am to Filemaker.  I did what you suggested and looked up some other examples.  I am having now luck.  I tried the following I must stress I followed the instructions to the tee.  And checked them twice.  An yes I changed phonenumber for the name of my field.

                FileMaker Pro does not have a built in number format for telephone numbers, but you can use text calculations to create custom formatting. The example below will yield numbers in the format (408)555-1212


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

                1. Create a text field called PhoneNumber.

                2. Click Options (or double-click the field name).

                3. From the Auto-Enter tab of the Options for Field dialog box, checkmark "Calculated value".

                4. Within the Specify Calculation dialog enter the following Calculation and click OK to save:

                Let(phone = filter(PhoneNumber;"0123456789");"(" & left(phone;3) & ")" & middle(phone;4;3) & "-" & middle(phone;7;4))

                (Example: If PhoneNumber contains Work: 408.555.1212 this calculation returns (408)555-1212)

                Important Note: Select "Do not evaluate if all referenced fields are empty" in the Specify Calculation dialog box.


                5. Within the Options for Field dialog make sure to Deselect "Do not replace existing value for field (if any)".

                6. Click OK to close the Options for Field dialog box.

                7. Click OK in the Define Database dialog box.


                The above example uses the new Filter Function introduced in FileMaker Pro 7. Further information regarding this function can be located in the Help File.

                 

                I know this is from version 7, but should it not work?

                • 5. Re: Formatting Phone Numbers
                  philmodjunk

                  Yes it should work.

                  Open Manage | Database | Fields and find the field you set up for this. Is the field listed as type "Text" or some other type?

                  If it's type text, double click it.

                  Click the auto-enter tab.

                  Click the calculation option.

                  Do you see the expression from the above article in this specify calculation dialog?

                  Click OK.

                  Make sure that the "do not replace existing values..." check box is NOT checked.

                  If it is working, you should be able to type in a 10 digit phone number with or without formatting characters and it should automatically become a formatted phone number when you exit the field.

                  • 6. Re: Formatting Phone Numbers
                    davidanders

                    I am old school and find this easy

                    PhoneArea [text] Drop down - display contents of same field

                    PhoneNumber [text] enter xxx-xxxx

                    PhoneExt [text] enter xxx or xxxx

                    PhoneCalc [calculation - text result]

                    "(" & PhoneArea & ") " & PhoneNumber    If PhoneExt IsEmpty result is (406) 555-1212

                    "(" & PhoneArea & ") " & PhoneNumber & " x" & PhoneExt  if PhoneExt IsNOTEmpty  result is (406) 555-1212 x123

                    easy to change the format to anything.

                    • 7. Re: Formatting Phone Numbers
                      springhouse99

                      Thanks! I checked everything and it works.  I thought if I entered through a field with data that it would change, but no.  If i enter a number if formats now.  Is there a way I can make it format the numbers I have already entered?

                      But again thanks! This form is an amazing resorce.

                      • 8. Re: Formatting Phone Numbers
                        philmodjunk

                        See this link for a trick that will update your existing records after adding or changing an auto-enter calc: Updating values in auto-enter calc fields without using Replace Field Contents