I've read and tried to use different phone formats like instead of showing 5555555555 it shows (555)555-5555. But each time I try to use one, I get an error message or it doesn't work (or just doesn't like me, haha). So here is the latest one I have been trying to get to work, the bold,underlined, and italicized "format" is where I get the error saying the specified field cannot be found. Any help on trying to implement this would be appreciated, thanks - Jeff

Let(

[NumbersOnly = Filter(Phone; "0123456789");

NewNumber = Right(NumbersOnly; Length(NumbersOnly) - 1);

NewFormat = Right(Format; Length(Format) - 1)];

Case(

not IsEmpty(Format);

Case(

Left(Format; 1) = "#";

Left(NumbersOnly; 1) & PhoneFormat(NewNumber; NewFormat);

Left(Format; 1) & PhoneFormat(NumbersOnly; NewFormat)

)

)

)

What is the end format that you are trying to achieve here? It seems like you are trying to use a PART of a custom Function.

What is wrong with the Database Pros example of Filter Phone,

Let(

NumbersOnly = Filter(Phone Number; "0123456789");

"(" & Left(NumbersOnly; 3) & ") " &

Middle(NumbersOnly; 4; 3) & "-" &

Right(NumbersOnly; 4)

)

[color:blue]results

(909) 393-4664

Lee

Your profile says you're using FMP 9, which doesn't support the creation and editing of custom functions. but it looks like you're editing a custom function named PhoneFormat(Phone; Format).

If you're testing this in the Data Viewer, you're going to get error messages because the DV won't recognize the string "Format" as the parameter you've passed and the calc won't recurse.

I'm going to take a stab and say you've got a custom function with a sample input of:

PhoneFormat("212.555.1212";

"(###) ###-####") which should return "(212) 555-1212"

It's possible to do what you want, but it seems much easier to simply choose a formatting style and stick to it, rather than allowing a user to define a custom style using the # symbol. You wouldn't need recursion for that. You may not even need recursion for this method either, but the calculation part of my brain is suffering from a caffeine deficient, so I can't help there.

But if you're set on this, you need to add error checking, like looking for number strings that are longer or shorter than the number of # signs.

Also, the nested Case() is a little awkward. I'd put an IsEmpty(Format) with a null result in the Case() with the other statements.

So.....I can only have this custom phone formatting if I have Filemaker Pro 9 Advanced? Seems odd...There is no other way?

No that is not the case. You are misunderstanding what was said. You could simulate the format for the field like the custom function with an extensive calcuation.

However, for what you posted, you will not be able to use it the way it is currently written because it uses a Custom Function called PhoneFormat( ). You can not recreate a custom function without Advanced.

However, once it is created within a particular file, you are able to use the function in Pro.

You need the Advanced version IF you want to use a [color:red]recursive custom function, like the one you have picked. Or better put, you need the Advanced version to INSTALL the custom function into your solution - once that is done, it will work in the 'regular' version, too.

There are other methods to format a phone number - see for example:

http://www.fmforums.com/forum/showtopic.php?tid/169475/

Hi Lee,

The only thing I see that is problematic with the Database Pros calc is this: What happens if there is no area code or the field also contains an ending of Ext 14 or what if it is an international number? I would test for these types of issues. If you don't and the number lacks an area code then you will end up with (393) 466-4664 instead of (000) 393-4664 or () 393-4664 as it should be.

Rules should be established on how to handle the exceptions, international numbers etc or it can bite someone; particularly if this type of calculation is used as an Auto-Enter (Replace). Depending upon how a calculation modifies the data, one might lose the true number altogether.

LaRetta :wink2:

Edited June 7, 200817 yr by Guest
Changed can to should

Does anybody have a custom function that can handle these variables (domestic vs. international vs. extension) easily?

OK -- here's what I ended up doing. I adapted the following custom function. It will format US numbers, but will NOT format any number beginning with a "+" or a "0."

If anybody has a better suggestion, post it!

Let(

p = Filter ( Phone ; "+0123456789" );



//Regional 10 digit Format - North American Style

Case(Left( p; 1 ) = "0" or Left( p; 1 ) = "+" ; Phone

; Case (Style = "Regional";

Case( Length ( p ) < 10; p &TextColor ( "" ; RGB ( 255 ; 0 ; 0 ) ); 

"(" & Left( p; 3) & ") " & Middle( p; 4; 3) & "-" & Middle( p; 7; 4) & 

Case( Length ( p ) > 10; " x" & Middle( p; 11; 999))

)



//Toll Free Number Format



; Style = "800";

Case(Length ( p ) < 11; p & 

TextColor ( " 11 digits Required" ; RGB ( 255 ; 0 ; 0 ) ); 

Left( p; 1) & "-" & Middle ( p; 2 ; 3 ) & "-" & Middle( p; 5; 3) & "-" & Middle( p; 8; 4) & 

Case( Length ( p ) > 11; "    xtn " & Middle( p; 11; 999))



); TextColor ( "Calc Error" ; RGB ( 255 ; 0 ; 0 ) )



)))

It's hard to say what could work better without knowing the purpose. As LaRetta said: "Rules should be established."

In any case, at least this portion of your calc doesn't look inspiring:

TextColor ( "" ; RGB ( 255 ; 0 ; 0 ) )

You're right. That probably should have said "error":

TextColor ( " error" ; RGB ( 255 ; 0 ; 0 ) )