11 Replies Latest reply on Apr 21, 2014 7:42 AM by AntonMarakhovski

    Telephone Number Format calculation

    JoseHowardBustos

      Title

      Telephone Number Format calculation

      Post

      I'm trying to make my telephone number field format the numbers entered but I don't know how to do this. Please help!

      if i enter (5555555555) I want the number to appear like this (55) 5555-5555 parenthesis##patenthesisspace####dash####

      also, If the telephone number starts with 55, 33 and 81 I want the format above mentioned to appear. and If the number starts with, say 32, then to format the number like this (###) ###-####

      Can anyone help me how to figure out this one, please!

        • 1. Re: Telephone Number Format calculation
          philmodjunk

          If the telephone number starts with 55, 33 and [or] 81 I want the format above mentioned to appear. and If the number starts with, say 32...

          What format do you want if it starts with none of the above? I'll assume you want (###) ###-#### in that case.

          Let ( [ Digits = Filter ( PhoneNumberField ; 9876543210 ;
                      1stTwo = Left ( Digits ; 2 ) ;
                      L = Length ( Digits )
                    ] ;
                     Case ( L = 10 and Patterncount ( "553381" ; 1stTwo ) ; "(" & 1stTwo & ") " & Middle ( Digits ; 3 ; 4 ) & "-" & right ( Digits ; 4 ) ;
                                 L = 10 ; "(" & Left ( Digits ; 3 ) & ") " & Middle ( Digits ; 4 ; 3 ) & "-" & Right ( Digits ; 4 ) ;
                                 /* else */ PhoneNumberField
                                ) // case
                 ) // Let

          Notes
          1) if an incorrect number of digits are entered, the data is left unmodified.
          2) If you use this an an auto-enter calculation on the phone number field, you can substitute "self" for "PhoneNumberField".

          • 2. Re: Telephone Number Format calculation
            JoseHowardBustos

            Hey PhilModJunk

            Thank you for your help. I get this error message saying that there are too many parameters in this function. I don't understand why. I copy-pasted your calculation and it ended up like this. Is this correct?

            Let ( [ Digits = Filter ( Self ; 9876543210 ;
                        1stTwo = Left ( Digits ; 2 ) ;
                        L = Length ( Digits )
                      ] ;
                       Case ( L = 10 and Patterncount ( "553381" ; 1stTwo ) ; "(" & 1stTwo & ") " & Middle ( Digits ; 3 ; 4 ) & "-" & right ( Digits ; 4 ) ;
                                   L = 10 ; "(" & Left ( Digits ; 3 ) & ") " & Middle ( Digits ; 4 ; 3 ) & "-" & Right ( Digits ; 4 ) ;
                                   /* else */ Self
                                  ) // case
                   ) // Let

            What I did was this: in the layout view i right-clicked on the "Telefono" field and selected the conditional formatting option and then i went to the specify calculation and entered the formula just like above.

            I don't know what i'm doing wrong.

            Thanks a bunch!

            • 3. Re: Telephone Number Format calculation
              philmodjunk

              oops. There's at least one missing parenthesis I can see right in the first line... Embarassed

              Let ( [ Digits = Filter ( PhoneNumberField ; 9876543210 ) ;
                          1stTwo = Left ( Digits ; 2 ) ;
                          L = Length ( Digits )
                        ] ;
                         Case ( L = 10 and Patterncount ( "553381" ; 1stTwo ) ; "(" & 1stTwo & ") " & Middle ( Digits ; 3 ; 4 ) & "-" & right ( Digits ; 4 ) ;
                                     L = 10 ; "(" & Left ( Digits ; 3 ) & ") " & Middle ( Digits ; 4 ; 3 ) & "-" & Right ( Digits ; 4 ) ;
                                     /* else */ PhoneNumberField
                                    ) // case
                     ) // Let

              • 4. Re: Telephone Number Format calculation
                JoseHowardBustos

                Hello again

                I'm getting this error message now. "List usage is not allowed in this calculation" :(

                • 5. Re: Telephone Number Format calculation
                  philmodjunk

                  For some reason, it doesn't like 1stTwo starting with the 1 character. When I change it to:

                  Let ( [ Digits = Filter ( PhoneNumberField ; 9876543210 ) ;
                              FirstTwo = Left ( Digits ; 2 ) ;
                              L = Length ( Digits )
                            ] ;
                             Case ( L = 10 and PatternCount ( "553381" ; FirstTwo ) ; "(" & FirstTwo & ") " & Middle ( Digits ; 3 ; 4 ) & "-" & Right ( Digits ; 4 ) ;
                                         L = 10 ; "(" & Left ( Digits ; 3 ) & ") " & Middle ( Digits ; 4 ; 3 ) & "-" & Right ( Digits ; 4 ) ;
                                         /* else */ PhoneNumberField
                                        ) // case
                         ) // Let

                   

                  It works.

                  • 6. Re: Telephone Number Format calculation
                    JoseHowardBustos

                    It works perfectly when I set the field to calculation mode. The problem with this is that when I set it up as a calculation it makes the filed "not modifiable" so you can't enter info in the field. When I use the conditional formatting option it states below that the result must be boolean :( there must be a way to make this one work. 

                    • 7. Re: Telephone Number Format calculation
                      philmodjunk

                      The other option I have already mentioned. You can set up a text field with this expression specified as an auto-entered calculation. If you do this, clear the "do not replace existing values..." check box.

                      As an auto-entered calculation, you can write the expression this way:

                      Let ( [ Digits = Filter ( Self ; 9876543210 ) ;
                                  FirstTwo = Left ( Digits ; 2 ) ;
                                  L = Length ( Digits )
                                ] ;
                                 Case ( L = 10 and PatternCount ( "553381" ; FirstTwo ) ; "(" & FirstTwo & ") " & Middle ( Digits ; 3 ; 4 ) & "-" & Right ( Digits ; 4 ) ;
                                             L = 10 ; "(" & Left ( Digits ; 3 ) & ") " & Middle ( Digits ; 4 ; 3 ) & "-" & Right ( Digits ; 4 ) ;
                                             /* else */ Self
                                            ) // case
                             ) // Let

                      • 8. Re: Telephone Number Format calculation
                        JoseHowardBustos

                        Cool! It worked great! Thank you PhilModJunk!!!

                        • 9. Re: Telephone Number Format calculation
                          JoseHowardBustos

                          Hello, sorry to bother you again.

                          In the same manner, is there a way to conditional format a text field to capitalize every single word after a space?

                          let's say. I write: john smith and I get: John Smith ?

                          How does this formula go?

                          • 10. Re: Telephone Number Format calculation
                            philmodjunk

                            Proper ( Self ) can do this (and these are not conditional formats, conditional formats are settings on a field specified when you select an object on your layout while in layout mode and specify "conditional format".)

                            You can also select the field while in layout mode and specify "title case".

                            Both options have a draw back in that personal names don't all get capitalized in this pattern. Consider what you want when you have a name such as McDonald...

                            I've tinkered around with this expression either as an auto-entered calculation or a conditional format:

                            If ( Exact ( Upper ( NameField ) ; NameField ) or Exact ( Lower ( NameField ) ; Namefield ) ; Proper ( NameField ) ; NameField )

                            How this works is if the user types in text that is all upper case or all lower case, the first letters of each word are capitalized, but if the user uses both upper and lower case letters to enter a name such as McDonald, the case is not changed. This is a compromise and may not be effective in every situation.

                            • 11. Re: Telephone Number Format calculation
                              AntonMarakhovski

                                   Hi!

                                   I've the same problem, but the telephone number format is +7 (###) ###-##-##. What formula should I write?

                                   Thx!

                                   Anton