How does it work out when you try it?
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 )
l = 4 ;
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 ;
And as Bruce said: Why not try it yourself, and ask if/when you run into trouble?
it's a good method. I needed to have xxx-xxx-xxx,nnn (with extension) and use this
[ 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'
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).
It seems to work very well.
Digits = Filter(L_Insurance Phone Number; "0123456789");
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) ;
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).
What would you do differently, and yes I’m using the auto entry calculation.
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).
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.
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”?
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.)
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.
"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?