Phone number format calc help for a newbe

[gcooker]

I am having trouble with a phone number format. Initally I had it working fine

Let(

P=Filter (Phone2;"0123456789")

;

Case(

Length (P)=11;Left(P;1)&" ("&Middle(P;2;3)&") "&Middle(P;5;3)&"-"&Middle(p;8;4);

Length (P)<10;P&TextColor("not enough digits";RGB(255;0;0));

Length (P)>11;P;

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

))

This worked great for normal numbers and basicly left the internationals alone (which is what I wanted).

Well this solution runs into a problem when importing data from our accounting package (were the master data is kept) In the account software there is only one place for a fax number and two places for phone numbers. Some of the second Phone numbers are used for second fax numbers with a "f" at the begining or end of the number. I can't seem to figure out a formula to not format the number if it has any letters in it. This is what I was trying but I am not formating the formula right or something. HELP.

Let PT=0;0+0

Let(P=Filter (Phone1;"0123456789");

Let(P1=filter (Phone1;"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ");

If(P1 &#8800; P;PT=1;PT=0)

If(P = 10;PT=2;PT=0)

Case(

Length (PT)=1;Phone1;

Length (PT)=0;Phone1;

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

))

Thanks

[Damocles]

Any chance for a few examples?

f(757)632-9098?

1(434)322-8988?

0-11-895-333-7873?

Thanks!

Paul

[gcooker]

Sorry I should have done that.

Problem numbers are

F 1231231234

f1231231234

1231231234f

I was trying to find a way to figure out if there were any text characters in the data and if there were to just show the data as entered. If it has the standard US 10 digit it would be formated.

I hope this is more clear.

Regards

[-Queue-]

How about something like

Case( PatternCount( Lower(Phone2); "f" ); Phone2;

Let( P = Filter( Phone2; "0123456789" );

Case( Length(P) = 11; Left( P; 1 ) & " (" & Middle( P; 2; 3 ) & ") " & Middle(P ;5 ;3 ) & "-" & Middle( p; 8; 4 );

Length(P) < 10; P & TextColor( "not enough digits"; RGB( 255; 0; 0 ) );

Length(P) > 11; P;

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

[gcooker]

Thank you Queue it seems to work but I don't total know how it is working. I understand the lower function and the "f" but the first case statement I am having trouble with. (I come from a Fortran, Basic, Cobalt, Pascal, GEFanuc PLC background) It seems it is like an If then statement. But what is the patternCount have to do with it? It should return a 1 if there is a f or F in the phone number? Also what if I wanted to include other leters or combinations of leters like "page" for pager (someone here is going to get me by doing that some day)?

Is there a good book(web site) on the functions and calculation with better def. and examples then the online help in filemaker 7?

Thanks

[-Queue-]

PatternCount would return how many times the letter occurs in the field. If it's anything other than zero, then the test is true, so the Case returns the next expression. If you want to incorporate page, it would be like Case( PatternCount( Lower(Phone2); "f" ) or PatternCount( Lower(Phone2); "page" ); Phone2; ....

I think I missed one closing parens in the original calculation, by the way.

Check databasepros.com for some help with calculations. I would recommend Scriptology, but I know it's basically impossible to find anymore.