13 Replies Latest reply on Apr 18, 2016 9:47 AM by beverly

    Creating a data input mask for formatting telephone numbers

    zmabney

      The KB recommends using this technique to create a data input mask for formatting telephone numbers.

      URL:

      Creating a data input mask for formatting telephone numbers | FileMaker

       

      Wouldn't this work as well?

      A Calculated Value: L_Insurance Phone Number

      Let(

      Digits = Filter(L_Insurance Phone Number; "0123456789");

       

      Case (

      GetAsNumber ( L_Insurance Phone Number ) < 10000 ;

      Right(Digits; 4) ;

       

      GetAsNumber ( L_Insurance Phone Number ) < 10000000 ;

      Middle(Digits; 1; 3) &

      "-" & Right(Digits; 4) ;

       

      GetAsNumber ( L_Insurance Phone Number ) < 10000000000 ;

      "(" & Left(Digits; 3) & ") " & Middle(Digits; 4; 3) &

      "-" & Right(Digits; 4) ;

       

      GetAsNumber ( L_Insurance Phone Number ) < 100000000000 ;

      "+" & Left(Digits; 1) & " (" & Middle(Digits; 2; 3) & ") " &

      Middle(Digits; 5; 3) & "-" & Right(Digits; 4) ;

       

       

      "Invalid"

      )

      )

        • 1. Re: Creating a data input mask for formatting telephone numbers
          BruceRobertson

          How does it work out when you try it?

          • 2. Re: Creating a data input mask for formatting telephone numbers
            erolst

            zmabney wrote:

            Wouldn't this work as well?

            […]

             

            More or less, but using Let() consequently, this can be written much cleaner:

             

            Let ( [

              d = Filter ( L_Insurance Phone Number ; "0123456789" ) ;

              l = Length ( d ) ;

              right4 = Right ( d ; 4 ) ;

              left3 = Left ( d ; 3 )

              ] ;

              Case (

                l = 4 ;

                  right4 ;

                l = 7 ;

                  left3 & "-" & right4 ;

                l = 10 ;

                  "(" & left3 & ") " & Middle ( d ; 4 ; 3 ) & "-" & right4;

                l = 11 ;

                  "+" & Left ( d ; 1 ) & " (" & Middle ( d ; 2 ; 3 ) & ") " & Middle ( d ; 5 ; 3 ) & "-" & right4 ;

                  "Invalid"

              )

            )

             

            And as Bruce said: Why not try it yourself, and ask if/when you run into trouble?

            • 3. Re: Creating a data input mask for formatting telephone numbers
              beverly

              it's a good method. I needed to have xxx-xxx-xxx,nnn (with extension) and use this

              Let(

              [ phf = Filter ( phone_num ; "0123456789" ) // only numerals

              ; ext = Filter ( phone_num ; ",xX" ) // probably has an extension

              ; ext = If ( not IsEmpty(ext) ; GetValue ( Substitute ( phone_num ; ext ; ¶ ) ; 2 ) ; "" )

              ; phl = Length ( phf )

               

              ; phph = Case

                 ( phl = 10 ; Left ( phf ; 3 ) & "-" & Middle ( phf ; 4 ; 3 ) & "-" & Right ( phf ; 4 ) // xxx-xxx-xxxx

                 ; phl = 7 ; Left ( phf ; 3 ) & "-" & Middle ( phf ; 4 ; 4 ) // xxx-xxxx

                 ; phl > 10 ; Left ( phf ; 3 ) & "-" & Middle ( phf ; 4 ; 3 ) & "-" & Middle ( phf ; 7 ; 4 ) & If ( not IsEmpty(ext) ; "," & ext ; "" ) // xxx-xxx-xxxx,xxx

                 ; phf // default 'as is'

                 )

               

              ]; phph

              )

               

              There are probably other custom functions that work:

               

              This thread (this forum) considers international phone numbers:

               

              Maybe all of these will give you ideas of what works for you and you may need to create something custom (as I did, but did not account for international #'s).

               

              beverly

              • 6. Re: Creating a data input mask for formatting telephone numbers
                keywords

                zmabney wrote:

                Let(

                Digits = Filter(L_Insurance Phone Number; "0123456789");

                 

                Case (

                GetAsNumber ( L_Insurance Phone Number ) < 10000 ;

                Right(Digits; 4) ;

                 

                GetAsNumber ( L_Insurance Phone Number ) < 10000000 ;

                Middle(Digits; 1; 3) &

                "-" & Right(Digits; 4) ;

                 

                GetAsNumber ( L_Insurance Phone Number ) < 10000000000 ;

                "(" & Left(Digits; 3) & ") " & Middle(Digits; 4; 3) &

                "-" & Right(Digits; 4) ;

                 

                GetAsNumber ( L_Insurance Phone Number ) < 100000000000 ;

                "+" & Left(Digits; 1) & " (" & Middle(Digits; 2; 3) & ") " &

                Middle(Digits; 5; 3) & "-" & Right(Digits; 4) ;

                 

                 

                "Invalid"

                )

                )

                A couple of observations:

                1.     You are making poor use of the Let() function here—at least two and up to five calls on the field "L_Insurance Phone Number".

                2.     Not clear if you are using a calc field, but better than that would be an auto-enter calc to replace whatever text is entered with your filtered, formatted version.

                3.     Whatever calc you use needs to take account of all possible phone numbers users might enter, and should also cater for users entering incorrect data (too many digits, too few digits, etc).

                • 7. Re: Creating a data input mask for formatting telephone numbers
                  zmabney

                  What would you do differently, and yes I’m using the auto entry calculation.

                   

                  See Attachment:

                  • 8. Re: Creating a data input mask for formatting telephone numbers
                    erolst

                    zmabney wrote:

                    What would you do differently,

                     

                    Why don't you simply take a look at my previous post?

                     

                    PS: No attachment in your post (that I can see).

                    • 9. Re: Creating a data input mask for formatting telephone numbers
                      zmabney

                      Oh, I see it now.

                       

                      So the discussion board doesn’t accept attachment via email?

                       

                      I was trying to send you my example.

                       

                      I’ll plug your technique in and give it a try.

                       

                      Thank you!

                      • 10. Re: Creating a data input mask for formatting telephone numbers
                        zmabney

                        I just plugged it in, and it works great!

                         

                        It is more efficient, accounts for all possibilities, and probably uses less resources. It seems to be a little more code and less script, and I would not consider myself a good code writer.

                         

                        Thank you so much. I’ll use this everywhere, but I’m struck that FMP doesn’t have something native in their format toolbox.

                         

                        So how do I flag this post with yours being “the answer”?

                        • 11. Re: Creating a data input mask for formatting telephone numbers
                          keywords

                          Re: "So the discussion board doesn’t accept attachment via email?"—

                          To attach a file click on "use advanced editor" in top righthand corner of the edit box

                           

                          Re: "So how do I flag this post with yours being “the answer”?"—

                          I see you must have worked that one out.

                           

                          Re: "I’m struck that FMP doesn’t have something native in their format toolbox." and "What would you do differently"—

                          I think of Filemaker as a box of tools that can be used in many different ways to solve an infinite variety of problems. One rule of thumb with FM is that there is always more than one way to achieve a given purpose. What erolst has given you is one such example. FM developers are always coming up with creative ways to use the tools available to solve problems, and often the very best of these creative approaches find themselves being built into future versions of FM.

                          The "simple" matter of formatting telephone numbers, while seeming straightforward enough at first glance is actually far more complex. The FM paper on creating data input masks looks at just one standard format, but that is only a small part of the problem, and entirely unsuited to my needs in my part of the world. To properly format phone numbers here my Let() calculation had to account for: (1) nine different types of phone number; (2) format these in six different formats; (3) the fact that users enter data in various ways, all of which may be legitimate; (4) users sometimes make mistakes, entering to many or too few digits. Using various functions in Filemaker's toolset I was able to create a single Let() calc that achieves all of this in a single field. (Within the calc, functions used included: Self; Filter; Length; Left: Middle; Substitute; and Case.)

                          • 12. Re: Creating a data input mask for formatting telephone numbers
                            zmabney

                            Thanks!

                             

                            I think of FileMaker as a box of tools too. I've been using it since version 1 (before it was relational). I would consider my uses of it as strong on providing a likable solutions to a automating processes, and an intermediate code writer. I'm heavy on logic, lite on math, but makeup for it all with tenacity.

                             

                            Thanks again!

                            • 13. Re: Creating a data input mask for formatting telephone numbers
                              beverly

                              "native phone format" is subjective. You can see by the varied answers here, that some have international prefix, some have no area code, some have an extension. And there is a variety of "formatting" with no separators, with space, using parens, using dashes, using parens and dashes, etc.

                               

                              If we could get a "clickable" version (as far as smart phones know them) then perhaps?

                              beverly